Corporate Financial Planning Model
In this model you will input historical financial statement information for the balance sheet and income statement, calculate cash flows, common size income statements and balance sheets, and finally project future cash flows. (Lecture FMCI)
- Select a non-financial publicly traded company (each student must select a different company) and go to that company’s website. Click on “Investor Relations” and then look for the company’s SEC filings, specifically the 10-K. You will need 4 years of historical balance sheets and income statements, which typically requires the company’s last 3 years of 10-K’s.
- You will input four years of historical data for your company. (Format ‘accounting’ with one decimal)
- Using Shift & arrow keys, highlight cells A1-P70, then Alt, W, I for Page Break Preview and then Alt, P, R, S. To add more pages, highlight cells below first page and then Alt, P, R, A. Setting your worksheet up in this way makes printing very easy.
- You will prepare the following schedules/statements:
- Income Statement
- Normalization adjustments (part of I/S)
- Income Statement % of revenue
- Balance Sheet
- Balance Sheet % of revenue
- Balance sheet & leverage metrics (ratios; part of B/S)
- Cash Flow statement (created)
- Fixed asset schedule – you will construct a ‘cascading’ depreciation expense schedule.
- Debt schedule – normally you would model each tranche of debt, but because some companies can have very numerous tranches, you will model debt in total. The debt schedule will include a “Revolver” that will be the assumed source of a financing need, if there is one.
- Cash schedule
- Income Statement (Lecture FMCIS) – Input from the company’s 10-K. See example for other items to include, such as growth rates and profitability margins.Remember – NEVER input totals.Your income statement should have the following line items. Some of your costs/expenses may differ.
Looking for help with your homework?
Grab a 30% Discount and Get your paper done!
Place an Order