Chapter_10 - Ross, Westerfield, and Jordan's Spreadsheet...

Info iconThis preview shows pages 1–7. Sign up to view the full content.

View Full Document Right Arrow Icon
by Brad Jordan and Joe Smolira Version 9.0 Chapter 10 In these spreadsheets, you will learn how to use The following conventions are used in these spr 1) Given data in blue 2) Calculations in red NOTE: Some functions used in these spreadsheets may the "Analysis ToolPak" or "Solver Add-In" be installed in To install these, click on the Office button then "Excel Options," "Add-Ins" and select "Go." Check "Analysis ToolPak" and "Solver Add-In," then click "OK." Ross, Westerfield, and Jordan's Spreadsheet Master Fundamentals of Corporate Finance, 9th edition Naming cells SLN VDB Solver
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
e the following Excel functions: readsheets: y require that in Excel.
Background image of page 2
Chapter 10 - Section 3 Pro Forma Financial Statements and Cash Flows Cans sold per year: 50,000 Price per can: $4.00 Variable cost per can: $2.50 Required return: 20% Fixed costs per year: $12,000 Manufacturing equipment: $90,000 Project life (years): 3 Initial net working capital: $20,000 Tax rate: 34% RWJ Excel Tip With these numbers, we can prepare the pro forma income statement, which will be: Sales $200,000 Variable costs 125,000 Fixed costs 12,000 Depreciation 30,000 EBIT $33,000 Taxes (34%) 11,220 Net income $21,780 RWJ Excel Tip Because capital budgeting requires numerous repetitive cash flows, it is an ideal applicatio should do few or no calculations on your own, but rather let Excel do the calculations for yo for the project: In a problem with a number of different variables, it can be advantageous to name the cells the Formula bar in the name bar and you will see the name "Units." We entered the name from this cell later, we can type in the name of the variable instead of referencing the cell. we used in this cell is Units * Price_per_unit. When naming cells, you should keep the nam variable name, so we used an underscore instead of the space in Price_per_unit. To calculate the depreciation each year for straight-line depreciation, we can divide the in as we have done here. The SLN we used in this case looks like this:
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
To calculate the AAR, we need the average investment in assets each year. The total inves Year 0 1 2 Net working capital $20,000 $20,000 $20,000 Net fixed assets 90,000 60,000 30,000 Total investment $110,000 $80,000 $50,000 So, the average assets are: Average assets: $65,000 Now, we can calculate the operating cash flow each year, which will be: EBIT $33,000 + Depreciation 30,000 - Taxes 11,220 Operating cash flow $51,780 So, the total cash flow for each year of the project will be: Year 0 1 2 Operating cash flow $51,780 $51,780 Changes in NWC $(20,000) The inputs are Cost, which is the initial cost, Salvage, which is the salvage value, and Life, cost by the life of the equipment in the cell rather than use this particular function, but it is a
Background image of page 4
Capital spending (90,000) Total project cash flow $(110,000) $51,780 $51,780 Given these cash flows, we can now calculate the NPV, IRR, and AAR of the project, which NPV $10,647.69 IRR 25.76% AAR 33.51%
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
on for Excel. When doing a capital budgeting problem, as in most Excel uses, you
Background image of page 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 33

Chapter_10 - Ross, Westerfield, and Jordan's Spreadsheet...

This preview shows document pages 1 - 7. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online