please fill in excel accordingly based off the directions in the word document
Question2: Consider a newspaper vendor selling papers on the corner. For each Sunday, the vendor must decide how many Sunday papers to buy at the wholesale price. The vendor pays $1.00 for each newspaper and sells them for $3.00 each. The shortage cost is considered to be equal to the profit foregone for not having enough papers to meet the demand. At the end of the day (Sunday), any unsold papers are sold back to the wholesaler at half of the purchasing price. The demand for the Sunday papers is normally distributed with a mean of 200 and a standard deviation of 10 papers. The vendor currently orders 200 paper for each Sunday. Use the template (Question 2 Template.xlsx) file to answer the following simulation questions. (a) Simulate vendor's ordering policy for 10 Sundays. Assume the demand for these 10 Sundays are as follows: 1 2 3 4 5 6 7 8 9 10 Demand 195 193 202 210 204 191 215 187 201 214 Report the following results for this simulation: Total Profit Average Profit/Sunday Total Shortage Cost Number of Shortages (b) Simulate vendor's ordering policy for 104 Sundays (about 2 years). Note the demand for the Sunday paper on each Sunday is normally distributed with a mean of 200 and a standard deviation of 10 papers. Given below is the Excel function to generate this random demand for each Sunday =ROUND(ABS(NORMINV(RANDO, 200, 10)),0) Report the following results for this simulation: Total Profit Average Profit/Sunday Total Shortage Cost Number of Shortages il Ilinniaristinn tabia tin ulcharkha n O Bie * $ DUHOU MUST Sca. Use the template (Question 2 Template.xlsx) file to answer the following simulation questions (a) Simulate vendor's ordering policy for 10 Sundays. Assume the demand for these 10 Sundays are as follows: 1 2 3 4 5 6 7 8 9 10 Demand 195 193 202 210 204 191 215 187 201 214 Report the following results for this simulation: Total Profit Average Profit/Sunday Total Shortage Cost Number of Shorts (b) Simulate vendor's ordering policy for 104 Sundays (about 2 years). Note the demand for the Sunday paper on each Sunday is normally distributed with a mean of 200 and a standard deviation of 10 papers. Given below is the Excel function to generate this random demand for each Sunday -ROUND(ABS(NORMINV(RANDO, 200, 10).0) Report the following results for this simulation: Total Profit Average Profit Sunday Total Shortage Cost Number of Shortages (c) Using the simulation table of Question 2(b), check the performances by two ordering quantities: Q-210 and Q-190. Looking at the profit and shortage information, which of these three ordering policy do you suggest to the vendor? Note: Each time you run the simulation, the random numbers (demand) change and therefore the performance values will vary. Try several times to see which ordering policy seem to be giving better performances and make your recommendation accordingly. Save O S u u estion Template (1) Protected View Search Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View C Question 2 D E Question 2(a) Problem Data (Given) Order Quantity Purchase Price Sell Price Shortage Cost Salvage Value Result (Question 2a) Total Profit Average Profit/Sunday Total Shortage Cost Number of Shortages Sunday Demand Sold Sales Loss Purchase Cost Revenue (shortage) Salvage Sunday Profit 195 193 202 210 201 191 215 mm 00 187 201 214 Total Question Template Type here to search caro coppolacc - 0 Comment Share wew. Enable Editing P Q R S T U Question 2(b) Result (Question 2b) Total Profit Average Profit /Sunday Total Shortage Cost Number of Shortages Equations to be entered Column M-ROUND(ABS(NORMINV(RANDO, 200, 10)),) Column N-min(M14,SD$6) First Row Column 0 =N14*SD$8 First Row Column PSD$6*SD$7 First Row Column Q-MAX(M14-SD$6,0)'$D$9 First Row Column R -MAX(D$3 M14,0)*$D$7 First Row Column S -014+R14-P14-014 First Row Sunday Demand Sold Sales Purchase Revenue Cost Loss (shortage) Salvage Sunday Profit A . d o 9:33 AM 2/19/2020
