 Details
 Parent Category: Programming Assignments' Solutions
We Helped With This Excel Programming Homework: Have A Similar One?
Category  Programming 

Subject  Excel 
Difficulty  Undergraduate 
Status  Solved 
More Info  Help With Excel 
Short Assignment Requirements
Assignment Description
QUESTION 1 (60 points)
You are considering a roadtrip 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 colorcoding 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 smallsized and largesized 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 smallsized stones, largesized 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. 