Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
figures and the actual results for each month. Next to the historical data is the plan for the next six months.
As each month
s operations are completed and the actual sales data is collected, the column for that month
will move to the History section, and a new month will be added to the plan. As stated before, most Sales and
Operations plans cover 12 months, but Green Jeans
plans are for six months.
To avoid excessive data entry in the historical section, you should download the skeleton file for this
case. If you choose to set up the model yourself, refer to Figure 9-2. A description of each line item follows
the figure. Gray cells contain formulas, and the yellow cells are the Changing Cells for this model.
DEALING WITH LARGE NUMBERS
The numbers in Sales and Operations plans are often quite large. To keep column widths manageable, planners frequently
express sales and units as multiples of thousands or millions. In this case, you will be given conversion units for the formulas;
usually you will divide or multiply by 1,000 to make the conversion.
Sales and Operations Plan section
This section contains values for October, November, and December, as shown in Figure 9-2.
The actual end-of-month inventory for December is the only value here that you will use in a
formula for the plan section. If you use the spreadsheet skeleton, these values will already be entered
Although these lines have entries in the History
section, they will remain blank in the Plan section. Accountants use these cells in the worksheet
to enter the Actual data for each month.
(Sales, Operations, and Inventory)
Forecast (in million $)
This line contains formulas for the dollar values of the sales units
forecast below. The formula for this value is the sales forecast in units for the month multiplied by
the sales price per unit, then divided by 1,000 to convert the units (in thousands) into dollars
Forecast (in 1,000 units)
These values are the sales forecast for Green Jeans, in thousands.
Enter the following values in the order shown: 253, 280, 340, 300, 393, and 233.
Operations Plan (in 1,000 units)
This line contains formulas for the amounts of planned
production, in thousands of units. The formula for this value is the number of employees multiplied
by Employee Productivity (cell $C$4) multiplied by the number of working days per month,
divided by 1,000. The number of employees and working days per month are reported in the
next two cells.
Plan (in employees)
This line is the heart of the Solver model: the Changing Cells. These values
are the numbers of employees in the workforce each month. Enter 550 in each cell for now.
Place a fill color in this line of cells to indicate that they will be the Changing Cells for Solver.
For added emphasis, you can select a thick box border to place around this line.
Number working days/mo.
These values are the number of days worked each month in the
plan. The Green Jeans plant runs five days per week. Enter the following values in the order
shown: 20, 21, 23, 20, 22, 22.