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.

Search JabSto ::

Custom Search