Microsoft Office Tutorials and References

In Depth Information

**ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT**

Inventory Plan (in 1,000 units)

This line contains formulas for the number of finished jeans

that remain in inventory at the end of each month. January

—

s formula (cell F27) is different from

the rest of the months because it will use the Actual ending inventory from December (cell E29).

Type the following formula into cell F27:

’

F17. The formula for February through

June (cells G27 through K27) will be the previous month

E29

F21

¼

þ

s plan inventory plus the plan

production for the current month, minus the forecast sales for that month. If you are confused, the

formula for cell G27 is

’

F27

G21

G17. Next, copy cell G27

’

s formula into cells H27 through K27.

¼

þ

Plan (in 1,000 $)

This line contains the dollar value of the finished jeans that remain in

inventory at the end of each month. The formula for this value is the inventory plan (in thousands of

units) from the previous cell multiplied by the Unit Cost of Inventory (cell $C$10 in the

Constants section).

—

Plan Days of Supply

This line contains the days of finished goods inventory available to supply

the forecast sales demand for that period. The formula for this value is the plan inventory in

units divided by the sales forecast, multiplied by the number of working days in each month.

Because both the plan inventory and the sales forecast are in units of 1,000, the formula does

not need a conversion factor. If you are confused, the formula for cell F30 is

—

(F27/F17)*F23.

¼

Next, copy cell F30

’

s formula into cells G30 through K30.

If you wrote your formulas correctly, your Sales and Operations Plan section should have the values

shown in Figure 9-3. If you have different numbers, check your work.

FIGURE 9-3

Sales and Operations Plan section with the formulas correctly entered

Hires and Layoffs and Costs Sections

These sections contain calculations for the number of hires and layoffs in your workforce for each month

in the plan, and calculations of the hiring costs, layoff costs, and inventory carrying costs for each month.

These sections also contain the total plan cost, which will be the Optimization Cell that you want to

minimize. If you downloaded the skeleton worksheet, you will only have to enter the formulas for the cells

in these sections. See Figure 9-4 and the following list for the formulas you will enter.

FIGURE 9-4

The Hires and Layoffs and Costs sections with a color legend for the cells