Construct a spreadsheet model for each of the following exercises and then use the model to build a simulation model.
a.Jason Enterprises faces uncertain future sales. Specifically, for the coming year, the firm€™s CFO described her sales expectations as follows: €œSales could be as high as $ 10,000,000 or as low as $ 7,000,000, but I could not tell you anything more.€ Gross profits are typically 25% of firm sales, what would you estimate gross profits to be for next year? If Jason€™s operating earnings are typically 25% of firm sales, what would, you estimate earning to be for next year? Construct a spreadsheet model and incorporate consideration for the uncertainty in the future revenue to estimate the expected gross profit for the firm.
b. In spring 2015, Aggiebear Dog Snacks Inc. estimates its gross profits (revenue less cost of goods sold) for 2016. The firm€™s CFO recently attended a two-day seminar on the use of simulation and asked his analyst to construct a simulation model to make the estimate. To help guide the analyst, the CFO prepared the following table:

1. Construct a spreadsheet model for Aggiebear€™s gross profit.
2. Use the information provided above to convert your spreadsheet model into a simulation model.
3. Run your simulation model for 10,000 iterations. What is the expected level of gross profit for 2016? What is the probability that the gross profit will fall below $ 3.5 million?

Transcribed Image Text:

lncome Statement Revenue ariable Description Mininun$18 millionmost likely $25 million, maximum- 35 million 70% to 80% of revenue Cost of goods sold