Recreate spreadsheet template 7.4

Solve the following problem using the spreadsheet you created. Some modifications of the original template may be necessary.

Problem: The owner of a pizza shop is considering buying a new delivery vehicle. A new vehicle would result in a savings of $8,000 per year over the old vehicle. The new vehicle (5-year property) would cost $25,000 and would have a useful life of eight years, at which time it would be sold for $2,500. The owner uses a 20% before-tax MARR, and the vehicle would be depreciated by using MACRS. Should the owner purchase the van on an after-tax basis? Use a 40% tax rate.

Would the vehicle have been purchased?

ETax Analysis,MACRS Depreciation Template7.4 40% 10% d(k) Taxable Income ATCF Adjusted BTCF Year Income Tax Due ATCF ($100,000) ($100,000) $0 $20,000 $20,000 $20,000 $32,000 ($12,000) $4,800 $24,800 $24,800 $800 ($320) $19,680 $19,680 $20,000 $11,520 $8,480 ($3,392) $16,608 $16,608 $20,000 $11,520 $8,480 ($3,392) $16,608 $16,608 20,000 $5,760 $14,240 (5,66 $14,304 $14,304 $20,000 note $20,000 ($8,000) $12,000$30,000 01 ($100,000) $20,000$20,000 $0 $20,000 $19,200 12 13 $30,000 ($12,000) $18,000! NPV= IRR = 0ool *note* ($1,411.71) $290 957% User input Unique formula note copy depreciation formula only to vear (N+1) 24D3 (B5>=15,1.52) 26 G8 2309 B9C9 30F9 B9-E9 VDB(-$B$8,0,$B$5,MAX(0,A9-1.5),A9-0.5,SD$3,0) $B$3*D9 F15+F16 31 G15 G18 NPV(B4,G9:G15)+G8 321 33 G19 PMT(B4,7,G18) 320 RR(G8 G15,B4) ETax Analysis,MACRS Depreciation Template7.4 40% 10% d(k) Taxable Income ATCF Adjusted BTCF Year Income Tax Due ATCF ($100,000) ($100,000) $0 $20,000 $20,000 $20,000 $32,000 ($12,000) $4,800 $24,800 $24,800 $800 ($320) $19,680 $19,680 $20,000 $11,520 $8,480 ($3,392) $16,608 $16,608 $20,000 $11,520 $8,480 ($3,392) $16,608 $16,608 20,000 $5,760 $14,240 (5,66 $14,304 $14,304 $20,000 note $20,000 ($8,000) $12,000$30,000 01 ($100,000) $20,000$20,000 $0 $20,000 $19,200 12 13 $30,000 ($12,000) $18,000! NPV= IRR = 0ool *note* ($1,411.71) $290 957% User input Unique formula note copy depreciation formula only to vear (N+1) 24D3 (B5>=15,1.52) 26 G8 2309 B9C9 30F9 B9-E9 VDB(-$B$8,0,$B$5,MAX(0,A9-1.5),A9-0.5,SD$3,0) $B$3*D9 F15+F16 31 G15 G18 NPV(B4,G9:G15)+G8 321 33 G19 PMT(B4,7,G18) 320 RR(G8 G15,B4)