- Details
- Parent Category: Programming Assignments' Solutions
We Helped With This Excel Programming Homework: Have A Similar One?
Short Assignment Requirements
Assignment Description
QUESTION 1 (60 points)
You are considering a road-trip from Seattle to Denver. Here is a map of relevant paths you would consider. The various paths have the travel times (in hours) listed. For example, it takes three hours to get from Seattle to Portland. There are numbers in parenthesis on each path which correspond to the actual highway/interstate. You’d like to find the quickest route.
Question: What is the best route from Seattle to Denver? Please answer the four parts below.
(1) Please build a spreadsheet model to provide an answer & neatly cut and paste it into the space below [30 points]. Make sure to follow all color-coding conventions [10 points].
(2) Also take a picture/snapshot of the final solver dialogue box (where you wire the spreadsheet to Solver, specifying the objective function, decision variables, constraints) and include it below. [10 points]
(3) Is the model you
built a LP? Why or why not? [5 points] |
QUESTION 2 (40 points)
You make two types of barbeques (bbq): a regular version and a deluxe version. Your barbeques are special because they are made of special stones. You use both small-sized and large-sized stones to make your bbqs in addition to labor hours. You’ve already setup a LP to decide how many bbqs of each type to make a week to maximize profit, given your resources of small-sized stones, large-sized stones, and labor. The sensitivity report is provided below.
Variable Cells |
|
|
|
|
|
| ||
|
|
| Final | Reduced | Objective | Allowable | Allowable |
|
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease |
|
| $B$7 | # units deluxe bbq | 3 | 0 | 250 | 50 | 100 |
|
| $C$7 | # units regular bbq | 2 | 0 | 150 | 100 | 25 |
|
|
|
|
|
|
|
|
|
|
Constraints |
|
|
|
|
|
| ||
|
|
| Final | Shadow | Constraint | Allowable | Allowable |
|
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease |
|
| $E$2 | small stones | 10 | 25 | 10 | 6 | 2 |
|
| $E$3 | large stones | 8 | 0 | 15 | 1E+30 | 7 |
|
| $E$4 | labor hours | 40 | 20 | 40 | 10 | 15 |
|
|
|
|
|
|
|
|
|
|
Please answer the questions below.
(1a) [2 pts] What is the optimal number of deluxe bbqs to make each week?
(1b) [2 pts] What is the optimal number of regular bbqs to make each week?
(1c) [2 pts] How many large stones are you currently using to make these bbqs?
(1d) [2 pts] How many
hours of labor do you currently have available per week? (2a) [2 pts] What is your profit per deluxe bbq?
(2b) [2 pts] What is your profit per regular bbq?
(3a) [4 pts] How much is an additional small stone worth to you? Why?
(3b) [4 pts] How much is an additional large stone worth to you? Why?
(4) [5 pts] One week, one employee can not come to work due to the flu. You lose 20 hours of labor that week. How much profit do you lose? Explain in detail.
(5a) [5 pts] What is the value of this bundle: 3 additional small stones and 3 additional large stones. Show your calculations and reasoning in detail. (5b) [5 pts] Suppose you obtain the bundle in part (5a), does the optimal solution stay the same? Explain in detail.
(6) [5 pts] Suppose you were off on your profit estimates and the true profits were $200 for the deluxe bbqs and $175 for the regular bbqs. Does the optimal solution stay the same for that set of simultaneous changes in the coefficients of the objective function? Explain in detail and show any relevant calculations. |
