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.

’

NOTE

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.

—

FIGURE 9-2

Sales and Operations Plan section

History

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

for you.

—

“

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.

Actual

”

(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

(in millions).

—

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.

—