Question

Please solve in Excel format and show step-by-step formulas

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

	Annual after tax profit
a)	Sale	100000
	operating cost	40000
	Loan repayement	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 cost	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 repayment	15400	15400	15400	15400	15400
	Profit	44600	20600	6200	-2440	-7624
	Tax	6690	3090	930	-366	-1143.6
	After Tax profit	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.