The nancial manager of the Villard Electric Company Fred Taylor has presented his

QUESTION

The nancial manager of the Villard Electric Company, Fred Taylor, has presented his estimates of cash ows resulting from the possible investment in a new computer system, the Webnet. Mr. Taylors esti- mates of net cash ows immediately and over the following four years are as follows: Item Initial First Y ear Second Y ear Third Y ear Fourth Y ear Purchase of computer system Sale of computer system $200,000 $40,000 Tax on sale of computer system 12,442 Acquisition and disposition cash ows $200,000 $0 $0 $0 $52,442 Change in expenses $50,000 $50,000 $50,000 $50,000 Change in depreciation 40,000 64,000 38,400 23,040 Change in taxable income $10,000 $14,000 $11,600 $26,960 Less: change in tax 3,600 5,040 4,176 9,706 Change in income after tax $6,400 $8,960 $7,424 $17,254 Change in depreciation 40,000 64,000 38,400 23,040 Change in operating cash ows $46,400 $55,040 $45,824 $40,294 Change in net cash ows $200,000 $46,400 $55,040 $45,824 $92,736 M r . T aylor has based his estimates on the following assumptions: The cost of the system (including installation) is $200,000. The system will be depreciated as a 5-year asset under the MACRS, but it will be sold at the end of the fourth year for $50,000. Villards expenses will decline by $50,000 in each of the four years. The companys tax rate will be 36%. Working capital will not be affected. When he made his presentation to Villards board of directors, Mr. Taylor was asked to perform additional analyses to consider the following uncertainties: The cost of the system may be as much as 20% higher or as low as 20% lower. The change in expenses may be 30% higher or 20% lower than anticipated. The tax rate may be lowered to 30%. a. Reestimate the projects cash ows to consider each of the possi- ble variations in the assumptions, altering only one assumption each time. Using a spreadsheet program will help with the calcu- lations. b. Discuss the impact that each of the changes in assumptions has on the projects cash ows.
Solution The current cash flow estimates have errors which have been corrected in the below table of cash flows and this will be used as an base case cash flow scenario. 1. Sale of computer system is for 50,0000 and not 40,000 2. Book value of computer on sale = 34,560 (200,000 40,000-64,000-38,400-23,040) 3. Profit on sale of computer = 15,440 (50,000-34,560) 4. Tax on profit on sale of computer = 36%*15,440 = 5,558 BASE CASE Tax rate 36% Implied MACRS depreciation rate 20.00% 32.00% 19.20% 11.52% Item Initial First Year Second Year Third Year Fourth Year Purchase of computer system (a) $ -2,00,000 Sale of computer system (b) $ 50,000 Tax on sale of computer system (c) $ 5,558 Acquisition and disposition cash ?ows (d)=(a)+(b)+(c) $ -2,00,000 $ $ $ $ 55,558 Change in expenses [A] $ 50,000 $ 50,000 $ 50,000 $ 50,000 Change in depreciation [B] $ 40,000 $ 64,000 $ 38,400 $ 23,040 Change in taxable income [C] = [A]-[B] $ 10,000 $ -14,000 $ 11,600 $ 26,960 Less: change in tax @ 36% [D] = [C]*tax rate $ 3,600 $ -5,040 $ 4,176 $ 9,706 Change in income after tax [E] = [C]-[D] $ 6,400 $ -8,960 $ 7,424 $ 17,254 Change in depreciation [F] = [B] $ 40,000 $ 64,000 $ 38,400 $ 23,040 Change in operating cash ?ows [G] = [E]+[F] $ 46,400 $ 55,040 $ 45,824 $ 40,294 Change in net cash ?ows [H] = [G] +(d) $ -2,00,000 $ 46,400 $ 55,040 $ 45,824 $ 95,853 Scenario 1: Cost of system 20% higher Tax rate 36% Implied MACRS depreciation rate 20.00% 32.00% 19.20% 11.52% Item Initial First Year

