Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
You now have the cost data for running the Chase plan. Before continuing, look at the employment levels
that Solver chose for optimizing the total cost. Your plan probably shows steady hiring for the first four or five
months to meet the sales forecast. By changing the Solver constraints, you can also model a Level plan, a
Logistics Target plan, and your own optimized plan.
Assignment 1B: Changing Parameters and Creating New Solutions
Using the model you created in Assignment 1A, you can model and evaluate the other managers
proposals.
Now that you know how to copy spreadsheets, you can create additional spreadsheets and look at the Solver
solutions for each plan.
Level Sales and Operations Plan
Next, you will evaluate Betty Miller
s proposal for a Level employment plan. The constraints for this plan are
simple. You will set your constraints so that the plan employment level for every month after January equals
January. You know from your original worksheet that the company will have to hire employees to meet its
sales demands and minimum inventory targets for days of supply.
Make a copy of your Green Jeans
Chase worksheet, and rename it Green Jeans Level Plan. Open your
Solver Parameters window; the Optimization Cell and Changing Cells stay the same. Keep your original
constraints for the Changing Cells to be integers and greater than zero, and keep the Plan Days of Supply at
greater than or equal to 5, but delete the six constraints for the Production cells to be greater than or equal to
the forecast. Next, you must add constraints to make the February, March, April, May, and June employment
levels equal to January
people
in January to satisfy the inventory supply minimums for the six months. Run your Solver model for the plan;
if you set it up correctly, Solver will use the same number of workers every month from January through
June. You might be surprised when you see the total cost of the Level plan proposal. Create an Answer Report
for this solution.
s. Do not set a hiring constraint for January; Solver must be allowed to
hire
5-6 Day Target Sales and Operations Plan
Next, you will evaluate Pedro Sanchez
proposal to target a 5- to 6-day inventory at the end of every month to
minimize inventory carrying costs. The constraints for this plan are not difficult; you will set them so that the
Plan Days of Supply for each month are between 5 and 6.
Make a copy of your Green Jeans
Level Plan worksheet, and rename it Green Jeans 5-6 Day Target.
Open your Solver Parameters window; the Optimization Cell and Changing Cells stay the same. Keep your
original constraints for the Changing Cells to be integers and greater than zero, and keep the Plan Days of
Supply at greater than or equal to 5, but delete the constraints for making all the monthly employment levels
equal to January
s. Add constraints to make the Plan Days of Supply for each month less than or equal to 6.
Run your Solver model for the plan; if you set it up correctly, Solver will keep the Plan Days of Supply
between 5 and 6 days for every month from January through June. Create an Answer Report for this solution.
Optimal Sales and Operations Plan
Finally, you will create what you think is the optimal Sales and Operations plan for Green Jeans. The only
constraint you think you have to satisfy is the minimum five days of supply for the inventory. You will give
Solver complete freedom to hire or fire employees as necessary to minimize the total cost. The constraints for
this plan are the easiest to define.
Make a copy of your Green Jeans—5-6 Day Target worksheet, and rename it Green Jeans Optimal Plan.
Open your Solver Parameters window; the Optimization Cell and Changing Cells stay the same. Keep your
original constraints for the Changing Cells to be integers and greater than zero, and keep the Plan Days of
Supply at greater than or equal to 5, but delete the constraints for making the Plan Days of Supply for each
month less than or equal to 6. Run your Solver model for the optimal plan. If you set it up correctly, Solver
will probably give you the least expensive solution of all the plans. You may also be surprised by its
recommended employment levels in the Changing Cells. Create an Answer Report for this solution.
When you have finished all four plans, print a copy of all the worksheets and Answer Reports. Open the
Page Setup dialog box by clicking the File tab on the Ribbon, then click Print in the left pane and click Page
Setup, the bottom item in the middle pane. The Page Setup dialog box appears. Select Landscape as the page
layout for the worksheets, and make each worksheet fit to print on one page (see Figure 9-11). The data in
the worksheet should be large enough to read easily.
 
Search JabSto ::




Custom Search