A B. Beaverclaw Garden Centre Fertilizer 1 2 3 Number Demanded Per Bag of Fertilizer: Revenue Cost 4 5 6 Number Ordered Number Sold at Full Price Number Sold at Discount Price Unsatisfied Demand 750 750 750 0.00 7 8 9 10 Net Profit $0.00 Before you proceed with the simulation, check your model. Check the Model Logic: Try at least 5 values to check the model logic by clicking the Random/Static Standard (F9) Recalc-button in the formulas tab (which looks like a pair of dice) and then press F9. The net profit should be displaying values of about $8,200- $9,000 in Cell C10 for most replications. a) Create the spreadsheet model. Use it to determine the average expected net profit when 750 bags of fertilizer are ordered for this season. Beaverclaw Garden Centre carries a wide selection of plants and related supplies but only orders the specialized Blues Spruce Tree Fertilizer one time in early spring. This year, they want to stock this item that costs them $15.00 to order and retails for $27.00. If they do not sell all of them by the end of July, they will be discounted by 60 percent and can always be sold easily. Beaverclaw Garden Centre is unsure of the exact demand for the bags of fertilizer but they expect increased sales this year because of an increased interest in gardening; likely a demand of about 750 bags with a Poisson distribution. Their dilemma is deciding how many bags to order for this season. If demand exceeds the order quantity, then Beaverclaw loses a profit opportunity. On the other hand, if too many are ordered, they will lose money by discounting them below cost. Part a): Beaverclaw first wants to examine the statistics associated with ordering exactly 750 bags, their expected demand. Part b): They then want to examine the impact of ordering between 600 and 900 bags. The manufacturer requires that the bags be ordered in batch sizes of 50 because of pallet shipping constraints. a) Program Excel: In B3, set the Distribution to Poisson with a lambda rate of 750. In Column C, rows 5 to 8, enter the appropriate revenue (+) or cost (-), including an opportunity cost in C8, calculated & formatted in dollar $. a) Create the spreadsheet model. Use it to determine the average expected net profit when 750 bags of fertilizer are ordered for this season. Add 2 Cells as RiskOutput (these are highlighted in blue on your spreadsheet): Put your cursor on the unsatisfied demand output cell (cell B8) and click on the Add Output icon. Then put your cursor on the Net Profit output cell (cell C10) and click on the Add Output icon. Unsatisfied Demand 14.00 -$ Net Profit $8. @RISK - Add/Edit Output: Cell B8 Name: Unsatisfied Demand / Fertilizer Add s Output F ? Remove OK Cancel Net Profit $8,784.00 @RISK - Add/Edit Output: Cell C9 Name: Net Profit / Per Bag of Fertilizer: Revenue/Cost fr ? Remove OK Cancel On the formula bar you should see the following for the Unsatisfied Demand and Net Profit: =RiskOutput()+MAX(B3-B5,0) =RiskOutput()+SUMPRODUCT(B5:38,C5:08) For the settings ensure you use 1000 iterations; For the Initial Seed Value you must use a fixed seed; you must use your group number so that every group has a (slightly) different solution. @RISK - Simulation Settings x Settings General view Sampling Macros Corovergence Random Numbers Sampling Type Latin Hypercube Generator Mersenne Twister Initial Seed Fixed Multiple Simulations All Use Same Seed C10 fx =SUMPRODUCT(B5:B8,C5:08) A B Beaverclaw Garden Centre Fertilizer 1 2 3 Number Demanded Per Bag of Fertilizer: Revenue/Cost 4 5 6 Number Ordered Number Sold at Full Price Number Sold at Discount Price Unsatisfied Demand 750 750 750 0.00 7 8 9 10 Net Profit $0.00 A B. Beaverclaw Garden Centre Fertilizer 1 2 3 Number Demanded Per Bag of Fertilizer: Revenue Cost 4 5 6 Number Ordered Number Sold at Full Price Number Sold at Discount Price Unsatisfied Demand 750 750 750 0.00 7 8 9 10 Net Profit $0.00 Before you proceed with the simulation, check your model. Check the Model Logic: Try at least 5 values to check the model logic by clicking the Random/Static Standard (F9) Recalc-button in the formulas tab (which looks like a pair of dice) and then press F9. The net profit should be displaying values of about $8,200- $9,000 in Cell C10 for most replications. a) Create the spreadsheet model. Use it to determine the average expected net profit when 750 bags of fertilizer are ordered for this season. Beaverclaw Garden Centre carries a wide selection of plants and related supplies but only orders the specialized Blues Spruce Tree Fertilizer one time in early spring. This year, they want to stock this item that costs them $15.00 to order and retails for $27.00. If they do not sell all of them by the end of July, they will be discounted by 60 percent and can always be sold easily. Beaverclaw Garden Centre is unsure of the exact demand for the bags of fertilizer but they expect increased sales this year because of an increased interest in gardening; likely a demand of about 750 bags with a Poisson distribution. Their dilemma is deciding how many bags to order for this season. If demand exceeds the order quantity, then Beaverclaw loses a profit opportunity. On the other hand, if too many are ordered, they will lose money by discounting them below cost. Part a): Beaverclaw first wants to examine the statistics associated with ordering exactly 750 bags, their expected demand. Part b): They then want to examine the impact of ordering between 600 and 900 bags. The manufacturer requires that the bags be ordered in batch sizes of 50 because of pallet shipping constraints. a) Program Excel: In B3, set the Distribution to Poisson with a lambda rate of 750. In Column C, rows 5 to 8, enter the appropriate revenue (+) or cost (-), including an opportunity cost in C8, calculated & formatted in dollar $. a) Create the spreadsheet model. Use it to determine the average expected net profit when 750 bags of fertilizer are ordered for this season. Add 2 Cells as RiskOutput (these are highlighted in blue on your spreadsheet): Put your cursor on the unsatisfied demand output cell (cell B8) and click on the Add Output icon. Then put your cursor on the Net Profit output cell (cell C10) and click on the Add Output icon. Unsatisfied Demand 14.00 -$ Net Profit $8. @RISK - Add/Edit Output: Cell B8 Name: Unsatisfied Demand / Fertilizer Add s Output F ? Remove OK Cancel Net Profit $8,784.00 @RISK - Add/Edit Output: Cell C9 Name: Net Profit / Per Bag of Fertilizer: Revenue/Cost fr ? Remove OK Cancel On the formula bar you should see the following for the Unsatisfied Demand and Net Profit: =RiskOutput()+MAX(B3-B5,0) =RiskOutput()+SUMPRODUCT(B5:38,C5:08) For the settings ensure you use 1000 iterations; For the Initial Seed Value you must use a fixed seed; you must use your group number so that every group has a (slightly) different solution. @RISK - Simulation Settings x Settings General view Sampling Macros Corovergence Random Numbers Sampling Type Latin Hypercube Generator Mersenne Twister Initial Seed Fixed Multiple Simulations All Use Same Seed C10 fx =SUMPRODUCT(B5:B8,C5:08) A B Beaverclaw Garden Centre Fertilizer 1 2 3 Number Demanded Per Bag of Fertilizer: Revenue/Cost 4 5 6 Number Ordered Number Sold at Full Price Number Sold at Discount Price Unsatisfied Demand 750 750 750 0.00 7 8 9 10 Net Profit $0.00