cond Year Third Year Fourth Year Purchase of computer system (a) $ -2,40,000 Sale of computer system (b) $ 50,000 Tax on sale of computer system (c) $ 3,070 Acquisition and disposition cash ?ows (d)=(a)+(b)+(c) $ -2,40,000 $ $ $ $ 53,070 Change in expenses [A] $ 50,000 $ 50,000 $ 50,000 $ 50,000 Change in depreciation [B] $ 48,000 $ 76,800 $ 46,080 $ 27,648 Change in taxable income [C] = [A]-[B] $ 2,000 $ -26,800 $ 3,920 $ 22,352 Less: change in tax @ 36% [D] = [C]*tax rate $ 720 $ -9,648 $ 1,411 $ 8,047 Change in income after tax [E] = [C]-[D] $ 1,280 $ -17,152 $ 2,509 $ 14,305 Change in depreciation [F] = [B] $ 48,000 $ 76,800 $ 46,080 $ 27,648 Change in operating cash ?ows [G] = [E]+[F] $ 49,280 $ 59,648 $ 48,589 $ 41,953 Change in net cash ?ows [H] = [G] +(d) $ -2,40,000 $ 49,280 $ 59,648 $ 48,589 $ 95,023 Difference in cash flows vs. base case $ -40,000 $ 2,880 $ 4,608 $ 2,765 $ -829 Impact on project cash flows: Increase in cost of computer decreases cash flows overall Scenario 2: Cost of system 20% lower Tax rate 36% Implied MACRS depreciation rate 20.00% 32.00% 19.20% 11.52% Item Initial First Year Second Year Third Year Fourth Year Purchase of computer system (a) $ -1,60,000 Sale of computer system (b) $ 50,000 Tax on sale of computer system (c) $ 31,870 Acquisition and disposition cash ?ows (d)=(a)+(b)+(c) $ -1,60,000 $ $ $ $ 81,870 Change in expenses [A] $ 50,000 $ 50,000 $ 50,000 $ 50,000 Change in depreciation [B] $ 48,000 $ 76,800 $ 46,080 $ 27,648 Change in taxable income [C] = [A]-[B] $ 2,000 $ -26,800 $ 3,920 $ 22,352 Less: change in tax @ 36% [D] = [C]*tax rate $ 720 $ -9,648 $ 1,411 $ 8,047 Change in income after tax [E] = [C]-[D] $ 1,280 $ -17,152 $ 2,509 $ 14,305 Change in depreciation [F] = [B] $ 48,000 $ 76,800 $ 46,080 $ 27,648 Change in operating cash ?ows [G] = [E]+[F] $ 49,280 $ 59,648 $ 48,589 $ 41,953 Change in net cash ?ows [H] = [G] +(d) $ -1,60,000 $ 49,280 $ 59,648 $ 48,589 $ 1,23,823 Difference in cash flows vs. base case $ 40,000 $ 2,880 $ 4,608 $ 2,765 $ 27,971 Impact on project cash flows: Decrease in cost of computer increases cash flows overall Scenario 3: change in expenses 30% higher Tax rate 36% Implied MACRS depreciation rate 20.00% 32.00% 19.20% 11.52% Item Initial First Year Second Year Third Year Fourth Year Purchase of computer system (a) $ -2,00,000 Sale of computer system (b) $ 50,000 Tax on sale of computer system (c) $ 5,558 Acquisition and disposition cash ?ows (d)=(a)+(b)+(c) $ -2,00,000 $ $ $ $ 55,558 Change in expenses [A] $ 65,000 $ 65,000 $ 65,000 $ 65,000 Change in depreciation [B] $ 40,000 $ 64,000 $ 38,400 $ 23,040 Change in taxable income [C] = [A]-[B] $ 25,000 $ 1,000 $ 26,600 $ 41,960 Less: change in tax @ 36% [D] = [C]*tax rate $ 9,000 $ 360 $ 9,576 $ 15,106 Change in income after tax [E] = [C]-[D] $ 16,000 $ 640 $ 17,024 $ 26,854 Change in depreciation [F] = [B] $ 40,000 $ 64,000 $ 38,400 $ 23,040 Change in operating cash ?ows [G] = [E]+[F] $ 56,000 $ 64,640 $ 55,424 $ 49,894 Change in net cash ?ows [H] = [G] +(d) $ -2,00,000 $ 56,000 $ 64,640 $ 55,424 $ 1,05,453 Difference in cash flows vs. base case $ $ 9,600 $ 9,600 $ 9,600 $ 9,600 Impact on project cash flows: Higher savings in expense increases cash flows overall Scenario 4: change in expenses 20% lower Tax rate 36% Implied MACRS depreciation rate 20.00% 32.00% 19.20% 11.52% Item Initial First Year Second Year Third Year Fourth Year Purchase of computer system (a) $ -2,00,000 Sale of computer system (b) $ 50,000 Tax on sale of computer system (c) $ 5,558 Acquisition and disposition cash ?ows (d)=(a)+(b)+(c) $ -2,00,000 $ $ $ $ 55,558 Change in expenses [A] $ 40,000 $ 40,000 $ 40,000 $ 40,000 Change in depreciation [B] $ 40,000 $ 64,000 $ 38,400 $ 23,040 Change in taxable income [C] = [A]-[B] $ $ -24,000 $ 1,600 $ 16,960 Less: change in tax @ 36% [D] = [C]*tax rate $ $ -8,640 $ 576 $ 6,106 Change in income after tax [E] = [C]-[D] $ $ -15,360 $ 1,024 $ 10,854 Change in depreciation [F] = [B] $ 40,000 $ 64,000 $ 38,400 $ 23,040 Change in operating cash ?ows [G] = [E]+[F] $ 40,000 $ 48,640 $ 39,424 $ 33,894 Change in net cash ?ows [H] = [G] +(d) $ -2,00,000 $ 40,000 $ 48,640 $ 39,424 $ 89,453 Difference in cash flows vs. base case $ $ -6,400 $ -6,400 $ -6,400 $ -6,400 Impact on project cash flows: Lower savings in expense decreases cash flows overall Scenario 5: tax rate at 30% Tax rate 30% Implied MACRS depreciation rate 20.00% 32.00% 19.20% 11.52% Item Initial First Year Second Year Third Year Fourth Year Purchase of computer system (a) $ -2,00,000 Sale of computer system (b) $ 50,000 Tax on sale of computer system (c) $ 4,632 Acquisition and disposition cash ?ows (d)=(a)+(b)+(c) $ -2,00,000 $ $ $ $ 54,632 Change in expenses [A] $ 50,000 $ 50,000 $ 50,000 $ 50,000 Change in depreciation [B] $ 40,000 $ 64,000 $ 38,400 $ 23,040 Change in taxable income [C] = [A]-[B] $ 10,000 $ -14,000 $ 11,600 $ 26,960 Less: change in tax @ 36% [D] = [C]*tax rate $ 3,000 $ -4,200 $ 3,480 $ 8,088 Change in income after tax [E] = [C]-[D] $ 7,000 $ -9,800 $ 8,120 $ 18,872 Change in depreciation [F] = [B] $ 40,000 $ 64,000 $ 38,400 $ 23,040 Change in operating cash ?ows [G] = [E]+[F] $ 47,000 $ 54,200 $ 46,520 $ 41,912 Change in net cash ?ows [H] = [G] +(d) $ -2,00,000 $ 47,000 $ 54,200 $ 46,520 $ 96,544 Difference in cash flows vs. base case $ $ 600 $ -840 $ 696 $ 691 Impact on project cash flows: Lower taxes increases cash flows overall

ANSWER:

CLICK REQUEST FOR  AN EXPERT SOLUTION