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

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 28, 2024

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

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

image text in transcribed

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