15 Feb Use Solver to determine the number of miles each truck should be driven
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:
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:
Truck payments: 24¢
The old truck’s costs are as follows:
Truck payments: 0¢
Rookie driver: 32¢
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
HOW OUR WEBSITE WORKS
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of
HIGH QUALITY & PLAGIARISM FREE.
To make an Order you only need to click ORDER NOW and we will direct you to our Order Page at WriteDen. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Deadline range from 6 hours to 30 days.
Once done with writing your paper we will upload it to your account on our website and also forward a copy to your email.
Upon receiving your paper, review it and if any changes are needed contact us immediately. We offer unlimited revisions at no extra cost.
Is it Safe to use our services?
We never resell papers on this site. Meaning after your purchase you will get an original copy of your assignment and you have all the rights to use the paper.
Our price ranges from $8-$14 per page. If you are short of Budget, contact our Live Support for a Discount Code. All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.
Please note we do not have prewritten answers. We need some time to prepare a perfect essay for you.