accounting excel homework

Excel #4

Capital Budgeting

Spring 2021

You are the CFO of a business and have the opportunity to evaluate two different investment opportunities. Information related to these investments follows:

	Investment 1	Investment 2
Investment Cost	$ 800,000	$ 500,000
Salvage Value	$ 40,000	$ 50,000
Useful Life	8 years	15 years
Required Rate of Return	10%	10%
Sales	$ 450,000	$ 400,000
Variable Costs	$ 150,000	$ 175,000
Fixed Costs (excluding depreciation)	$ 100,000	$ 150,000
Tax Rate	35%	35%

Your company has a required rate of return of 10% for all new investments and is subject to a tax rate of 35%.

1) Determine the annual after tax cash flow for each investment. In addition, determine the after-tax cash flow for the salvage value if you assume that the actual market value of Investment #1 at the end of eight years is $50,000 and the actual market value of Investment #2 at the end of fifteen years is $40,000.

2) Prepare a schedule of expected cash flows for each investment. Note that all positive cash flows must be shown as positive numbers and all negative cash flows must be shown as negative numbers.

3) Calculate the Net Present Value for each investment using the NPV function in Excel.

4) Calculate the Internal Rate of Return for each investment using the IRR function in Excel. (Note your guess for the interest rate in the formula should be the required rate of return)

5) Recalculate the Net Present Value for INVESTMENT #1 ONLY but assume that the required/target rate of return is 6%, 12%, and 20%.

Rules regarding the completion of the Excel Assignments:

1. You MUST use  Microsoft Excel  to complete your assignments. Do  NOT  use Mac’s Numbers or Google Sheets to complete the assignment. Your instructor will use a grading software and ONLY files in the format with the file extension of .xls or .xlsx can be graded. All else will be ignored!

2. You MUST use the Excel Templates provided on  www.acct1020.com  for this semester.

3. You MUST download the Excel Templates using YOUR account from www.acct1020.com .  Each template is individually coded to prevent academic dishonesty.  Any student found using the template from another student’s account will receive an automatic zero on the assignment and be subject to further penalties for academic dishonesty.  In addition, any student allowing another student to use his/her account from  www.acct1020.com  will also receive a zero on the assignment.

4. You MUST sign the academic honesty pledge or you will lose points per your instructor’s discretion.

5. You MUST use formulas and links whenever possible.  If you simply type in your solutions rather than use formulas (in ANY part of the assignment), you will lose points per your instructor’s discretion.

6. You MUST submit your Excel files per your instructor’s request. Failure to submit your Excel file correctly according to your instructor’s request will result in points lost per your instructor’s discretion. Please note that each instructor may have his/her own required method of turning in the assignment!

7. To receive credit, your instructor must RECEIVE your assignment by the due date (no later than 11:59 pm without exception).