Excel Case Study

Witte Lawn Equipment produces four types of lawn mowers in its factory. Each type of mower requires a different amount of labor input and a different amount of materials to produce a single mower. As the production manager, your job is to determine the best mix of mowers to produce based on the amount of available labor and materials for a given week. The company should produce no more than 20 electric and power lawn mowers in a week due to a high level of inventory. Due to contractual obligations with a distributor, the company must produce at least 16 self-propelled mowers each week. Overall, the company does not want to produce more than 65 of any type of mower in a week.

Instructions:

1. Create the Witte Lawn Equipment Excel workbook using the data below and the following formulas:

a. Cell F8 =SUM(B8:E8)

b. Cell F10 =SUM(B10:E10)

c. Cell F11 =SUM(B11:E11)

d. Cell B10 =B5*B8

e. Copy this formula across to Cell E10

f. Cell B11 =B6*B8

g. Copy this formula across to Cell E11.

2. Your finished worksheet should resemble the worksheet above.

a. Name the Worksheet Tab Mower Production Planning

b. Save the workbook using the name Project 8_Your Name.

3. Use Solver to determine the mix of products that maximizes the number of units produced in the week. In the first week of March, the total amount of labor hours available is 6,200 hours and $75,000 worth of material is available. Use the Assume Linear Model option in Solver.

4. Instruct Solver to create an Answer Report if it can find a solution to the problem.

5. See the figure below for Solver results.

6. Save the scenario as March Week 1. The changing cells are B8:E8.

7. Rename the Answer Report containing the scenario as March Week 1.

8. Change the worksheet header to contain your name, Witte Lawn Equipment and Project 8.

9. The company president thinks the assumptions about the number of labor hours needed to produce the mowers is not correct. See the table below for the new labor hour values. Enter these new values in the Mower Production Planning Sheet.

Mower	Labor Hours
Electric Lawn Mower	32 Hours
Power Lawn Mower	40 Hours
Riding Garden Tractor	55 Hours
Riding Lawn Tractor	68 Hours

10. Use Solver to find a solution to the problem of maximizing the number of units produced in one week based on the new labor assumptions. Instruct Solver to create an Answer Report if it can find a solution to the problem. Solver should report that the optimal number of units of Electric Lawn Mowers is 20, of Power Lawn Tractors is 20, of Riding Garden Tractors is 65, and of Riding Lawn Tractors is 17.

11. Save the scenario as March Week 2.

12. Rename the Answer Report containing the scenario as March Week 2.

13. Create a Scenario Summary showing the two scenarios you saved in Scenario Manager. See below for an example of how your Scenario Summary should look.

14. Delete all blank worksheets. Assign colors to the remaining worksheet tabs. The workbook should contain the following worksheets: Mover Production Planning, March Week 1, March Week 2 and Scenario Summary.

15. Save the workbook.