Question

Can you do in excel and include the excel show formulas

The Air Marshal Co. has recently completed a $10,000,000 two-year marketing study.  Based on the results of this study, Air Marshal has estimated that 800 units of its new security electro-optical human scanning hardware, known as "Marshal Dillon," could be sold annually over the next 12 years, at a price of $110,000 the first year with an estimated 2% annual rise from inflation in years 2-6.  The sales price is expected to drop to $90,000 in year 7 due to increasing competition with 2% annual increase for year 8-12.  Variable costs per unit are $45,000 with an estimated 4% annual rise from inflation in years 2-12 and incremental cash fixed costs total $15 million per year all 12 years.   

Answer 1

Year	Price	Variable Cost	Contribution per Unit	Units	Total Contribution	Fixed Cost	EBIT
1	110,000.00	45,000.00	65,000.00	800	52,000,000.00	15,000,000.00	37,000,000.00
2	112,200.00	46,800.00	65,400.00	800	52,320,000.00	15,000,000.00	37,320,000.00
3	114,444.00	48,672.00	65,772.00	800	52,617,600.00	15,000,000.00	37,617,600.00
4	116,732.88	50,618.88	66,114.00	800	52,891,200.00	15,000,000.00	37,891,200.00
5	119,067.54	52,643.64	66,423.90	800	53,139,121.92	15,000,000.00	38,139,121.92
6	121,448.89	54,749.38	66,699.51	800	53,359,606.20	15,000,000.00	38,359,606.20
7	90,000.00	56,939.36	33,060.64	800	26,448,515.33	15,000,000.00	11,448,515.33
8	91,800.00	59,216.93	32,583.07	800	26,066,455.95	15,000,000.00	11,066,455.95
9	93,636.00	61,585.61	32,050.39	800	25,640,314.19	15,000,000.00	10,640,314.19
10	95,508.72	64,049.03	31,459.69	800	25,167,750.75	15,000,000.00	10,167,750.75
11	97,418.89	66,610.99	30,807.90	800	24,646,321.26	15,000,000.00	9,646,321.26
12	99,367.27	69,275.43	30,091.84	800	24,073,471.80	15,000,000.00	9,073,471.80

Year	Price	Variable Cost	Contribution per Unit	Units	Total Contribution	Fixed Cost	EBIT
1	110000	45000	=+C4-D4	800	=+E4*F4	15000000	=+G4-H4
=+B4+1	=+C4*1.02	=+D4*1.04	=+C5-D5	800	=+E5*F5	15000000	=+G5-H5
=+B5+1	=+C5*1.02	=+D5*1.04	=+C6-D6	800	=+E6*F6	15000000	=+G6-H6
=+B6+1	=+C6*1.02	=+D6*1.04	=+C7-D7	800	=+E7*F7	15000000	=+G7-H7
=+B7+1	=+C7*1.02	=+D7*1.04	=+C8-D8	800	=+E8*F8	15000000	=+G8-H8
=+B8+1	=+C8*1.02	=+D8*1.04	=+C9-D9	800	=+E9*F9	15000000	=+G9-H9
=+B9+1	90000	=+D9*1.04	=+C10-D10	800	=+E10*F10	15000000	=+G10-H10
=+B10+1	=+C10*1.02	=+D10*1.04	=+C11-D11	800	=+E11*F11	15000000	=+G11-H11
=+B11+1	=+C11*1.02	=+D11*1.04	=+C12-D12	800	=+E12*F12	15000000	=+G12-H12
=+B12+1	=+C12*1.02	=+D12*1.04	=+C13-D13	800	=+E13*F13	15000000	=+G13-H13
=+B13+1	=+C13*1.02	=+D13*1.04	=+C14-D14	800	=+E14*F14	15000000	=+G14-H14
=+B14+1	=+C14*1.02	=+D14*1.04	=+C15-D15	800	=+E15*F15	15000000	=+G15-H15