16 Feb Use Solver to determine the number of miles each truck should be driven

Order Instructions

Overview: This exercise is designed to give you some experience practicing using Solver before you need to do so for your final project submission, which is due

in Module Seven. It is recommended that you complete this assignment as early as possible; that way, you will have time to post to the General Questions

discussion topic if you have any questions or problems.

Prompt: In this assignment, the scenario is to determine which combination of manufacturing tables and chairs is the most profitable. To complete this

assignment, follow the steps outlined below:

1. Part 1 of this assignment is ungraded, but will assist you greatly in understanding how a table is set up to run Solver.

a) Open the Module Six Assignment Spreadsheet and make sure you are on the tab (worksheet) titled Solver Part 1. You will see a spreadsheet that

has been set up to use Solver. (Note: There is more than one way to set up and program a table; the tables used for this exercise illustrate one

method.) The table highlighted in gray shows the specific amounts of material, fabrication time, and output needed for each chair and table, as

well as the total cost of producing each item. The table in blue calculates total profit from producing both tables and chairs. The table in green

tracks the amount of materials and fabrication consumed to produce the volume that will be decided. Lastly, the yellow cells on the green table

indicate the materials and fabrication time as well as the output volume minimums set by management.

b) Click on each cell to observe the programing used, but do not change anything.

c) After you have reviewed the programing, click on the red cell, go to the menu bar at the top of the page, and click on the Data tab. Then click on

Solver. The Solver dialog box should open, and you can observe which cell has been identified as the target cell, which cells Solver will change,

and how the constraints have been entered. Do not run Solver at this time; simply click Close on the dialog box.

2. Now you will practice running Solver and viewing two of its associated reports. Click on the tab at the bottom on the screen titled Solver Parts 2 and 3.

You will see the same data tables from Part 1. Follow these steps:

a) At the top of the worksheet, click on Data, then Solver.

b) On the Solver Parameters dialog box, click Solve.

c) See how the cells with the zeros now have been assigned values.

d) Do not close out of the Solver Results dialog box.

e) On the Solver dialog box, run a Sensitivity and a Limits report.

f) In the area indicated in the Part 2 section of the Solver Parts 2 and 3 tab, type a short paragraph explaining what these reports indicate.

3. In this step, you will adjust the variables and constraints and run Solver again.

a) Suppose management has altered their decision and has increased fabrication time from 480 minutes to 600 minutes and increased the

minimum number of chairs to be produced from 10 to 16. Run Solver again and list the number of tables and chairs that should be produced in the provided table under Part 3a of the worksheet.

b) Now suppose too many tables have been rejected by quality assurance, and the production line for tables will be slowed, increasing fabrication

time to 26 minutes. However, management has also found a way to decrease fabrication time on the chairs to three minutes. Use the original

constraints for fabrication time (480 minutes) and the minimum tables (4) and chairs (10) and run Solver again. List the number of tables and

chairs that should be produced in the provided table under section Part 3b of the worksheet.

4. Use the tab labeled Solver Part 4 to complete the following:

a) Create and program a spreadsheet with data based on the following scenario:

Scenario

Your company has two trucks that it wishes to use on a specific contract. One is a new truck the company is making

payments on, and one is an old truck that is fully paid for. The new truck’s costs per mile are as follows:

 Fuel/additives: 54¢

 Truck payments: 24¢

 Driver: 36¢

 Repairs: 12¢

 Miscellaneous: 1¢

The old truck’s costs are as follows:

 Fuel/additives: 60¢

 Truck payments: 0¢

 Rookie driver: 32¢

 Repairs: 24¢

 Miscellaneous: 1¢

The company knows that truck breakdowns lose customers, so they have capped estimated repair costs at $14,000. The

total distance involved is 90,000 miles (to be divided between the two trucks). After you set up and program your table,

be sure to provide a rationale statement.

b) Use Solver to determine the number of miles each truck should be driven guidelines for Submission: Your assignment must be submitted using the Module Six Assignment Spreadsheet. Use 11-point Calibri font