Part 2. Pro forma Financial Statements of Target Corporation (TGT)
1. Projections: Find Projections Template from Capital IQ Excel Plugin/Templates/Valuation section. Update it with your company ticker
2. Guides: Please read the guides posted on the BB for each financial statement forecast.
3. Assumptions: Based on the manager?s guidance, business strategies, and industry overall perspectives, discuss your key assumption for the following items in each
financial statement in the next 5 years:
(1) Income statement: Revenue growth rate, Cost of revenues, SG&A expense, R&D
expense, D&A, Income tax rate etc.
(2) Balance sheet: Days Receivable/Payable, Days Inventory etc.
(3) Statement of Cash Flow: CapEx as % of Revenue, D&A as % of Revenue etc.
4. Summarize your company?s future financial performance.
-Just Part 2
uide to Balance Sheet Projections
ommon approaches to forecasting balance sheet line items when building a 3 statement odel
? This article is part of a larger guide: How to Build an Integrated 3 Statement Financial Model
alance sheet projections exercise
Imagine that we are tasked with building a 3-statement statement model for Apple. Based on analyst research and anagement guidance, we have completed the company?s income statement projections, including revenues, perating expenses, interest expense and taxes ? all the way down to the company?s net income. Now it’s time to turn
o the balance sheet.
etting up the balance sheet forecasts
ypically, the main balance sheet section of a model will either have its own dedicated worksheet or it will be part of a larger worksheet containing other nancial statements and schedules. Before we dive into individual line items, here are some balance sheet best practices (click here for a complete guide to
nancial modeling best practices):
1.At least two years of historical data
It is recommended that at least two years of historical results are inputted into the model to help provide some context to forecasts. Data is organized in columns ascending from left to right.
2.Reclassify GAAP to suit your needs
Companies present their balance sheet in ways that are not always optimized for analysis. For example, companies may lump line items with di”erent drivers together. In these cases, the line items need to be separated and forecasting approaches should be tailored to the nature of the items.
Conversely, GAAP requires that certain line items be broken out into current and long-term components (deferred taxes and deferred revenue are common examples). However, for forecasting purposes, they can be combined because they are forecast using the same drivers.
3.Use supporting schedules
All forecasting needs to be done in supporting schedules ? either in the same worksheet or in dedicated separate worksheets. This is where the forecasting and calculations should take place. The consolidated balance sheet simply pulls the finished product ? the forecasts ? to present a complete picture.
Wall Street Prep | www.wallstreetprep.com
creenshot of a consolidated balance sheet from Wall Street Prep’s Premium Package Training Program
e start the balance sheet forecast by forecasting working capital items. (For a complete guide to working capital, read our “Working Capital 101” article.) roadly speaking, working capital items are driven by the company’s revenue and operating forecasts. Conceptually, working capital is a measure of a ompany?s short-term financial health. Working capital items include:
ccounts receivable (AR)
Grow with sales (net revenues).
Using an IF statement, model should enable users to override with days sales outstanding (DSO) projection, where days sales outstanding (DSO) = (AR / Credit Sales) x days in period.
Grow with cost of goods sold (COGS).
Override with inventory turnover (Inventory turnover = COGS / Average inventory).
If prepaid expenses comprise expenses predominantly classified as SG&A, grow with SG&A. If you aren?t sure, grow with revenue.
ther Current Assets
Grow with revenues (presumably these are tied to operations and grow as the business grows).
If there’s reason to believe they are not tied to operations, straight-line the projections.
If the payables are generated predominantly for inventory, grow with COGS. If you aren?t sure, grow with revenue.
Override with payables payment period assumption.
Wall Street Prep | www.wallstreetprep.com