Excel Case Study

it153_u9assignemntb_0001 it153_u9assignmenta

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.

You work for the Farmers Wholesale Cooperative and help the financial director prepare and analyze revenue and expenses. He has asked you to create two PivotTables and corresponding PivotCharts from the annual sales worksheet for the Farmers Wholesale Cooperative. One PivotTable and PivotChart summarize the sales by farm and the other PivotTable and PivotChart summarize the dairy sales by month for the top dairy producer. Instructions:

1. Create the workbook Farmers Wholesale use the data from the table below, name

the worksheet tab Annual Sales and save the workbook using the file name, Unit 9 Project_Your Name.

worksheet Sales by Farm. See below for ex

4. Change cell A4 to Farms and cell B3 to Stores. Apply the Pivot Style Light 16 style to the PivotTable. Format the values as Currency values with a dollar sign and no decimal places. Apply the Style 19 to the PivotChart.

5. Create a second PivotTable and associated PivotChart to determine the dairy sales by month in a separate worksheet in the workbook. Name the worksheet Dairy Sales by Month.

See below for example:

6. Change cell A4 to Month an d cell B 3 to Farm. Apply the Pivot Style Light 16 style to the PivotTable. Format the values as Currency values with a dollar sign and no decimal places. Turn off the legend for the chart.

7. Filter the product type by Dairy. Filter the farm to Red Earth.

8. Change the chart type to Line and then add a linear Trendline that forecasts the trend for two more months.

9. Save the workbook.

�

Using the concepts and techniques learned in the unit chapter’s readings, create the

PivotTable and associated PivotChart to determine the sales by farm. Name the