Investment Banking University is the world's only university dedicated to higher learning in investment banking.
Don’t miss anything. Follow Us.
WELCOME
CALL +1 920 264 3054
FOLLOW US
Top

Financial Statement Modeling

 

Financial Statements & Financial Statement Modeling

Financial statements are the formatting of accounts to allow for analysis. The structure for the financial statement model is copied from the financial statements into excel. Historicals are inputted and then we build our fully linked financial statement model for future years and we can run analyses on the projections. Pro formas are financial planning based upon assumptions that drive the corporation’s performance.

 

Modeling Process:

Start with IS historicals and forecast, but have to skip interest expense/income since it depends on BS items. Then BS historicals and forecast, separate schedules for each line item. BS has IS drivers, ratios in relation to IS line items, that cause the BS monetary value. Finally, the cash flow statement, allows us to plug the model, depending on whether the company has a cash surplus, excess cash plug, or cash shortfall, revolver plug, which is determined in the statement of cash flows. Finally, we error proof the model and build on scenario analyses and sensitivity analyses.

Financial Statement Modeling Process:

  1. Identify whether company displays data in thousands, millions or billions and list this at the top of the spreadsheet
  2. Identify company name, ticker, share price as of last close, close date, latest fiscal year end, and circuit breaker
  3. Create IS row headers
    1. Company specific row headers from the 10k
      1. Break out other interest income/expense into its three components so have row headers for each (footnotes in 10k)
    2. Diluted shares outstanding roll forward
    3. EPS row headers
    4. EBITDA reconciliation
      1. Add back D&A.
        1. Locate D&A from SCF
      2. Add back stock based compensation
    5. Growth rates and margins for line items other than revenue for the IS
      1. Implied growth rates and margins based upon historicals hardcoded into the IS
        1. First is revenue growth = This year revenues / last year revenues – 1. Consolidated growth rate derived from SEGMENT BASED CONSOLIDATED REVENUE GROWTH RATE CHECK and linked to it.
        2. Next is gross profit as a % of sales = gross profit / revenue
        3. Next is R&D margin = R&D / revenues. Don’t forget to use a negative sign so we get a positive margin
        4. SG&A margin = SG&A / revenues. Use negative sign so we get a positive margin.
        5. Tax rate = tax expense / pretax profit. Not revenue
      2. Revenue schedule by revenue, product and ASP
        1. Segment based data in product based breakouts in Sales Data disclosure in 10k
          1. Total line at the bottom with % growth headers
          2. Back into ASP by inputting revenue historical in schedule and product sales historical and dividing them to get ASP. Multiply by 1,000 to get the scale correct.
          3. FOR FORECASTING, use Equity Research drivers blue hardcoded for PRODUCT and ASP to then drive REVENUE AGGREGATE to get into detailed drivers on a product by product basis
          4. ASP needs formula in absolute number for ASP = last year * (1 + growth rate) and guess at the rate to get you the ABSOLUTE ASP which is consistent with EQUITY RESEARCH. Keep 0% change for years 3 through 5 since we don’t have Equity Research guidance
          5. Units are hardcoded % growth from Equity research and hardcode 3% growth for years 3 through 5
        2. Product and ASP have row for % growth for each line item
      3. Create BS row headers
        1. Company specific row headers from the 10k
        2. Row headers for Schedules for each line item starting with working capital
      4. Create time period column headers
        1. Need latest fiscal year end date (Latest 10k date)
        2. Three years historicals, five year projection period
          1. Start with third column in as the current last year historical 10k and work backwards to the first column header
          2. Input fiscal year above
  • Input fiscal year end date below
  1. A for actual
  2. E for expected (projection years)
  1. Input IS historicals as blue hardcoded numbers
    1. Income is presented as a positive
    2. Expenses is presented as a negative
  2. Build a revenue schedule by product units sold and ASP (average selling price). Blue hardcoded driver is derived from Equity Research (the Street) for initial projection periods for base case. Roll this up into revenue.
  3. For FORECASTING the IS, build IS blue hard coded drivers for each line item in IS. Blue drivers drive black linked line items in same year.
    1. Skip interest income (function of cash balance on BS) and interest expense (function of debt balance on BS)
    2. MARGINS AND RATES DRIVE THE EXPENSE ITEMS ON THE IS
      1. Pull the forecast DRIVERS (blue hardcoded) from Equity Research for first two years and hold even from years 3 through 5
        1. Gross profit as a % of sales drives GROSS PROFIT line item = Revenues * gross profit as a % of sales
        2. Back into COGS = Revenues – Gross profit
        3. R&D % drives R&D line item = Revenues * R&D %
        4. SKIP interest expense/income
        5. Straightline nonoperating items including OTHER EXPENSE
        6. Tax = Pretax profit * tax rate
      2. Leave BASIC SHARES OUTSTANDING, DILUTED and EPS blank
  • Leave D&A and Stock based comp blank
  1. Leave EBITDA blank
  1. Input BS historicals
    1. Two years worth of historical results since company disclosures have two years (SEC required)
    2. Aggregate line items for cash into one row header since these are all really cash equivalents
      1. Cash
      2. Short term marketable securities
  • LT marketable securities
  1. Aggregate vendor nontrade receivables and other current assets
  2. Aggregate Goodwill and acquired intangible assets into one line item
  3. Aggregate Accrued Expenses and Deferred Revenue (current and noncurrent)
  4. LINE ITEM ROW FOR REVOLVER ADDED IN (NOT ON HISTORICAL BS)
  5. Aggregate Common stock and APIC (additional paid in capital)
  6. LINE ITEM ROW FOR TREASURY STOCK. Companies may lump buybacks into retained earnings to show retained earnings net of treasury buybacks.
  7. Balance sheet check = total assets – liabilities – equity
  8. Determine historical ratios based upon historical absolute numbers in balance sheet
    1. Row headers for RATIOS
      1. Net debt = sum (company’s debt) – company’s cash
      2. Asset turnover
      3. Net profit margin
      4. Return on Assets
      5. Return on Equity
    2. For FORECASTING THE BS, Build roll forwards (Prior year’s ending balance as BEGINNING OF PERIOD, then ADDITIONS & SUBTRACTIONS during period, then get to END OF PERIOD balance which is the line item balance for that year) for each line item from historicals by the blue driver to forecast the black linked line item below the balance sheet:
      1. Start with Working Capital
        1. AR
          1. Natural driver is revenue. Grow AR with revenue growth rate
          2. Also want to track Days Sales Outstanding when it comes to AR. DSO = (AR/revenue) x days in period. How long does it take to collect on receivables?
          3. Determine EOP balance by referencing the AR from that year for historical years
          4. Determine AR as a % of revenue = AR/revenue. This is for two years historical.
          5. Determine Days Sales Outstanding
          6. Determine BOP balance for first projection year by referencing EOP balance from previous historical year.
          7. TO FORECAST AR, refer to EQUITY RESEARCH for the DSO HARDCODED BLUE DRIVER to get to EQUITY RESEARCH ABSOLUTE NUMBERS FOR AR for EOP to then back into AR as a % of revenue. DSO talked about more by analysts.
          8. Calculate the roll forward change
          9. Build IF statement to handle explicit thesis for DSO, but does not require it, as in grow with revenue growth rate instead of DSO.
        2. Inventory
          1. Natural driver is COGS since inventory cycles out through the IS through COGS. More COGS, more inventory
          2. Also want to track inventory turnover, when it comes to inventory. IT = COGS/inventory
          3. Determine EOP balance by referencing the AR from that year for historical years
          4. Determine Inventory as a % of COGS = I/COGS. This is for two years historical.
          5. Determine Inventory Turnover historical
          6. Determine BOP balance for first projection year by referencing EOP balance from previous historical year.
          7. TO FORECAST AR, refer to EQUITY RESEARCH for the IT HARDCODED BLUE DRIVER to get to EQUITY RESEARCH ABSOLUTE NUMBERS FOR I for EOP to then back into I as a % of COGS. Hold IT flat (= previous years IT). IT talked about more by analysts.
          8. Calculate the roll forward change
  • AP
    1. The natural driver is COGS or revenue, most commonly revenue. Grow AP with revenue. More revenue, more receivables. If AP comprised of inventory, then tied to COGS. If AP comprised of SG&A then tied to revenue (broader default relationship). Drivers are company specific. Allow user to model different assumption than Revenue or COGS.
    2. Also want to track Days Payable Outstanding when it comes to AP. How long can we get away with not paying our vendors.
    3. DPO = AP/COGS x days in period
    4. Determine EOP balance by referencing the AP from that year for historical years
    5. Determine AP as a % of COGS = AP/COGS. This is for two years historical.
    6. Determine DPO historical
    7. Determine BOP balance for first projection year by referencing EOP balance from previous historical year.
    8. TO FORECAST AP, refer to EQUITY RESEARCH for the DPO HARDCODED BLUE DRIVER to get to EQUITY RESEARCH ABSOLUTE NUMBERS FOR AP for EOP to then back into AP as a % of COGS. DSO talked about more by analysts.
    9. Calculate the roll forward change
  1. AE & DR
    1. The natural driver is revenue. If going into SG&A solely, then grow with SG&A. The more revenue the more expenses you have, this drives the accrued expenses. Allow user to model different assumption. AE and DR are lumped together in the roll forward schedule.
    2. Determine EOP balance by referencing the AE from that year for historical years
    3. Determine AE as a % of Revenues = AE/Revenues. This is for two years historical
    4. Determine BOP balance for first projection year by referencing the EOP balance from previous historical year.
    5. TO FORECAST AE, hold constant the AE & DR % of revenues hardcoded blue to get the same as EQUITY RESEARCH
  2. Then work way down the balance sheet starting with Assets, then liabilities, then equity.
    1. Intangible Assets
      1. Last historical EOP begins as BOP balance. Purchases increase Intangible and amortization expense decreases the intangible assets (amortization expense means noncash expense on the IS and add back on the SCF)
      2. Projection period first year has BOP balance as last historical year’s EOP balance
      3. LOOK FOR HISTORICAL PURCHASES OF INTANGIBLE ASSETS AND AMORTIZATION ON 10k. NOTE 4, look at NET CARRYING AMOUNT AND HISTORICAL AMORTIZATION EXPENSE FOR DEFINITE LIVED ASSETS. GO TO SCF TO SEE PURCHASES UNDER INVESTING ACTIVITIES. GET FOR ALL THREE HISTORICAL YEARS. AMORTIZATION EXPENSE GUIDANCE PROVIDED, SIMPLY HARDCODE THIS INTO FORECAST YEARS. ASSUME NO NEW PURCHASES IF YOU TAKE GUIDANCE ON AMORTIZATION. CHECK EQUITY RESEARCH REPORT TO SEE IF FORECASTING AN INTANGIBLE ASSET PURCHASES IN SCF.
      4. Link in the roll forward to purchases and amortization drivers.
      5. The value of intangible assets can only increase due to PURCHASES not WRITEUPS.
      6. For FORECASTING, assume that purchases are in line with historical trends using % of Sales or straightline the dollar amount. If intangibles are lumpy and not consistent, assume no new purchases (lumpy is most companies).
      7. Future amortization of existing assets disclosed in 10k. If new purchases, then future INCREMENTAL AMORTIZATION with useful life assumption.
      8. Infinite lived assets, trademarks and goodwill.
      9. Writedowns and sales of intangibles may need to be included in roll forward schedule. Don’t include unless specific guidance. Does the sale impact your IS (was the intangible asset driving sales?). Does the cash proceeds equal the book value? Will have a noncash gain that needs to be put on the IS.
    2. PP&E
      1. Start with prior periods EOP balance and BOP balance
      2. CAPEX makes balance go up and depreciation makes it go down. Depreciation effects income statement in line items COGS and SG&A and SCF is add back.
      3. Purchases of CAPEX should be in line with historicals. Absence of guidance, use historical % of sales or straight line.
      4. If mature company, CAPEX grows at a lower rate than a high growth startup. If distress or restructuring, then prune back CAPEX. Maintenance (bare minimum) CAPEX vs. discretionary CAPEX.
      5. For depreciation, forecast as a % of CAPEX using historical ratio of depreciation to CAPEX. Historical depreciation not disclosed, since we have D&A, we can back into historical depreciation. Arrive at D, by finding A and subtracting it from D&A.
      6. When companies provide book value of PP&E by category, along with useful life estimates, a more complex depreciation forecast can be made. Can build depreciation schedule. But book value of PP&E is almost never provided, a function of receiving private information.
      7. Land is not depreciable
      8. The more CAPEX a company has made, the more Depreciation it will see in the future. Depreciation is the cycling out of CAPEX through the income statement as an expense.
        1. For high growth companies, CAPEX outpaces depreciation. CAPEX/depreciation = >1
        2. For mature or declining businesses, Depreciation catches up and sometimes goes larger than CAPEX, =1. Ratio should converge to 1.
      9. Writedowns not explicit in models.
      10. Modeling Sales of PP&E is uncommon except for some industries. Company consistently make sales? Impact the IS (did PP&E drive sales)? Do cash proceeds equal book value, if exceed there is to be a gain on sale on IS which is a noncash decrease NI and add back on SCF.
      11. HISTORICALLY, can find CAPEX on the SCF in 10k and get CAPEX as a % of revenue historically.
      12. HISTORICALLY, get Depreciation by D&A in EBITDA reconciliation and add the negative Amortization to get D. Get Depreciation as a % of CAPEX.
      13. FORECASTING, CAPEX as a % of revenue as a driver but want to be in line with EQUITY RESEARCH. Arrive at a forecast that approximates ER. Drives CAPEX.
      14. FORECASTING, straight line historical Depreciation as a % of CAPEX. Drives Depreciation. Get to absolute results that ER is suggesting. Backing into the Street case.
      15. FORECAST EBITDA RECONCILIATION D&A based upon our forecast of separate Depreciation and Amortization. What should our driver look like to approximate the Street case on the ER report?
      16. Link Roll Forward Schedule to Absolute CAPEX and Depreciation that were driven by the DRIVERS
    3. Other Current Assets & Other Non-Current Assets
      1. Catch all OTHER current all lines for prepaid expenses
        1. If tied to operations, want to grow with revenue otherwise straightline. As business grows, expect accruals to grow as well to support growing business. Need to read disclosures to then determine the DRIVER.
        2. To see what is inside of OTHER, go to the footnotes
      2. Catch all OTHER noncurrent all deferred tax assets, pension assets
        1. Associated with nonoperating. Straightline if no disclosures
      3. OCA schedule including non-trade receivables
        1. Go to 10k BS and see disclosures for OCA and vendor non-trade receivables
        2. Straightline at 0 to be consistent with Equity Research
      4. DTA schedule
        1. EOP in that year is the BS line item for the two years
        2. BOP in future projection year is the EOP balance from historical year
        3. Are the actual items within the disclosure tied to operations? Then grow with revenue.
      5. OA schedule
        1. EOP in that year is the BS line item for the two years
        2. BOP in future projection year is the EOP balance from historical year
        3. Are the actual items within the disclosure tied to operations? Then grow with revenue.
        4. ER research straightlines so we will. Straightline means zero increase/decrease
      6. ONL schedule
        1. EOP in that year is the BS line item for the two years
        2. BOP in future projection year is the EOP balance from historical year
        3. Are the actual items within the disclosure tied to operations?
          1. Made up of DTLs so search for disclosures. Waiting for repatriation of cash. Money owed to the IRS. Recognition of taxes owed but not yet paid
          2. Hardcode the change in the ER report amount in the roll forward to get to EOP balance
        4. Deferred Tax Assets (Prepay of taxes for revenue that hasn’t been recognized on a GAAP basis yet)
          1. When actual taxes are greater than GAAP. Can be presented as current (expect reversal in next 12 months) or noncurrent asset. Book recognition of revenue vs. tax recognition of revenue. Actual tax authorities are more aggressive in recognizing revenue. Captures temporary difference between actual taxes vs. GAAP. Instead of cash going down, what gets reversed is DTA.
          2. Best practice is straightline historical balance.
        5. Deferred Tax Liabilities (Defer required payment of taxes for revenue that has already been recognized on a GAAP basis)
          1. When actual taxes are less than GAAP. Captures temporary difference between actual taxes vs. GAAP. What gets reversed is the DTL. The excess is captured is a DTL.
          2. Best practice is straightline historical balance. Or grow with revenue.
        6. Other Liabilities
          1. Other Current Liabilities (Catchall)
            1. If tied to operations, grow with operations. If financial, then straightline it.
          2. Other Noncurrent Liabilities(Catchall)
            1. If tied to operations, grow with operations. If financial, then straightline it. A lot of BS line items are straightlined, especially when there is no clear driver (not sure what line item represents as in no disclosures). If management or equity research is guiding to straightlining then ok to straightline. Ok to straightline small items.
          3. Debt
            1. Roll forward increased by NEW BORROWINGS and PAID IN KIND INTEREST (PIK), which is a noncash interest expense on IS and add back on SCF, and decreased by PAYDOWN of debt
            2. Types of Debt: Bank debt (directly issued by lenders, interest rate tied to LIBOR plus some spread, principal payments (amortization) made throughout the term of the loan, most restrictive covenants and secured by assets of the firm so bank debt is cheaper) vs. Bonds (issued by company and purchased by investors, interest rate structure is fixed coupon and no principal paydown until maturity, unsecured)
            3. In 10k there is a Debt footnote and schedule of principal payments on aggregate debt and usually corresponding interest rates and terms of the borrowing.
            4. FORECASTING, you want to reflect paydown if declining debt is assumed to not be replaced by new debt to maintain capital structure. Assume debt level stays the same. Straight line the debt. New debt will replace old debt
            5. For PIK option on loan, accrues interest expense as PIK INTEREST. Interest expense on IS but no cash impact so added back on SCF. Accrued principal is growing slowly by amount of PIK interest.
            6. EOP for both historical years references BS and BOP for forecast year first is EOP for previous historical year
            7. Go to 10k for note of LONG TERM DEBT. Floating rates tied to LIBOR. Swaps to make floating rates to fixed interest rates. ER shows no new borrowing, but reflect beyond ER, that paydown. HARDCODE PAYDOWN in ROLL FORWARD.
            8. FIND INTEREST EXPENSE on LONG TERM DEBT by backing into with a given INTEREST RATE DRIVER BLUE HARDCODED into the future. =Interest Rate* Average(previous period, current period)
            9. FIND PIK ACCRUAL by % paid in PIK vs. cash so the INTEREST EXPENSE ON LT DEBT IS ADDED TO PRINCIPAL IN THE AMOUNT OF THE % PAID IN PIK. This is called a PIK TOGGLE (choice of PIK or not)
          4. Capital Stock
            1. Roll forward begins in forecast year with previous period historical EOP balance
            2. Increases with NEW ISSUANCES and STOCK BASED COMPENSATION (value of issued to employees, recognize the value as an expense).
            3. Capital stock is common stock and APIC (aggregate them)
            4. FORECASTING, assume no new issuances, unless management and ER guiding to this. If going to forecast new share issuances, factor this is share count for EPS. Stock based compensation expense Forecast is to straightline % SBC to sum(COGS, R&D, SG&A) to then back into STOCK BASED COMPENSATION. GET NEAR ER amounts for SBC.
            5. New share issuances located on SCF (Proceeds from issuance of common stock)
            6. AFTER SBC FORECASTED, CAN FINISH FORECASTED EBITDA RECONCILIATION
          5. Treasury Stock
            1. Contra account so starts as a negative and gets more negative, accumulation of all prior buybacks
            2. Statement of shareholder’s equity financial report or SCF (Financing section) detailed REPURCHASES OF COMMON STOCK
            3. Roll forward increases the negative by SHARE BUYBACKS.
            4. In absence of formal buyback program, straight line historical buybacks. Companies buy back shares on a regular basis to mitigate dilution that occurs from others exercising options.
            5. Impact to share count will depend on share price at the time
            6. Look in 10k for repurchase program.
            7. FORECAST HARDCODED ER AMOUNT OF BUYBACKS
          6. Retained Earnings
            1. Roll forward increases by net income and decreases by dividends (common or preferred)
            2. FORECASTING is going to have missing parts of net income. FORECASTING dividends by % dividends to net income historical ratio.
            3. Historical dividends in SCF (financing)
            4. DIVIDEND PAYOUT RATIO is going to be dividends/net income. Do historical first. Dividends as a % of net income. If do not have ER, then straightline the dividend payout ratio. Override PAYOUT RATIO TO GET TO ABSOLUTE NUMBER OF DIVIDENDS IN ER REPORT.
            5. Complete the roll forward by pulling NI and dividends in
          7. OCI
            1. Represents income/loss accumulated that does not flow through retained earnings
            2. Roll forward OCI in current year. Example would be currency, derivatives, hedges.
            3. FORECAST OCI, straightline historical balance by forecasting 0 gains and losses. Assume no change.
            4. Historical two periods EOP
          8. Then consolidate the roll forward schedules into the consolidated BS
            1. Start from working capital and input EOP balance into line items into the balance sheet
            2. All done except for int income/expense, cash, and revolver (plugs and plug derivatives)
            3. Fill RATIOS to the right
          9. SCF line items are changes in BS. The final statement that is forecast. Don’t need historical SCF. The forecast is changes in the balance sheet. When models don’t balance, often due to mistakes in SCF. Need to build a SCF to figure out what CASH BALANCE will be and REVOLVER BALANCE (IS) will be and INTEREST INCOME and INTEREST EXPENSE (BS)
            1. Cash Flows from Operations
              1. Income Statement line items cash be referenced directly on SCF. BS items need year over year changes. CAN USE THE ROLL FORWARD SCHEDULES FOR THIS SO GRAB THE CHANGE IN THE ACCOUNT LINE ITEM.
              2. Assets are NEGATIVE and Liabilities are POSITIVE. SWITCH THE SIGN ON ZEROS STILL WHEN NECESSARY.
            2. Cash Flows from Investing
              1. CAN USE THE ROLL FORWARD SCHEDULES FOR THIS SO GRAB THE CHANGE IN THE ACCOUNT LINE ITEM.
              2. Assets are NEGATIVE and Liabilities are POSITIVE. SWITCH THE SIGN ON ZEROS STILL WHEN NECESSARY.
            3. Cash Flows from Financing
              1. CAN USE THE ROLL FORWARD SCHEDULES FOR THIS SO GRAB THE CHANGE IN THE ACCOUNT LINE ITEM.
              2. Assets are NEGATIVE and Liabilities are POSITIVE. SWITCH THE SIGN ON ZEROS STILL WHEN NECESSARY.
  • SKIP REVOLVER
  1. Net Change in Cash
    1. CFO – CFI – CFF = net change in cash
    2. CAN FINISH BS CASH WHICH IS LAST YEAR CASH + NET CHANGE IN CASH FROM SCF
  • Asset side of balance sheet done
  1. Build plug schedules for excess cash and revolver
    1. Revolver
      1. Revolving credit facility, is a core plug that handles deficits. Plug for surplus is excess cash. Plug on deficit is revolver.
      2. To support short term working capital needs. Secured by AR and Inventory. Can borrow up to 90% of AR plus 70% of inventory for example. Often a component of borrowing from a bank. Priced at LIBOR plus some spread.
  • Revolver balance rises if cash shortfall
  1. If surplus, cash rises only after revolver balance paid down
  2. Still want to build revolver into model even if don’t have a revolver. When building weak case, will have to use the revolver
  3. ROLL FORWARD LOGIC, increases with NEW BORROWING, revolver needs analysis starts with BOP cash balance, surplus pay down revolver. Minimum cash assumption to get to available cash. Add new Free Cash Flows (all CFs except for revolver). If revolver fully paid down, remaining cash surplus adds to cash balance. When decreasing the revolver, this is a cash use and will have to get tracked in the CFF section revolver line item.
  • If have losses after min cash, revolving credit line is increased by the amount. CFF section on SCF revolver line item inflow.
  • REVOLVER SCHEDULE HAS MAX AVAILABILITY (% of AR and INVENTORY)
  1. REVOLVER NEEDS ANALYSIS IS, WHAT IS THE DEFICIT THAT EXISTS THAT REQUIRES A REVOLVER DRAW? BEGINNING OF PERIOD CASH EQUALS EOP CASH THE YEAR BEFORE ON BS. LESS MINIMUM CASH EQUALS BOP EXCESS CASH. MINCASH OF $5BN FOR EXAMPLE. ADD FCFs FROM THE PERIOD FROM THE SCF (CFO + CFI + SUM(CFF BEFORE REVOLVER). EQUALS CASH AVAILABLE TO PAYDOWN/DRAWDOWN.
  2. Roll forward INCREASES/DECREASES, USE – MIN FUNCTION(CASH AVAILABLE, BOP REVOLVER). Spits out whatever balance is smaller.
  3. INTEREST RATE ON REVOLVER, GUIDANCE ON THIS, CAN LOOK AT LONG TERM DEBT RATES. 2%
  • INTEREST EXPENSE ON REVOLVER = RATE * AVERAGE(BOP REVOLVER BALANCE, EOP REVOLVER BALANCE)
  • LINK REVOLVER CHANGE IN SCF (CFF) TO CHANGE IN SCHEDULE FOR REVOLVER BELOW  
  • LINK REVOLVER BALANCCE IN BS TO EOP REVOLVER IN SCHEDULE
  1. Interest income
    1. Now can complete the CASH ROLL FORWARD
    2. Back into Interest rate on cash, find Interest income from 10k for historicals. Can get IMPLIED INTEREST RATE ON CASH = INTEREST INCOME / AVERAGE(EOP THIS PERIOD, EOP PREVIOUS PERIOD)
  • Can find WEIGHTED AVERAGE INTEREST by searching in 10k to get interest rate and simply hard code it historically and then straight line the % going forward
  1. INTEREST INCOME AS INT RATE * AVERAGE (BOP, EOP CASH)
  2. NOW THAT WE HAVE INTEREST INCOME, WE CAN FINISH THE INCOME STATEMENT BY LINKING INTEREST INCOME TO INTEREST INCOME IN CASH SCHEDULE. WE CAN ALSO GET INTEREST EXPENSE BY LINKING TO REVOLVER AND LT DEBT SCHEDULE INTEREST EXPENSE.
  1. EPS
    1. To calculate Earnings Per Share, we need to calculate the share count.
      1. We needed to go through equity items on BS to determine if company was going to issue shares (increase basic share count) or buyback shares (decrease basic share count)
      2. Basic share count is actual share count
      3. Need to get to a SHARE PRICE to get to share count. USE CONSENSUS EPS ANNUAL GROWTH AS A PROXY FOR SHARE PRICE GROWTH. Holding the PE ratio multiple constant, the increase in EPS should drive the same increase in share price. Solve for Price and change the EPS.
      4. Share price current x (1 + growth rate) = New share price
      5. EPS over the long term approximates value creation
    2. Diluted shares includes options, warrants, and convertible debt and stock.
  • STRAIGHT LINE THE HISTORICAL DIFFERENCE BETWEEN BASIC AND DILUTED SHARES OUTSTANDING
  1. SHARES OUTSTANDING SCHEDULE TO GET TO EPS. IN MODEL, YOU HAVE WEIGHTED AVERAGE SHARE COUNT FOR BASIC AND DILUTED, NOT EOP. EOP SHARE COUNT IS ON THE FRONT PAGE OF THE 10K. Latest historical EOP is the 10K share count. Future pro forma first year is BOP which is EOP from previous year.
  2. Shares outstanding are increased by issuances, which are not forecasted in model, and decreased by buybacks which are forecasted in model. Need to get consensus EPS for future years to get implied EPS growth and thus implied average share price growth. CONSENSUS EPS IS FOR DILUTED EPS. CAN GET EPS HISTORICAL. Average share price for last year by analyzing every day share price for last year. Grab two years consensus EPS results. For years 3 through 5, use analysts long term growth rate. Back into IMPLIED EPS. THEN BACK INTO IMPLIED AVERAGE SHARE PRICE.
  3. Shares repurchased aggregate dollar amount taken from CAPITAL STOCK SCHEDULE and divided by implied share price to get to how many shares bought back.
  • Now can link to the Basic and Diluted share count up top but take AVERAGE OF BOP AND EOP, not just EOP.
  • Straight line impact of diluted shares
  1. Find Basic EPS and Diluted EPS by taking NI / basic shares outstanding and diluted shares outstanding
  1. Balancing the Model
    1. By linking interest income and interest expense into the income statement, we created a circularity.
    2. ENABLE ITERATIVE CALCULATIONS
    3. BUILD CIRCUIT BREAKER INTO MODEL. SAYS THAT IF MODEL BLOWS UP, ELIMINATE THE SOURCE OF THE CIRCULARITY BY HAVING INTEREST EXPENSE ON REVOLVER AND INTEREST INCOME ON CASH SCHEDULES BE ZERO.
      1. Alt D L for data validation. List. Allow list. Source ON, OFF
      2. Start with OFF and have drop down menu for ON
  • Interest income and interest expense do IF statement which says, IF circuit breaker, is off, do function previously, otherwise, 0 which fixes the broken model
  1. Circuit breaker is turned on when model breaks to fix it. Then turn back off to get to normal model again.
  2. TO AVOID CIRCULARITY, DO NOT USE AVERAGE OF BOP AND EOP. SIMPLY DO INTEREST RATE * BOP BALANCE
  1. If BS is off, divide that amount by 2 and try to find that number and then switch the sign (usually in the SCF)
  2. Sanity check base case model against Equity Research report
  1. Sensitivity Analysis using data tables
    1. How is the projected EPS effected by changes in the revenue growth rate? This is called sensitivity
    2. Output variable in the top left corner. Row variable. Column variable and reference where in the model the row variable is and column variable is.
    3. Highlight entire table after output variable, and the input variables in the row and column are done. Alt D T. Choose input variable location for row and header. Then hit enter. Then hit F9 to calculate the table.
  2. Scenario Analysis
    1. Create drop down menu for base case, weak case and best case.
    2. Create an area for the main drivers of the IS that will choose the given assumption best upon the case. In the area for drivers, each cell will have an OFFSET WITH MATCH statement to choose the assumptions based upon the case.
    3. Now reference the scenario analysis area into the GROWTH RATES & MARGINS JUST BELOW THE INCOME STATEMENT

 

Then build roll forward schedules first, then roll up into the consolidated balance sheet. First you forecast working capital line items.

 

 

 

 

 

 

 

 

 

 

 

 

Conclusion

Financial statement modeling refers to the creation of a standalone operating model for a company. The operating model is built using historical performance (i.e. historical financial statements). We use the operating model to see pro forma performance of a company given certain assumptions. These pro-formas are the basis for decision making on the build-side, the sell-side and the buy-side.

Post a Comment

User registration

Reset password