Financial Statement Modeling
Copyright 2008 © by Wall Street Prep, Inc. | All rights reserved Inc.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE *****************************
Table of contents
SECTION 1: PREPARING FOR MODELING Welcome letter Introduction Gathering historical documents/information Understanding projections Modeling techniques in Excel Debt schedule Interest expense SECTION 3: ENHANCEMENTS TO A COMPLETE MODEL Balancing the model and circularity Ratio analysis Form toggles, sensitivity and scenario analysis
SECTION 2: BUILDING A FINANCIAL STATEMENT MODEL Income statement – historicals Income statement – projections Balance sheet – historicals Balance sheet – projections Working capital schedule
…show more content…
It is easy to forget that you hid the rows
Rows 5-8 are hidden
If you must hide information, “group” the rows or columns so the hidden information shows up on the margin. Shortcut in Excel to group / ungroup rows: Shift + Alt + Right arrow key / Left arrow key
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
7
Copyright © 2007 by Wall Street Prep, Inc.
Income statement – historicals
Inserting rows in Excel Highlight row: Shift Spacebar Add row: Shift Ctrl = Insert a dynamic header formula in Excel Using the & sign, you can combine text and formulas, so that whatever you type in the company name input area should automatically change the header. Formatting inputs As in all models, inputs are blue and calculations are black. In addition, inputs are designated by the light yellow background color for easier navigation.
***************************** SAMPLE PAGES FROM TUTORIAL GUIDE For illustrative Purposes Only *****************************
8
Copyright © 2007 by Wall Street Prep, Inc.
Income statement – historicals
Adjust historical IS for nonrecurring items Insert lines below reported COGS, SG&A and Other, Non-operating Income, and Taxes that will reference the nonrecurring items input area to calculate pro forma (normalized) historical results.
Also remember to change EBIT, EBT, and net income calculations to reflect ***************************** pro forma (vs.
Marketing. Because of the large number of suppliers selling similar products, apparel-retail firms must stimulate demand with attractive store layouts, colorful product offerings, and various sales promotions.
10)Retrospective treatment of prior years ' financial statements is required when there is a change from:
b. Prepare an income statement, a statement of retained earnings, and a balance sheet (See Exhibit 1.2, 1.3 and 1.4).
It is easy to forget that pouring money into a problem will not fix it unless revenue flows continue or are increased and expenses are controlled. Some of the easiest computations can be made with information retrieved from balance sheets and income statements provided by accountants. Ratios such as the current ratio, long-term solvency ratio, contribution ratio, programs and expense ratio, general and management expense ratio, fund-raising and expense ratio, and revenue and expense ratio can provide a picture of where a company stands now compared to where it was in past years and what may need to be done in the future.
ABC Company reports Net Income of $20,000 for the year 2010. The company negotiated a change in its defined benefit pension plan that increased the pension obligation by $3,000 attributed to the work already completed by its employees. $300 of this change was included in pension expense for 2010. Because of well-chosen investments, the pension trust fund had unexpected gains of $4,000 over its expected return of $2,000. Finally the actuaries estimate of the end of the year value of the Defined Benefit Pension Plan included a $1000 increase in the liability due to changes in actuarial assumptions.
Prepare the journal entries for the years 2008 to 2012 to record income tax expense and the effects of the net operating loss carrybacks and carryforwards assuming Synergetics Company uses the carryback provision. All income and losses relate to normal operations.( In recording the benefits of a loss carryforward, assume that no valuation account is deemed necessary.)
a. The type of adjusting entry needed at year-end (Use the following codes: A, adjust¬ment of a prepaid expense; B, adjustment of an unearned revenue; C, adjustment to record an accrued expense; or D, adjustment to record an accrued revenue.)
In order to evaluate your understanding of the use of accounting information, you are asked to analyze the 2007 financial statements of The Procter & Gamble Company (P&G). The financial statements of P&G are presented in Appendix 5B or can be accessed at the book’s companion website,
As a consultant it is my responsibility to recommend the best direction for a new business to follow in order to be successful in financial documentation. It is very important for a new business/owner to understand the functions of accounting and its’ basic accounting system. Since Accounting Information Systems (AIS) is used to store, collect and process financial information. It would be wise for a new business to know the ins and outs of recording their finances so that they can keep track of their finances to see if they are gaining or losing a profit.
| |Moderate | |40–50 | | | | | | | | | |9A | |Prepare a statement of cash flows—indirect method. | |Moderate | |40–50 | | | | | | | | | |*10A | |Prepare a statement of cash flows—direct method. | |Moderate | |40–50 | | | | | | | | | |11A | |Prepare a statement of cash flows—indirect method. | |Moderate | |40–50 | | | | | | | | | |12A | |Identify the impact of transactions on ratios. | |Moderate | |25–35 | | |
b. Prepare an income statement, a statement of retained earnings, and a balance sheet, (See Exhibit 1.2, 1.3 and 1.4)
Balance Sheet & Income Statement. For the Financial Statement file attached with the assignment, click on the annual
Trade allowances, amounts collected on behalf of third parties and net of returns comprise the revenue. Revenue is recognised as sales revenue, interest income and dividend receivable. Sales revenue is recognised when the considerable risks and rewards of the ownership have been transferred to the buyer. Interest income is recognised on an accrual basis, while dividend receivable recognition occurs when it is declared or received.
Accounts receivable – gross Allowance for doubtful debts Accounts receivable – net Plant at cost – gross Accumulated depreciation Plant at cost – net Land at fair value Development costs ‐ gross Accumulated amortisation Development costs – net Interest receivable Provision for long service leave Asset revaluation reserve Accumulated depreciation for income tax $ 200,000 (10,000) 190,000 600,000 (120,000) 480,000 600,000 200,000 (80,000) 120,000 20,000 60,000 100,000 240,000
YEAR 0 2009 1 2010 2 2011 3 2012 4 2013 5 2014 6 2015 7 2016 8 2017 9 2018 10 Initial Investment Gross Revenue 2 COGS 3 Add'l revenue Less: COGS Loan down payment 4 Loan repayment Depreciation Additional workers Land square required Moving cost 5 Operating Expenses Total Expenses Net Income Before Tax Income Tax Net Income After Tax After Tax Cash Flow ATCF Cummulative ATCF NPV through Year N