Question

EXCEL CRYSTAL BALL:

Not wanting to leave his beloved alma mater, Will Anderson has come up with a scheme to stay
around for 5 more years: He has decided to bid on the fast-food concession rights at the football
stadium. He feels sure that a bid of $60,000 will win the concession, which gives him the right to sell
food at football games for the next 5 years. He estimates that annual operating costs will be 40% of sales
and annual sales will average $100,000. His Uncle Josh has agreed to lend him the $60,000 to make the
bid. He will pay Josh $15,400 at the end of each year. His tax rate is 15%.

(a) Use a spreadsheet model to answer the following question. What is Will’s average annual after-tax
profit? Assume that the yearly payments of $15,400 are tax deductible.

(b) Suppose that sales will probably vary plus or minus 40% from the average of $100,000 each year.
Will is concerned about the minimum after-tax profit he can earn in a year. He feels that he can survive
if it is at least $20,000. Model annual sales for the 5 years as five continuous uniform random variables.
Based on a sample of 7,500 five-year periods (750 periods if using Excel alone), estimate the probability
that over any five-year period the minimum after-tax profit for a year will be at least $20,000. Should
Will bid for the concession?

Answer 1

Answer :

(a).

Annual after tax profit
Sale	100000
Operating cost	40000
Loan repayment	15400
Profit	44600
Tax	6690
After tax profit	37910

(b). Case 1 : Increase by 40%

	1	2	3	4	5	Average
Sales	100000	140000	196000	274400	384160
Operating	40000	56000	78400	109760	153664
Loan repayment	15400	15400	15400	15400	15400
Profit	44600	68600	102200	149240	215096
Tax	6690	10290	15330	22386	32264.4
After tax profit	37910	58310	86870	126854	182831.6	98555.12

Case 1: Decrease by 40%

	1	2	3	4	5	Average
Sales	100000	60000	36000	21600	12960
Operating cost	40000	24000	14400	8640	5184
Loan payment	15400	15400	15400	15400	15400
Profit	44600	20600	6200	-2440	-7624
Tax	6690	3090	930	-366	-1143.6
After tax	37910	17510	5270	-2074	-6480.4	10427.12
In the case if there are chances of fall in sale, then will should not bid for right. As in that case will not be able to pay loan repayment.