Create an Excel spreadsheet in which you use capital budgeting tools to determine the quality of...

50.1K

Verified Solution

Question

Accounting

Create an Excel spreadsheet in which you use capital budgetingtools to determine the quality of 3 proposed investment projects,as well as a 6-8 page report that analyzes your computations andrecommends the project that will bring the most value to thecompany.
Introduction
This portfolio work project is about one of the basic functionsof the finance manager: allocating capital to areas that willincrease shareholder value. There are many uses of cash managerscan select from, but it is essential that the selected projects areones that add the most value to the company. This means forecastingthe projected cash flows of the projects and employing capitalbudgeting metrics to determine which project, given the forecastcash flows, gives the firm the best chance to maximize shareholdervalue.
As a business professional, you are expected to:
Use capital budgeting tools to compute future project cash flowsand compare them to upfront costs.
Evaluate capital projects and make appropriate decisionrecommendations.
Prepare reports and present the evaluation in a way that financeand non-finance stakeholders can understand.
Scenario
You work as a finance manager for Drill Tech, Inc., a mid-sizedmanufacturing company located in Minnesota. Three capital projectrequests were identified as potential projects for the company topursue in the upcoming fiscal year. In the meeting to discusscapital projects, the director of finance (and your boss), JenniferDavidson, gives you a synopsis of the projects along with thisquestion: Which one of these projects will provide the mostshareholder value to the company?
She also tells you that other than what is noted in each projectscenario, all other costs will remain constant, and you shouldremember to only evaluate the incremental changes to cashflows.
The proposed projects for you to review are as follows.
Project A: Major Equipment Purchase
A new major equipment purchase, which will cost $10 million;however, it is projected to reduce cost of sales by 5% per year for8 years.
The equipment is projected to be sold for salvage valueestimated to be $500,000 at the end of year 8.
Being a relatively safe investment, the required rate of returnof the project is 8%.
The equipment will be depreciated at a MACRS 7-yearschedule.
Annual sales for year 1 are projected at $20 million and shouldstay the same per year for 8 years.
Before this project, cost of sales has been 60%.
The marginal corporate tax rate is presumed to be 25%.
Project B: Expansion into Europe
Expansion into Western Europe has a forecast to increasesales/revenues and cost of sales by 10% per year for 5 years.
Annual sales for the previous year were $20 million.
Start-up costs are projected to be $7 million and an upfrontneeded investment in net working capital of $1 million. The workingcapital amount will be recouped at the end of year 5.
Because of the higher European tax rate, the marginal corporatetax rate is presumed to be 30%.
Being a risky investment, the required rate of return of theproject is 12%.
Project C: Marketing/Advertising Campaign
A major new marketing/advertising campaign, which will cost $2million per year and last 6 years.
It is forecast that the campaign will increase sales/revenuesand costs of sales by 15% per year.
Annual sales for the previous year were $20 million.
The marginal corporate tax rate is presumed to be 25%.
Being a moderate risk investment, the required rate of return ofthe project is 10%.

Answer & Explanation Solved by verified expert

4.1 Ratings (585 Votes)

PROJECT A A Initial Cost 10000000 Present Value PV of Cash Flow Cash Flow1iN iDiscount RateRequired return8008 NYear of Cash Flow CALCULATION OF CASH FLOWS AND PV OF CASH FLOWS N Year 1 2 3 4 5 6 7 8 B Annual Sales 20000000 20000000 20000000 20000000 20000000 20000000 20000000 20000000 C06B 12000000 12000000 12000000 12000000 12000000 12000000 12000000 12000000 D005C Reduction in cost of sales 600000 600000 600000 600000 600000 600000 600000 600000 E1025D After tax Savings 450000 450000 450000 450000 450000 450000 450000 450000 Depreciation Tax Shield F MACRS 7 year depreciation rate 1429 2449 1749 1249 893 892 893 446 GF10000000 Annual depreciation 1429000 2449000 See Answer

Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:

Unlimited Question Access with detailed Answers
Zin AI - 3 Million Words
10 Dall-E 3 Images
20 Plot Generations
Conversation with Dialogue Memory
No Ads, Ever!
Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!

Become a Member

Transcribed Image Text

Create an Excel spreadsheet in which you use capital budgetingtools to determine the quality of 3 proposed investment projects,as well as a 6-8 page report that analyzes your computations andrecommends the project that will bring the most value to thecompany.IntroductionThis portfolio work project is about one of the basic functionsof the finance manager: allocating capital to areas that willincrease shareholder value. There are many uses of cash managerscan select from, but it is essential that the selected projects areones that add the most value to the company. This means forecastingthe projected cash flows of the projects and employing capitalbudgeting metrics to determine which project, given the forecastcash flows, gives the firm the best chance to maximize shareholdervalue.As a business professional, you are expected to:Use capital budgeting tools to compute future project cash flowsand compare them to upfront costs.Evaluate capital projects and make appropriate decisionrecommendations.Prepare reports and present the evaluation in a way that financeand non-finance stakeholders can understand.ScenarioYou work as a finance manager for Drill Tech, Inc., a mid-sizedmanufacturing company located in Minnesota. Three capital projectrequests were identified as potential projects for the company topursue in the upcoming fiscal year. In the meeting to discusscapital projects, the director of finance (and your boss), JenniferDavidson, gives you a synopsis of the projects along with thisquestion: Which one of these projects will provide the mostshareholder value to the company?She also tells you that other than what is noted in each projectscenario, all other costs will remain constant, and you shouldremember to only evaluate the incremental changes to cashflows.The proposed projects for you to review are as follows.Project A: Major Equipment PurchaseA new major equipment purchase, which will cost $10 million;however, it is projected to reduce cost of sales by 5% per year for8 years.The equipment is projected to be sold for salvage valueestimated to be $500,000 at the end of year 8.Being a relatively safe investment, the required rate of returnof the project is 8%.The equipment will be depreciated at a MACRS 7-yearschedule.Annual sales for year 1 are projected at $20 million and shouldstay the same per year for 8 years.Before this project, cost of sales has been 60%.The marginal corporate tax rate is presumed to be 25%.Project B: Expansion into EuropeExpansion into Western Europe has a forecast to increasesales/revenues and cost of sales by 10% per year for 5 years.Annual sales for the previous year were $20 million.Start-up costs are projected to be $7 million and an upfrontneeded investment in net working capital of $1 million. The workingcapital amount will be recouped at the end of year 5.Because of the higher European tax rate, the marginal corporatetax rate is presumed to be 30%.Being a risky investment, the required rate of return of theproject is 12%.Project C: Marketing/Advertising CampaignA major new marketing/advertising campaign, which will cost $2million per year and last 6 years.It is forecast that the campaign will increase sales/revenuesand costs of sales by 15% per year.Annual sales for the previous year were $20 million.The marginal corporate tax rate is presumed to be 25%.Being a moderate risk investment, the required rate of return ofthe project is 10%.