# Question: Construct a spreadsheet model for each of the following exercises

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?

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?

## Answer to relevant Questions

Rayner Aeronautics is considering a $ 12.5 million investment that has an estimated project free cash flow (FCF) of $ 2 million in its first year of operations. The project has a five-year life, and Rayner requires a return ...The Clayton Manufacturing Company is considering an investment in a new automated inventory system for its warehouse that will provide cash savings to the firm over the next five years. The firm’s CFO anticipates ...On July 15, 2014 the Ford motor company (F) had a $ 2 billion bond issue outstanding that it issued the previous year. The bonds have a $ 1,000 par value and pay a fixed coupon of 4.75% paid semi-annually on July 15 and ...Nestlé Enterprises is estimating its cost of capital for the first time and has made the following estimates: The firm’s debt carries a AAA rating, which is currently yielding 6%; the firm pays taxes at a rate of 30%; the ...The term hurdle rate is often used in the context of project evaluation and is sometimes used to refer to the risk-adjusted discount rate—i.e., the required rate of return on a project with a given level of risk. The risk ...Post your question