60.1K

Verified Solution

Link Copied!

1. Part B: (15 Points) Consider the partial solution to Pierre Simulation that we covered in the class in the worksheet Q1. Please note the prices are different in this spreadsheet from our original example but the structure of the problem is the same. If any loaves of bread are left over at the end of the day, the charitable kitchen will now only take up to 20 loaves at a salvage price of $1.25. That is, Pierre can only sell up to a maximum of 20 loaves to the charitable kitchen. If he has more than 20 loaves left over, any loaves over 20 will have to be thrown away. How would now calculate the salvage revenues in Cell F14 in the worksheet Q1? Please enter your equation directly into Cell F14. Part B: (15 Points) Consider the following probability distribution for weekly demand for a particular product that you are going to use within a simulation. Set up the VLOOKUP table that will generate demand from this distribution. Answer this question in the sheet labeled Q1. Also, show the statement you will use in the simulation (in Cell A20) to generate random demand for Week 1 from this distribution.

Demand	Probability
20	0.035
25	0.145
30	0.225
35	0.275
40	0.155
45	0.105
50	0.060

A G a 36 36 48 B D E F H 1 Pierre's Bakery Simulation | 2 3 Cost Data Demand Distribution Data Table 4 Price of a Loaf $ 3.50 Ranges Demand $ 5 Cost of a Loaf $ 1.75 0.00 $ 6 Cost of Lost Profits $ 0.75 0.10 48 $ 7 Salvage Price $ 1.25 0.35 60 60 $ 8 0.65 72 72 $ 9 0.85 84 84 $ 10 Ordering Policy = 60 0.95 96 96 $ 11 12 Demand Lost Salvage 13 Day Random No Demand Revenue Profit Revenue Profit 14 1 0.20126 48 $168.00 $ - $ 15.00 $ 78.00 - - - A G a 36 36 48 B D E F H 1 Pierre's Bakery Simulation | 2 3 Cost Data Demand Distribution Data Table 4 Price of a Loaf $ 3.50 Ranges Demand $ 5 Cost of a Loaf $ 1.75 0.00 $ 6 Cost of Lost Profits $ 0.75 0.10 48 $ 7 Salvage Price $ 1.25 0.35 60 60 $ 8 0.65 72 72 $ 9 0.85 84 84 $ 10 Ordering Policy = 60 0.95 96 96 $ 11 12 Demand Lost Salvage 13 Day Random No Demand Revenue Profit Revenue Profit 14 1 0.20126 48 $168.00 $ - $ 15.00 $ 78.00

Answer & Explanation Solved by verified expert

See Answer