Instructions
FinSAS Version 20111028
FinSAS: Financial Statement Analysis Spreadsheet
by
Dr. Donald V. Saftner
[email protected]
To Accompany
Financial Reporting and Analysis:
Using Financial Accounting Information
13/e
by Dr. Charles Gibson
Copyright © 2013 Cengage Learning
All rights reserved
FinSAS: Financial Statement Analysis Spreadsheet allows users to input the reported data from the financial statements, notes, and other sources. FinSAS then computes the ratios found in Financial Reporting and Analysis: Using Financial Accounting Information 13/e. Using this tool, you can spend more time analyzing the statements and less time crunching the numbers. In addition to the ratios, both a vertical and horizontal analysis are performed.
Instructions:
Note the sheet tabs below. The following instructions use the names of those tabs as section headings. Click on a tab to see that sheet after reading the instructions below.
Input
On the Input sheet, enter values into all appropriate cells (i.e., those with blue font). The figures in black font are calculated based on your input. By entering the Company Name, Analyst Name(s), and Most Recent Year Available in the first three input cells, those items will appear on all subsequent output sheets. They make it easy to identify any printouts you make.
The next two input cells (Years Available for Income Statement and Balance Sheet) are particularly important to the functioning of the model. Changing these numbers (while keeping them within the range one through five) changes the number of columns of data that are available on the Input sheet and all subsequent output sheets. In a single annual report/10K, there are often three years of income statements and two years of balance sheets. The annual report/10K you are using may have more and/or you may want to use multiple years’ worth of annual report/10Ks to increase the amount of data available to analyze. The maximum years that FinSAS can accept is five years.
Fill in all remaining input cells for which you can find data. There are many different names for the same account. You must translate the terminology used by the company to the generic terms used in FinSAS. When the detail is not needed, FinSAS requests a summary figure. For instance, on the published income statement there may be several operating expenses (e.g., wages, utilities, rent), but FinSAS only needs the sum of those, which would be entered as Operating Expenses. Be sure to read the footnotes to the financial statements and the comparative data section. Those sources include some of the information you need to complete the Input sheet. Also be sure to match key subtotals (e.g. the FinSAS total current asset should be same as the figure on the balance sheet even though the detail lines may be different.)
Be consistent in ignoring a certain number of decimal places. Many financial statements have "in thousands, except par values," "millions except share and per share amounts," or similar statements. When entering data into FinSAS, it is usually best to follow the same convention as the financial statements in choosing the number of decimal places to assume have been removed. An exception is the second statement above. Only "per share" data should be entered as dollars with no decimal places removed. Account balances and total share information should consistently have the same number of decimal places removed. If the share and account balances have a different number of decimal places removed then ratios such as "book value per share" and "operating cash flow per share" will be inaccurate.
Source of Input
This is the place to document where you found the information you entered on the previous sheet. This can be very useful for you and for anyone who reviews your work. For instance if most of it came from the company's annual report/10K, then state that at the top. Individual lines of input in FinSAS might be the sum of a few lines in the published financial statements. Indicate on this sheet how you determined the FinSAS inputs from the statements. For some of the inputs, you may have used information from the footnotes or comparative information section. Indicate that information on this sheet and include page numbers wherever possible. Of course, other sources of information should be cited.
Vertical Analysis
After you insert your data, you can see the result of the vertical analysis. It compares each amount on the income statement and balance sheet with a base amount (net sales on the income statement and total assets on the balance sheet) selected from the same year.
Horizontal Analysis
After you insert your data, you can see the result of the horizontal analysis. It compares each amount on the income statement and balance sheet with the same amount for the oldest year available.
Ratios – avg.
After you insert your data, you can see the result of the ratio analysis. For the ‘Ratio – Average’, the average of beginning of year values and end of year values is used when appropriate. Note that if you only have two years of balance sheet information, then for those ratios that use the average of two years of balance sheet information, only one year's worth of ratios can be calculated. Since it is desirable to compare the ratios across time to determine possible trends, a solution to only having one year's worth of ratios should be sought. Some possible solutions are to find additional years' worth of data and/or use the Ratio - end. sheet.
Ratios with a negative cash flow or negative profit measure in the numerator will display #N/A, which means not available or not applicable. This makes it less likely that invalid conclusions will be made during your analysis.
You may want to determine how a certain ratio is calculated. You may do this in one of two ways. If you select the cell in which the ratio appears, then the formula for that cell appears near the top of the screen. You will then have to determine what accounts the cell refers to by looking at the Input screen. Alternatively, you can refer to the Documentation screen for the definition.
Ratios – end.
For the ‘Ratio – End’, the end of year values are used instead of the average values. The ‘*’ marks indicate the ratios for which the end of year values are used whereas the averages are used on the previous sheet.
For the Ratios - end. sheet, the end of year values are used instead of the average values. The ‘*’ marks indicate the ratios for which the end of year values are used whereas the averages are used on the previous sheet. Note that the Ratios - avg. sheet is theoretically superior in that averages for balance sheet information (point in time) is compared to income statement or cash flow statements (period of time).
Documentation
The Documentation sheet provides you the definitions of the ratios using the row labels of the Input sheet or other ratio results.
Copyright © 2013 Cengage Learning
mailto:
[email protected]Input
1 2 3 4 5
FinSAS Version 20111028 Input
Company: Macys
Analyst: Your name(s) here
Most Recent Year Available: 2017
Years Available for:
Income Statement (1-5) 5
Balance Sheet (1-5) 5
= = = = = =
INCOME STATEMENT 2017 2016 2015 2014 2013
- - - - - -
Net Sales 24837 25778 27079 28105 27931
Less: Cost of Goods Sold 15152 15621 16496 16863 16725
- - - - -
Gross Profit 9685 10157 10583 11242 11206
Other Operating Revenue 544 209 212 92 74
Less: Operating Expenses 8422 9051 8756 8534 8602
- - - - -
Operating Income 1807 1315 2039 2800 2678
Less: Interest Expense 321 367 363 395 390
(no capitalized interest)
Other Income (Expenses) 21 4 2 -15 2
Unusual or Infreq. Item;
Gain (Loss) 0 0 0 0 0
Equity in Earnings of Assoc.;
Profit (Loss) 0 0 0 0 0
- - - - -
Income before Taxes 1507 952 1678 2390 2290
Less:Taxes Related to Operations -29 341 608 864 804
- - - - -
N.I. before Noncontr. Inc 1536 611 1070 1526 1486
Noncontrolling income (loss) 11 8 2 0 0
- - - - -
N.I. before Nonrecurring Items 1547 619 1072 1526 1486
Oper. of Discontinued Segment;
Income (Loss) 0 0 0 0 0
Disposal of Discont. Segment;
Gain (Loss) 0 0 0 0 0
Extraordinary Item;
Gain (Loss) 0 0 0 0 0
Cum. Effect of Acct Change;
Gain (Loss) 0 0 0 0 0
- - - - -
Net Income (Loss) 1547 619 1072 1526 1486
= = = = = =
BALANCE SHEET 2017 2016 2015 2014 2013
- - - - - -
ASSETS
Current Assets:
Cash 1297 1109 2246 2273 1836
Marketable Securities 0 0 0 0 0
Gross Receivables 522 558 424 438 371
Less: Allowance for Bad Debts 0 0 0 0 0
Net Trade Receivables 522 558 424 438 371
Inventories 5399 5506 5516 5557 5308
Prepaid Expenses 408 479 493 420 361
Other Current Assets 0 0 0 0 0
- - - - -
Total Current Assets 7626 7652 8679 8688 7876
Long-Term Assets:
Net Tangible (Fixed) Assets (other than construction in progress) 7017 7616 7800 7930 8196
Construction in Progress 0 0 0 0 0
Intangible Assets 4395 4411 4239 4270 4304
Investments 0 0 0 0 0
Other Nonoperating Assets 813 897 743 732 615
Other Operating Assets 0 0 0 0 0
- - - - -
Total Long-Term Assets 12225 12924 12782 12932 13115
Total Assets 19851 20576 21461 21620 20991
LIABILITIES AND EQUITY
Current Liabilities:
Accounts Payable 4986 4859 4802 4501 4189
Short Term Loans 309 642 76 463 124
Current Maturity of L.t. Debt 0 0 0 0 0
Other Current Liabilities 352 227 658 762 762
- - - - -
Total Current Liabilities 5647 5728 5536 5726 5075
Long-Term Liabilities:
Long-term Debt 6562 6995 7265 6714 6806
Reserves 0 0 0 0 0
Deferred Liabilities 1443 1477 1081 1273 1238
Noncontrolling Interest -1 3 0 0 0
Redeemable Preferred 0 0 0 0 0
Other Long-term Liabilities 1877 2123 2201 1658 1821
- - - - -
Total Long-term Liabilities 9881 10598 10547 9645 9865
Total Liabilities 15528 16326 16083 15371 14940
Shareholders' Equity:
Preferred Equity 0 0 0 0 0
Common Equity-incl. Ret. Ern. 4323 4250 5378 6249 6051
- - - - -
Total Equity 4323 4250 5378 6249 6051
Total Liabilities and Equity 19851 20576 21461 21620 20991
= = = = = =
OTHER DATA 2017 2016 2015 2014 2013
- - - - - -
Capitalized Interest 2.0 2.0 2.0 2.0 2.0
Interest Portion of Rentals 0.0 0.0 0.0 0.0 0.0
Liquidation Value of Pref. Stock 0.0 0.0 0.0 0.0 0.0
Dividends on Redeemable Pref. 0.0 0.0 0.0 0.0 0.0
Dividends on Nonredeemable Pref. 0.0 0.0 0.0 0.0 0.0
Dividends per Common Share 1.510 1.492 1.392 1.187 0.950
Total Cash Dividends 1.510 1.492 1.392 1.187 0.950
Dil. Earn. per Sh. before Nonrec. Items 5.040 1.990 3.220 4.220 3.860
Market Price per Common Share 30.200 34.980 65.690 53.400 38.500
Tax Rate (0-1) 0.350 0.358 0.362 0.362 0.351
Common Shares Outstanding 305.4 308.5 328.4 355.2 378.3
Diluted Weighted Average Common Shares 305.4 308.5 328.4 355.2 378.3
Cash Flow from Operations 1455.0 1297.0 1109.0 2246.0 2273.0
Options:
Option...