Read Me
Financial Spreadsheets
Read Me Notes
Please read these brief notes before using the financial spreadsheets.
1. It would be prudent to make a copy of the Excel spreadsheet files contained on the CD-ROM.
2. The spreadsheets are divided into two parts. Part A, Financial Statement Analysis, contains the tools that will help you analyze your financial statements. Part A contains fourteen (15) templates. Part B, Decision-Making Tools, contains financial decision-making tools and has twelve (12) templates.
3. Both Part A and Part B include two separate Excel files. The Excel file named Examples, gives examples of the different templates contained in these spreadsheets. These examples are presented in hard copy at the end of the Instructor’s Manual. They give an overview of what the spreadsheets look like, how financial information is presented, and how financial statements can be analyzed. The Excel file named Templates contains spreadsheets that you will use to do your own financial statement analysis and financial decisions.
Objective of these financial spreadsheets
The objective of these financial spreadsheets is to help you perform most financial calculations contained in the sixth edition of the book, Finance for Non-Financial Managers. You will not have to go through the chore of doing endless calculations to analyze your financial statements and to make decisions. These spreadsheets will calculate the most simple financial ratios such as the return on revenue to the most complicated ones such as the internal rate of return and the financial health score.
These financial spreadsheets will therefore allow you to concentrate your time on analyzing your financial statements and the financial objectives that you want to achieve and less time on “crunching” numbers. However, if you want to have a good understanding of what the spreadsheets can do for you, you should have a “crisp” understanding of the financial concepts and techniques that are contained in the book, Finance for Non-Financial Managers. It might even be wise to perform some of these calculations manually in order to have a good grasp of what these spreadsheets will do for you.
Let’s now examine the contents of these financial spreadsheets and briefly look at how they can be used.
Part A: Financial Statement Analysis Spreadsheets
This Excel file contains fifteen templates. These spreadsheets can be organized into two sub-groups, input templates and output templates.
Input Templates
The following three templates will be used to input your financial statement information. Therefore, most of the cells contained in these templates are unprotected.
Template 1 Statement of Income
Template 2 Statement of Comprehensive Income and
Statement of Changes in Equity
Template 3 Statement of Financial Position
Output Templates
The following twelve templates provide the information that will help you analyze your financial statements. Most cells contained in these templates are protected. There is no need for you to input (with the exception of a few cells) numbers. Basically, these templates are your analytical and decision-making documents that have calculated the numbers that you have inputted on templates 1 to 3. These output templates will help you analyze your financial statements and improve the effectiveness of your decisions.
Template 4 Statement of Sources and Uses of Funds
Template 5 Statement of Cash Flows
Template 6 Vertical Analysis of the Statement of Income
Template 7 Horizontal Analysis of the Statement of Income
Template 8 Vertical Analysis of the Statement of Financial Position
Template 9 Horizontal Analysis of the Statement of Financial Position
Template 10 Financial Ratios
Template 11 Du Pont System
Template 12 Cash Flow, EBIT and EBITDA
Template 13 Economic Value Added
Template 14 Sustainable Growth Rate
Template 15 Financial Health Zone
How to input your information
The only cells that you will use to input your data are shaded and bordered; they are not protected. All other cells are protected. Therefore, you cannot type any data in the cells that are not shaded and bordered. If you unprotect your template and decide to make changes to some of the cells like renaming salaries instead of purchases on the Statement of Income, or renaming inventories by trade receivables on your Statement of Financial Position, your analytical templates (output templates) will show distorted and irrelevant numbers. These changes will throw off your financial ratios and other critical data, which in turn will make your analytical or output templates useless.
The following three input templates are used to calculate your financial information contained on templates 4 to 15.
Template 1: On the Statement of Income input template, most lines are protected. Therefore, you cannot change the names of the lines with the exception of the ones called “other charges” (shaded). However, most cells under each column (years 2008, 2009 and 2010) are unprotected. You can input your numbers in these cells. The only protected cells are the sub-total and total cells. Since the years are unprotected, you can change them. If you do, the years appearing on the following templates will be changed accordingly.
Template 2: All lines on the Statement of Comprehensive Income and the Statement of Changes in Equity are protected. However, most cells under the years are unprotected with the exception of most balance at beginning of year and end of year cells.
Template 3: Most lines on the Statement of Financial Position input template are also protected. Therefore, you cannot change the names of the lines with the exception of four: other non-current assets, other current asset 1, other current asset 2 and other current liability. Therefore, all other account descriptions should not be changed. Like in the Statement of Income, most cells under each column (years) are unprotected with the exception of the sub-total and total cells. The share capital, retained earnings and other comprehensive income/(loss) numbers are drawn from the Statement of Comprehensive Income and Statement of Changes in Equity (template 2).
Here are a few words about the output templates.
Template 4: All cells on the Statement of Sources and Uses of Funds template are protected with the exception of the Adjustments shaded and bordered cell which appears on the lower part of the template. All numbers on this template are drawn from three input templates: (1) the Statement of Income, the (2) the Statement of Changes in Equity and (3) the Statement of Financial Position. If you have correctly inputted your financial statement numbers on these financial statements, the total numbers under the columns change for the years 2009 versus 2008 and 2010 versus 2009 should read “0”. This means that all sources and all uses of funds incurred by the business between two accounting periods should be equal, and therefore the amount should read 0. If the total shows a number other than 0, in most instances, it means that the depreciation/amortization figure shown on your Statement of Income is different from the accumulated depreciation/amortization change between the years 2008 and 2009, 2009 and 2010 shown on your Statement of Financial Position. For example, the Statement of Sources and Uses of Funds shows a $375,000 amount (as a source) for accumulated depreciation/amortization for the year 20010. Your Statement of Income also shows $375,000 ($206,000 + $70,000 + $99,000). Your Statement of Financial Position also shows $375,000 for accumulated depreciation/amortization ($1,260,000 - $885,000). If your Statement of Sources and Uses of Funds is off by just several thousand dollars (say the 0 Total line shows $5,000), you might want to input $5,000 in the adjustment cell in order to make it balance to 0. If not, you have the option of making a change to either the depreciation/amortization on your Statement of Income or your accumulated amortization shown on your Statement of Financial Position.
Template 5: All cells on the Statement of Cash Flows are protected. The numbers contained in this template are drawn from the Statement of Sources and Uses of Funds (template 4).
Template 6: All cells on the Vertical Analysis of the Statement of Income are protected. The numbers on this template are drawn from template 1.
Template 7: All cells on the Horizontal Analysis of the Statement of Income are protected. The numbers on this template are drawn from template 1.
Template 8: All cells on the Vertical Analysis of the Statement of Financial Position are protected. The numbers on this template are drawn from template 3.
Template 9: All cells on the Horizontal Analysis of the Statement of Financial Position are protected. The numbers on this template are drawn from template 3.
Template 10: All cells on the Financial Ratios template are protected. The numbers contained on the Statement of Income (template 1) and the Statement of Financial Position (template 3) are used to calculate these financial ratios.
Template 11: All cells on the Du Pont System template are protected. The numbers contained on the Statement of Income (template 1) and the Statement of Financial Position (template 3) are used to calculate the financial ratios.
Template 12: All cells on the Cash Flow, the EBIT (earnings before interest and taxes) and the EBITDA (earnings before interest, taxes, depreciation and amortization) template are protected. The numbers contained on the cash flow template are drawn from the Statement of Income (template 1).
Template 13: All cells on the Economic Value Added (EVA) template are protected with the exception of three lines: miscellaneous adjustments, income tax rate and total weighted cost of capital.
Template 14: All cells on the Sustainable Growth Rate template are protected. The information used to calculate these various ratios is drawn from the Statement of Income (template 1) and the Statement of Financial Position (template 3).
Template 15: All cells on the Financial Health Zone document are protected. The information used to calculate these various ratios is drawn from the Statement of Income (template 1) and the Statement of Financial Position (template 3).
Part B: Decision-Making Tools Spreadsheets
The Decision-Making Tools Excel file contains the following 12 templates:
Template 1 Break-Even Analysis Using the Contribution Margin
Template 2 Break-Even Analysis Using the PV ratio
Template 3 Operating Leverage
Template 4 Cost of Capital for Privately Owned Company
Template 5 Cost of Capital for Publicly Owned Company
Template 6 Capital Project Analysis
Template 7 Monthly Cash Budget
Template 8 Lease/Buy Decision
Template 9 Sales and Manufacturing Budgets
Template 10 Cash Discounts
Template 11 Credit Terms
Template 12 Economic Ordering Quantity
Unlike the templates contained in Part A, Financial Statements Analysis, the templates contained in Part B should be used as both working and analytical documents. Many cells on these templates are unprotected allowing you to do two things. First, you input your financial numbers in the shaded and bordered cells and second, you read the financial results in the protected cells (those that are not shaded and bordered).
Template 1: The objective of the Break-Even Analysis Using the Contribution margin template is to calculate the break-even in units and in revenue of a proposed project by using the “contribution margin”. Here, you need to input your data in the shaded and bordered cells (i.e., volume, price per unit, unit variable costs, cash fixed costs and depreciation/amortization) to calculate the regular and the cash break-even points. By inputting your profit objective (using three options), you will be able to determine how many units you need to sell in order to break even. This template also summarizes the revenue required to realize your regular break-even point, cash-break-even point and the three profit break-even points.
Template 2: The objective of the Break-Even Using the PV Ratio template is to calculate the break-even in revenue by using the “PV ratio”. You need this tool if sales units are “not known or available”. The information shown on this template is usually drawn from your Statement of Income. From your Statement of Income, you will have to differentiate all costs that you consider variable and all other costs that you consider fixed. Once done, you will have to input the variables costs and fixed costs in the appropriate shaded and bordered cells in order to determine the regular break-even point, the cash break-even point and the break-even point to achieve three different profit objectives.
Template 3: The objective of the Operating Leverage template is to calculate your profit (or loss) position under different sales volume assumptions (high, expected and low). By inputting your fixed costs, selling price and variable costs under each operating method (present and proposed), you will be able to determine the level of profit (or loss) each alternative will realize.
Template 4: The objective of the Cost of Capital Calculation for a privately owned company template calculates the cost of capital for raising funds in order to invest in capital projects. You need to input the amount of funds needed to be raised from the different sources and the before-tax cost. The income tax rate must also be inputted.
Template 5: The objective of the Cost of Capital Calculation for a publicly owned company template is to calculate the cost of capital to raise funds in order to invest in capital projects. You need to input the amount of funds to be raised from the different sources and the before-tax cost. The income tax rate must also be inputted in addition to the dividends to be paid, the market value of the shares, the flotation costs and growth factor.
Template 6: The objective of the Capital Project Analysis template is to calculate the economic return of capital projects by using time value of money yardsticks such as the internal rate of return, the net present value and the profitability index. You have to input your information in the shaded and bordered cells under the following seven categories:
• Cost of your capital assets. Make sure that the numbers appearing in these cells have a negative sign in front (-) reflecting an outflow of cash.
• The appropriate CCA rate for each capital asset category.
• The investments in working capital accounts for each of the three years. Make sure that the numbers appearing in these cells have a negative sign in front (-) also reflecting an outflow of cash.
• The appropriate income tax rate.
• The revenue, cost of sales and distribution costs and administrative expenses expected during the life of the project.