Question

17. After spending ten years as an assistant manager for a large restaurant chain, Ray Clark has decided

    to become his own boss. The owner of a local submarine sandwich store wants to sell the store to Ray for $65,000 to be paid in installments of $13,000 in each of the next five years. According to the current owner, the store brings in revenue of about $110,000 per year and incurs operating costs of about 63% of sales. Thus, once the store is paid for, Ray should make about $35,000–$40,000 per year before taxes. Until the store is paid for, he will make substantially less—but he will be his own boss. Realizing that some uncertainty is involved in this decision, Ray wants to simulate what level of net income he can expect to earn during the next five years as he operates and pays for the store. In particular, he wants to see what could happen if sales are allowed to vary uniformly between $90,000 and $120,000, and if operating costs are allowed to vary uniformly between 60% and 65% of sales. Assume that Ray’s payments for the store are not deductible for tax purposes and that he is in the 28% tax bracket.

18. Create a spreadsheet model to simulate the annual net income Ray would receive during each of the next five years if he decides to buy the store.

19. Given the money he has in savings, Ray thinks he can get by for the next five years if he can make at least $12,000 from the store each year. Replicate the model 500 times and track: 1) the minimum amount of money Ray makes over the five-year period represented by each replication, and 2) the total amount Ray makes during the five-year period represented by each replication.

20. What is the probability that Ray will make at least $12,000 in each of the next five years?

21. What is the probability that Ray will make at least $60,000 total over the next five years?

***using @RISK***

Answer 1