4. Use the fill function to complete the Revenue row from Year 2 to Year 10 with growth of 12% per year. 5. Using absolute cell references to help you, calculate the cost of goods sold for all years. 6. Use the fill function with a linear trend to complete the Operating Cost row. 7. Use the appropriate function for Declining Balance depreciation to complete Row 21. 8. Calculate the Income Tax Expense for each year of operation. Remember that the Company will pay no tax if Earnings before Tax are negative. 9. Complete the Net Income row. 10. In Row 26 enter the Purchase Price and Salvage Value in the appropriate cells. 11. In row 28 determine the annual free cash flow. See opening paragraphs for hint and include Purchase Price and Salvage Value. 12. In the cell B30 determine the net present value (NPV) and in cell B32 calculate the internal rate of return (IRR) for the project using the Discount Rate in the box at the top of the page. 13. Answer the question "Would you invest and why?" in cell F31 on the worksheet. 14. Format the worksheet so that it fits on 1 page with Landscape orientation. 15. Put your name in the left footer. 16. Save your worksheet.
Added by Guillermo C.
Close
Step 1
To fill the Revenue row from Year 2 to Year 10 with a growth of 12% per year, you can use the fill function. First, calculate the revenue for Year 2 by multiplying the revenue of Year 1 by 1.12. Then, select the cell with the Year 2 revenue, move your cursor to Show moreā¦
Show all steps
Your feedback will help us improve your experience
Prabhakar Kumar and 53 other Principles of Accounting educators are ready to help you.
Ask a new question
Labs
Want to see this concept in action?
Explore this concept interactively to see how it behaves as you change inputs.
Recommended Videos
5. (Depreciation) You are considering the following investment: a. Assuming that the investment can be depreciated using 7-year straight-line depreciation with no salvage value, calculate the project NPV. b. What will be the company's gain in present value if it uses a 7-year modified accelerated depreciation (MACRS) schedule, given below:
Akash M.
Cardinal Company is considering a five-year project that would require a $3,200,000 investment with a useful life of five years and no salvage value. The company's discount rate is 12%. The project would provide net operating income in each of the five years as follows: Sales: $2,735,000 Variable Expenses: $1,400,000 Contribution Margin: $1,335,000 Fixed Expenses: Advertising, salaries, and other fixed out-of-pocket costs: $235,000 Depreciation: $800,000 Total Fixed Expenses: $1,035,000 Net Operating Income: $300,000 Required: 1. What is the present value of the project's annual net cash inflows? 2. What is the project's net present value? 3. What is the project profitability index for this project? (Round your answer to four decimal places.) Describe how a Manager would utilize the Profitability Index? 4. What is the project's internal rate of return to the nearest whole percent? 5. What is the project's payback period in years (round to three decimal places)? 6. What is the project's simple rate of return for each of the five years (round your answer to three decimal places)? 7. If the company's discount rate was 15% instead of 12%, calculate the answers to questions 2 through 5 (NPV, PI, IRR and Payback). Is each it higher, lower or same (please comment on each and why)? 8. Using the original data, if the equipment had a salvage value of $300,000 at the end of five years recalculate #2 (NPV), #3 (Profitability Index) and #6 (Simple Rate of Return).
Adi S.
Given the following information, please calculate after-tax cash flow for year 1 Assuming a sales price of $1,100,000, please calculate the after-tax cash flow from the sale (donāt forget the depreciation recapture.) Finally, calculate the after-tax IRR for the investment. Purchase Price: $900,000 Loan: $750,000, 5%, 25 years (annual payments) Year 1 NOI: $100,000 Year 2 ATCF: $33,000 Year 3 ATCF: $34,000 Use an 85/15 ratio for depreciation. 39 year, straight line. 35% tax rate on income, 15% on long-term capital gains, 25% depreciation recapture. 1. What is the annual loan payment? (Round to the nearest cent, input your answer as a positive number.) 53,214.34 2.What is the annual depreciation expense? (Round to the nearest cent, input your answer as a positive number.) 19,615.38 3. What is the after-tax cash flow (ATCF) for year 1? (Round to the nearest cent, input your answer as a positive number.) 31,776.04 4. What is the after-tax cash flow from the sale at the end of year 3? 354,828 5. What is the IRR of the investment? (12% is ā12ā not ā.12,ā and round to 2 decimal places.)
Madhur L.
Recommended Textbooks
Horngrenās Cost Accounting
Cost Accounting A Managerial Emphasis
Principles of Accounting Volume 1: Financial Accounting
Watch the video solution with this free unlock.
EMAIL
PASSWORD