Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
Completed Constants and Sales and Operations Plan sections
Completed Hires and Layoffs and Costs sections
Setting up and Running Solver for the Chase Plan
The first plan you will examine is George Detweiler
s Chase proposal. In this plan, you will set up the
constraints so that the monthly units produced are at least equal to the sales forecast. Copy your Green
Starting worksheet to a new worksheet, and rename it Green Jeans — Chase.
Before using the Solver Parameters window, you should consider jotting down the parameters you must
define and their cell addresses. Here is a suggested list:
The cell you want to optimize (total cost
cell L42) and whether you want to minimize or maximize it
The cells you want Solver to manipulate to obtain the optimal solution: Plan (in employees), in
cells F22 to K22
The constraints you will define:
Each Changing Cell must be an integer greater than or equal to zero
you cannot hire half
You must keep at least a five-day supply of inventory in every month. In other words, each
cell in the Plan Days of Supply row must contain a value of 5 or more.
For the Chase plan, your units of production each month must be greater than or equal to
the forecast units. For example, the value in cell F21 must be greater than or equal to the
value in cell F17 for January. You must repeat this constraint for each month of the plan.