Consider the partial solution to Pierre Simulation that we covered in the class in the worksheet Q1.
Question:
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. 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 |
Pierre's Bakery Simulation I | |||||||
Cost Data | Demand Distribution | Data Table | |||||
Price of a Loaf | $ 3.50 | Ranges | Demand | $ - | |||
Cost of a Loaf | $ 1.75 | 0.00 | 36 | 36 | $ - | ||
Cost of Lost Profits | $ 0.75 | 0.10 | 48 | 48 | $ - | ||
Salvage Price | $ 1.25 | 0.35 | 60 | 60 | $ - | ||
0.65 | 72 | 72 | $ - | ||||
0.85 | 84 | 84 | $ - | ||||
Ordering Policy = | 60 | 0.95 | 96 | 96 | $ - | ||
Demand | Lost | Salvage | |||||
Day | Random No | Demand | Revenue | Profit | Revenue | Profit | |
1 | 0.88231 | 84 | $ 210.00 | $ 18.00 | $ - | $ 87.00 |
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill