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
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
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
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
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.
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.
The Hires and Layoffs and Costs sections with a color legend for the cells