Hello, Can You Please Help Me with this question? If you could show me your formulas in excel so I know how you got your answer that would be really helpful. I will make sure to give you a thumbs up. I have also attached a template (The Last Two Images) that is a guide to help with formatting. Thanks so much. Feel free to drop your Venm o lol.
You work for a consulting firm and have been asked by a client--InvestEd--to evaluate two project proposals. Your task is to develop a spreadsheet system for the proposals to calculate the project cash flows (free cash flows), calculate the criteria for evaluating the proposed projects, and graph the NPV profile for each project so the company can see the range of rates in which each project is worth. Finally, based on the information in the proposals, you need to advise InvestEd on which proposal they should select and explain why they should select it. 1. California Carnmetarbors InvestEd is considering enter the carnmetarbor prefabrication business in California. In reviewing the proposal, the InvestEd's CFO is using the following facts: The carnmetarbor production will require the company to purchase additional fixed assets that will cost $1,000,000 at t = 0. For tax purposes the facility will be depreciated on a straight-line basis to zero over 3 years. At the end of three years, the company will exit the business and will sell the fixed assets at an estimated value of $300,000. The project will require a $60,000 increase in current assets, and a $30,000 increase in accounts payable at t = 0. The InvestEd's marginal tax rate is 25%. The new line of business is expected to generate an additional $1,800,000 in annual sales. The operating costs excluding depreciation are expected to be $1,400,000 per year. The project's cost of capital is 12%. 2. Erie Eablizes The other project is an eablize production facility near Erie, PA. In deciding whether to proceed with the project, the InvestEd has gathered the following information: The estimated up-front cost of constructing the facility at t = 0 is $10 million. For tax and accounting purposes, these costs will be depreciated using a MACRS five (5) year schedule. The company will operate the facility for 4 years. It estimates today that the facility can be sold at the end of the project's life for $2.2 million. If the facility is opened, InvestEd will have to increase its inventory by $3.0 million at t = 0. In addition, its accounts payable will increase by $1.6 million at t = 0. The new facility will increase the company's yearly sales by $7.8 million. The annual operating costs (excluding depreciation) of the new facility are expected to equal 58% of annual revenues. The InvestEd's tax rate is 25%. The project's cost of capital is 10.5%. Five-Year MACRS depreciation schedule Year Depr 1 20.00% 2 32.00% 3 19.20% 4 11.52% 5 11.52% 6 5.76% + Sales 4- Costs - Depreciation EBIT Taxes Net Operating Profit After Taxes + Depreciation Operating Cash Flows - Capital Expenditures + After-Tax Salvage Value Changes in NOWC Project Cash Flows Discounted Cash Flows Payback Discounted Payback 1) NPV 2) IRR 3) MIRR 4) Payback (in years) 5) Discounted Payback (in years) MACRS Year 20.00% 32.00% 19.20% 11.52% 11.52% 5.76% 1 2 3 4 5 $ $ $ 30,000 $ 30,000 $ (19,200) $ 30,000 $ (11,520) $ $ 30,000 $ (6,912) $ (12,000) $ 18,000 $ 10,800 $ 18,480 $ 23,088 $ 30,000 (6,912) 23,088 (9,235) 13,853 6,912 (7,200) $ (7,392) $ (9,235) $ 10,800 $ 11,088 $ (4,320) $ 6,480 $ 19,200 $ 25,680 $ 13,853 $ 6,912 $ 12,000 $ 11,520 $ 22,800 $ 22,608 $ 20,765 $ 20,765 $ (60,000) $ 7,382 $ S 5,000 (1,000) $ (61,000) $ (2,000) $ 20,800 $ (1,000) $ 24,680 $ (1,000) 21,608 $ $ 20,765 $ 33,147 $ (61,000.00) $ 18,909.09 $ 20,396.69 $ 16,234.41 $ 14,182.64 $ 20,581.80 $ (61,000) $ (61,000) $ (40,200) $ (42,091) $ (15,520) $ (21,694) $ 6,088 (5,460) $ $ 8,723 0 Value $ 29,304.64 26.41% 18.9786% 2.7183 3.3850 $ $ $ $ $ $ $ $ Purchase Equipment Yes Yes Yes Tax Rate Discount Rate Salvage Value Sale Price Book Value Gain (loss) Taxes ATSV 40% 10% $ 10,000 $ 3,456 $ 6,544 $ 2,618 $ 7,382 IRR Maxwell FCFs Warren FCFs A in FCFs -61000 20800 24680 21608 20764.8 33147.2 -100000 39000 35000 -14200 42000 -17320 32000 -10392 10000 10764.8 50000 -16852.8 26.41% 20.82% 8.95% Maxwell Warren 0% $ 60,000.00 $ 69,000.00 1% $ 56,253.27 $ 64,067.87 2% $ 52,681.41 $ 59,362.12 3% $ 49,274.14 $ 54,869.45 4% $ 46,021.90 $ 50,577.49 5% $ 42,915.77 $ 46,474.71 6% $ 39,947.43 $ 42,550.38 7% $ 37,109.15 $ 38,794.50 8% $ 34,393.68 $ 35,197.73 9% $ 31,794.29 $ 31,751.34 10% $ 29,304.64 $ 28,447.20 11% $ 26,918.83 $ 25,277.67 12% $ 24,631.34 $ 22,235.63 13% $ 22,436.98 $ 19,314.40 14% $ 20,330.90 $ 16,507.72 15% $ 18,308.55 $ 13,809.71 16% $ 16,365.65 $ 11,214.86 17% $ 14,498.20 $ 8,718.01 18% $ 12,702.41 $ 6,314.30 19% $ 10,974.74 $ 20% $9,311.86 $ 3,999.15 1,768.26 (382.41) 21% $ 7,710.62 $ 22% $ 6,168.07 $ (2,456.68) 23% $ 4,681.42 $ (4,458.14) 24% $3,248.03 $ (6,390.18) 25% $ 1,865.43 $ (8,256.00) 26% $ 531.28 $ (10,058.62) 27% $ (756.65) $ (11,800.88) 28% $ (2,000.43) $ (13,485.49) 29% $ (3,202.05) $ (15,114.99) 30% $ (4,363.38) $ (16,691.80) $70,000.00 $60,000.00 $50,000.00 $40,000.00 $30,000.00 $20,000.00 $10,000.00 $- $(10,000.00) $(20,000.00) 0% 5% 10% NPV Profiles 15% Maxwell Warren 20% 25% 30% You work for a consulting firm and have been asked by a client--InvestEd--to evaluate two project proposals. Your task is to develop a spreadsheet system for the proposals to calculate the project cash flows (free cash flows), calculate the criteria for evaluating the proposed projects, and graph the NPV profile for each project so the company can see the range of rates in which each project is worth. Finally, based on the information in the proposals, you need to advise InvestEd on which proposal they should select and explain why they should select it. 1. California Carnmetarbors InvestEd is considering enter the carnmetarbor prefabrication business in California. In reviewing the proposal, the InvestEd's CFO is using the following facts: The carnmetarbor production will require the company to purchase additional fixed assets that will cost $1,000,000 at t = 0. For tax purposes the facility will be depreciated on a straight-line basis to zero over 3 years. At the end of three years, the company will exit the business and will sell the fixed assets at an estimated value of $300,000. The project will require a $60,000 increase in current assets, and a $30,000 increase in accounts payable at t = 0. The InvestEd's marginal tax rate is 25%. The new line of business is expected to generate an additional $1,800,000 in annual sales. The operating costs excluding depreciation are expected to be $1,400,000 per year. The project's cost of capital is 12%. 2. Erie Eablizes The other project is an eablize production facility near Erie, PA. In deciding whether to proceed with the project, the InvestEd has gathered the following information: The estimated up-front cost of constructing the facility at t = 0 is $10 million. For tax and accounting purposes, these costs will be depreciated using a MACRS five (5) year schedule. The company will operate the facility for 4 years. It estimates today that the facility can be sold at the end of the project's life for $2.2 million. If the facility is opened, InvestEd will have to increase its inventory by $3.0 million at t = 0. In addition, its accounts payable will increase by $1.6 million at t = 0. The new facility will increase the company's yearly sales by $7.8 million. The annual operating costs (excluding depreciation) of the new facility are expected to equal 58% of annual revenues. The InvestEd's tax rate is 25%. The project's cost of capital is 10.5%. Five-Year MACRS depreciation schedule Year Depr 1 20.00% 2 32.00% 3 19.20% 4 11.52% 5 11.52% 6 5.76% + Sales 4- Costs - Depreciation EBIT Taxes Net Operating Profit After Taxes + Depreciation Operating Cash Flows - Capital Expenditures + After-Tax Salvage Value Changes in NOWC Project Cash Flows Discounted Cash Flows Payback Discounted Payback 1) NPV 2) IRR 3) MIRR 4) Payback (in years) 5) Discounted Payback (in years) MACRS Year 20.00% 32.00% 19.20% 11.52% 11.52% 5.76% 1 2 3 4 5 $ $ $ 30,000 $ 30,000 $ (19,200) $ 30,000 $ (11,520) $ $ 30,000 $ (6,912) $ (12,000) $ 18,000 $ 10,800 $ 18,480 $ 23,088 $ 30,000 (6,912) 23,088 (9,235) 13,853 6,912 (7,200) $ (7,392) $ (9,235) $ 10,800 $ 11,088 $ (4,320) $ 6,480 $ 19,200 $ 25,680 $ 13,853 $ 6,912 $ 12,000 $ 11,520 $ 22,800 $ 22,608 $ 20,765 $ 20,765 $ (60,000) $ 7,382 $ S 5,000 (1,000) $ (61,000) $ (2,000) $ 20,800 $ (1,000) $ 24,680 $ (1,000) 21,608 $ $ 20,765 $ 33,147 $ (61,000.00) $ 18,909.09 $ 20,396.69 $ 16,234.41 $ 14,182.64 $ 20,581.80 $ (61,000) $ (61,000) $ (40,200) $ (42,091) $ (15,520) $ (21,694) $ 6,088 (5,460) $ $ 8,723 0 Value $ 29,304.64 26.41% 18.9786% 2.7183 3.3850 $ $ $ $ $ $ $ $ Purchase Equipment Yes Yes Yes Tax Rate Discount Rate Salvage Value Sale Price Book Value Gain (loss) Taxes ATSV 40% 10% $ 10,000 $ 3,456 $ 6,544 $ 2,618 $ 7,382 IRR Maxwell FCFs Warren FCFs A in FCFs -61000 20800 24680 21608 20764.8 33147.2 -100000 39000 35000 -14200 42000 -17320 32000 -10392 10000 10764.8 50000 -16852.8 26.41% 20.82% 8.95% Maxwell Warren 0% $ 60,000.00 $ 69,000.00 1% $ 56,253.27 $ 64,067.87 2% $ 52,681.41 $ 59,362.12 3% $ 49,274.14 $ 54,869.45 4% $ 46,021.90 $ 50,577.49 5% $ 42,915.77 $ 46,474.71 6% $ 39,947.43 $ 42,550.38 7% $ 37,109.15 $ 38,794.50 8% $ 34,393.68 $ 35,197.73 9% $ 31,794.29 $ 31,751.34 10% $ 29,304.64 $ 28,447.20 11% $ 26,918.83 $ 25,277.67 12% $ 24,631.34 $ 22,235.63 13% $ 22,436.98 $ 19,314.40 14% $ 20,330.90 $ 16,507.72 15% $ 18,308.55 $ 13,809.71 16% $ 16,365.65 $ 11,214.86 17% $ 14,498.20 $ 8,718.01 18% $ 12,702.41 $ 6,314.30 19% $ 10,974.74 $ 20% $9,311.86 $ 3,999.15 1,768.26 (382.41) 21% $ 7,710.62 $ 22% $ 6,168.07 $ (2,456.68) 23% $ 4,681.42 $ (4,458.14) 24% $3,248.03 $ (6,390.18) 25% $ 1,865.43 $ (8,256.00) 26% $ 531.28 $ (10,058.62) 27% $ (756.65) $ (11,800.88) 28% $ (2,000.43) $ (13,485.49) 29% $ (3,202.05) $ (15,114.99) 30% $ (4,363.38) $ (16,691.80) $70,000.00 $60,000.00 $50,000.00 $40,000.00 $30,000.00 $20,000.00 $10,000.00 $- $(10,000.00) $(20,000.00) 0% 5% 10% NPV Profiles 15% Maxwell Warren 20% 25% 30%
