Microsoft Office Tutorials and References

In Depth Information

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

Color Legend

—

This section contains a legend that explains the purpose of the fill colors in the

worksheet.

Hires

This line contains the number of new employees hired each month. If the number of

employees in the plan for the current month is greater than the number of employees in the

plan the previous month, then the number of new hires equals the number of employees in the

plan for the current month minus the number of employees in the plan the previous month. If

the number of employees in the plan for the current month is less than or equal to the number

of employees the previous month, then the number of new hires is zero. If you need a refresher

in how to convert IF statements into formulas, see Tutorial C.

—

Layoffs

This line contains the number of employee layoffs each month. If the number of

employees in the plan for a month is less than the number of employees in the plan the previous

month, then the number of layoffs equals the number of employees in the plan for the previous

month minus the number of employees in the plan for the current month. If the number of

employees in the plan for the previous month is less than or equal to the number of employees

for the current month, then the number of layoffs is zero.

—

Totals

Cells L34 and L35 contain the total numbers of hires and layoffs. Enter formulas for the

sums of monthly hires and layoffs for the six months into cells L34 and L35, respectively.

—

Cost of Hires

This value is the number of hires from the Hires and Layoffs section multiplied

by the Hiring Cost per worker from the Constants section (cell $C$5).

—

Cost of Layoffs

This value is the number of layoffs from the Hires and Layoffs section

multiplied by the Layoff Cost per worker from the Constants section (cell $C$6).

—

Cost of Inventory

This value is the Plan (in 1000 $) from the Inventory portion of the Sales

and Operations Plan section multiplied by the Inventory Carrying Cost from the Constants

section (cell $C$7) multiplied by 1,000. If you are confused about this formula, enter the

following in cell F41:

—

F28*1000*$C$7. Copy this formula to cells G41 through K41.

¼

Totals

Cells L39, L40, and L41 contain the total costs of hiring, layoffs, and inventory (carrying

cost). Enter formulas for totaling each of these three costs for the six months in cells L39, L40,

and L41, respectively. Cell L42 is the total of cells L39 through L41, and is the Optimization Cell

for your decision model. Enter the appropriate formula to sum your costs in this cell.

—

If you wrote the formulas correctly, your last two sections will look like those in Figure 9-6.

Because you have the same employment level (550) set up from January through June, the Hires and

Layoffs section should contain all zeros. The Inventory costs decline steadily by month and actually become

negative starting in April, because 550 employees cannot produce enough jeans per month to keep up with

the sales forecast, given the starting inventory. This is acceptable because you have not yet specified

parameters for Solver to determine your plan.

You have finished the initial setup of your decision model. Right-click the sheet tab at the bottom of the

worksheet and rename it Green Jeans
—
Starting. You will copy this worksheet to build each of your Solver

solutions. If you have not already saved your workbook, click File, click Save, and then name the file Green

Jeans SOP (your name).xlsx. Include the first initial of your first name and then your last name. Including

your name in the filename makes it easier for your instructor to identify the workbook.

Your completed worksheet should look like Figures 9-5 and 9-6.