Order from us for quality, customized work in due time of your choice.
submit in spreadsheet attached
This exercise is designed to give you experience practicing using Solver before your final project submission, which is due in Module Seven. It’s recommended that you complete this assignment as early as possible. That way, you’ll have time to post to the General Questions discussion if you have any questions or problems.
Directions
In this assignment, the scenario is to figure out which combination of manufacturing tables and chairs is the most profitable. The Linear Programming Solver Document, linked in Supporting Materials, will help you with the assignment. To complete this assignment, follow the steps outlined below:
1. Part 1 of this assignment is ungraded, but it will help you understand how a table is set up to run Solver.
A. Open the Module Six Assignment Spreadsheet, linked in What to Submit, and make sure you are on the tab (worksheet) titled “Solver Part 1.” You’ll see a spreadsheet that’s 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 show one method.)
i. The table highlighted in gray shows the specific amounts of material, fabrication time, and output needed for each chair and table. It also shows the total cost of producing each item.
ii. The table in blue calculates total profit from producing both tables and chairs.
iii. The table in green tracks the amount of materials and fabrication consumed to produce the volume that will be decided.
iv. 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 look at the programming used, but don’t change anything.
C. After you review the programming, 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. You can see which cell has been identified as the target cell and which cells Solver will change. You can also see how the constraints have been entered. Don’t run Solver at this time; simply click Close on the dialog box.
2. Now you’ll practice running Solver and viewing two of its associated reports. Click on the tab at the bottom on the screen titled “Solver Parts 2 & 3.” You’ll see the same data tables from Part 1. Follow these steps:
A. At the top of the worksheet, click on Data, then Solver.
B. Find the Solver Parameters dialog box and 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 report and a Limits report.
F. In the area indicated in the Part 2 section of the “Solver Parts 2 & 3” tab, type a short paragraph explaining what these reports indicate.
3. In this step, you’ll change the variables and constraints and run Solver again.
A. Imagine management has changed their decision and has increased fabrication time from 480 minutes to 600 minutes. They’ve also increased the minimum number of chairs to be produced from 10 to 16. Run Solver again. List the number of tables and chairs that should be produced in the provided table under Part 3a of the worksheet.
B. Now imagine too many tables have been rejected by quality assurance. 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 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. The other 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). Set up and program your table, then be sure to write a rationale statement.
B. Use Solver to determine the number of miles each truck should be driven.
Specifically, you must address the following rubric criteria:
1. Run Sensitivity and Limits Reports correctly, then explain what they indicate.
2. Adjust variables and constraints to produce the correct Solver output.
3. Create a spreadsheet that correctly runs Solver.
4. Write a rationale statement that fully addresses programming decisions.
What to Submit
Your assignment must be submitted using the Module Six Assignment Spreadsheet. Use 11-point Calibri font.
Supporting Materials
The following resource supports your work on the assignment:
Reading: Linear Programming Solver Document
Order from us for quality, customized work in due time of your choice.