Index Sheet Index: Financial StatementsThe page has your financial statements and common sized analysis Balance Sheet LeadThis sheet uses ratios and the IS to drive working capital accounts Fixed...

MBA Accounting Forecasting Project


Index Sheet Index: Financial StatementsThe page has your financial statements and common sized analysis Balance Sheet LeadThis sheet uses ratios and the IS to drive working capital accounts Fixed Assets Lead SheetLead sheet for your fixed assets (instructions on that page) Operating Expense Lead SheetLead sheet for your operating expenses (instructions on that page) Revenue Lead SheetLead sheet for your revenues (instructions on that page) Financial Statements PROJECTED INCOME STATEMENTS Common Sized 202020212022202020212022 Revenue Cost of Goods Sold Gross Margin Operating expneses Selling Expenses General & Administrative Expenses Depreciation Expense Total Operating Expenses Income from Operations Other revenue (expense) Interest Pretax Income Income Tax Expense Net Income PROJECTED STATEMENT OF RETAINED EARNINGS 202020212022 Beginning Retained Earnings +Net Income -Dividends Ending Retained Earnings PROJECTED BALANCE SHEETS Opening202020212022202020212022 ASSETS Current Assets Cash$ - 0 Accounts Recievable Inventory Prepaid and Other Assets Total Current Assets Long Lived Assets Property, Plant and Equipment Accumulated Depreciation Net P,P&E TOTAL ASSETS LIABILITIES & EQUITY Current Liabilities Accounts Payable Accrued Other Current Liabilities Total Current Liabilities Long Term Liabilities LT-Debt$ - 0 TOTAL LIABILITIES OWNERS' EQUITY Common Stock$ - 0 Retained Earnings TOTAL EQUITY TOTAL LIABILITIES & EQUITY PROJECTED STATEMENT OF CASH-FLOWS 202020212022 Cash Flow from Operating Activities Net Income Adjustments to reconcile Net Income to cash used in operating activities: Depreciation Change in Operating Assets & Liabilities Accounts Receivable Inventory Prepaid and Other Current Assets Accounts Payable Accrued & Other Current Liabilities Net Cash generated (used) in Operating Activities Cash Flow from Investing Activities Purchases of Property, Plant & Equipment Net Cash generated (used) in Investing Activities Cash Flow from Financing Activities LT-Debt Additional Equity Financing Distributions to Stockholders Net Cash generated (used) in Financing Activities Net Change in Cash Beginning Cash Balance Ending Cash Balance Revenue Lead Sheet Revenue Lead Sheet202020212022 REVENUE SOURCE 1 $ per unit Units sold Total source 1$ - 0$ - 0$ - 0 REVENUE SOURCE 2 $ per unit$ - 0$ - 0$ - 0 Units sold Total source 2$ - 0$ - 0$ - 0 REVENUE SOURCE 3 $ per unit$ - 0$ - 0$ - 0 Units sold Total source 3$ - 0$ - 0$ - 0 REVENUE SOURCE 4 $ per unit$ - 0$ - 0$ - 0 Units sold Total source 4$ - 0$ - 0$ - 0 REVENUE SOURCE 5 $ per unit Units sold Total source 5$ - 0$ - 0$ - 0 Total Revenue to inc stmt$ - 0$ - 0$ - 0 COGS % COGS $$ - 0$ - 0$ - 0 Instructions: This lead sheet has 5 sources of revenue built into the model. The product of $per unit and units gives you revenue by source. The sources total up on Row 32. If you need to add more just insert some rows and add the toal by source to cell by year to line 32. Operating Expense Lead Sheet Expense Lead Sheet202020212022 Selling Expenses Advertising$ - 0$ - 0$ - 0 Other$ - 0$ - 0$ - 0 Other$ - 0$ - 0$ - 0 Other$ - 0$ - 0$ - 0 Other$ - 0$ - 0$ - 0 Other$ - 0$ - 0$ - 0 Other$ - 0$ - 0$ - 0 Total Selling Expenses$ - 0$ - 0$ - 0 General & Administrative Expenses Rent$ - 0$ - 0$ - 0 Other$ - 0$ - 0$ - 0 Other$ - 0$ - 0$ - 0 Other$ - 0$ - 0$ - 0 Other$ - 0$ - 0$ - 0 Other$ - 0$ - 0$ - 0 Other$ - 0$ - 0$ - 0 Other$ - 0$ - 0$ - 0 Other$ - 0$ - 0$ - 0 Other$ - 0$ - 0$ - 0 Other$ - 0$ - 0$ - 0 Total General & Administrative$ - 0$ - 0$ - 0 Instructions: This lead sheet has two primary components Selling expenses and General and Administrative expenses. Simply add in your list of expenses in Column B and the dollars in the respective years and it will total and link to the Income Statement. Fixed Assets Lead Sheet Depreciation20202021202220232024 ADDITIONSLifeCost 2020Type 10.00$0 2021Type 10.00$0 2022Type 10.00$0 2023Type 10.00$0 2024Type 10.00$0 - 0- 0- 0- 0- 0 LifeCost 2020Type 20.00$0 2021Type 20.00$0 2022Type 20.00$0 2023Type 20.00$0 2024Type 20.00$0 - 0- 0- 0- 0- 0 LifeCost 2020Type 30.00$0 2021Type 30.00$0 2022Type 30.00$0 2023Type 30.00$0 2024Type 30.00$0 - 0- 0- 0- 0- 0 LifeCost 2020Type 40.00$0 2021Type 40.00$0 2022Type 40.00$0 2023Type 40.00$0 2024Type 40.00$0 - 0- 0- 0- 0- 0 Table to drive Financial Statements Depreciation Expense PP&E Cost Accum. Depreciation Net PP&E Instructions: This lead sheet has 5 different buckets of fixed assets. Simply put the useful life into column C and the Cost in column D for the year you plan to purchase the asset and it will depreciate the asset and link it over to the Income Statement, Balance Sheet, and Statement of Cash-Flows. So you could group Type 1 = all assets with a useful life of 5 years. Type 2 all assets with 10 year depreciable life. It uses straight line and zero residual Balance Sheet Lead WORKING CAPITAL ASSUMPTIONS202020212022 Accounts Recievable - Inventory - Prepaids & Other - Accounts Payable - Accrued & Other Current Liabilities- Information for Initial LT-Debt Term (enter term in years) Rate (enter interest rate) Borrowing (link to opening BS) Pmt Interest Principal Balance Key Financial Ratios 202020212022 Return on Assets Return on Equity Total Asset Turnover Fixed Asset or PPE Turnover Current Ratio Quick Ratio Debt-Equity Ratio ACCT (FINA) 470 Forecasting project Introduction: This project has two primary objectives. First, you will develop a basic understanding of Excel. Second and more important, the project is designed to help you understand how transactions and events captured in an accounting system impact financial statements and consequently the key metrics used to analyze a firm. I encourage you to make your forecast of a simple company you may have thought about starting. When the accounts relate to something tangible in your mind it is easier to understand how the financial statements work. I will not grade the forecast based on whether it is a viable business model, but instead on how well the model works (e.g., can I change your revenue assumptions and see the impact on each of the financial statements and does the balance sheet still balance). This project should cause you to struggle at some points. It is well-known that what we struggle with is what we learn. Task: It is an individual project, with each individual developing a three year financial forecast (I am fine with you working together to understand how things flow, but you cannot simply copy another project). To help you be successful, I have posted a number of videos in the Project Module. These videos will walk you through key portions of the project. Process: Step 1: Open up the forecasting spreadsheet and have a quick look around. You will find the spreadsheet with the following tabs in the Project Module: 1. Financial Statements 2. Revenue Lead Sheet 3. Expense Lead Sheet 4. Fixed Asset Lead Sheet 5. Balance Lead Sheet and Analysis Step 2: On the financial statements tab, I provide you with a skeleton of the financial statements. You will end up having formulas for nearly all the accounts on your financial statements: 1. Income Statement 2. Statement of Shareholders Equity (or simply Retained Earnings) 3. Balance Sheet 4. Cash Flows Step 3: Develop your revenue lead sheet. You need to make up a price per unit and number of units sold for some of the revenue sources (I put five in the model, use as many as you like) this will result in the revenue produced by each revenue source. I have put a total revenue line on row 32 of the Revenue Lead Sheet tab. You should make the total revenue line on the Revenue Lead Sheet tab link over to the Income Statement on the Financial Statements Tab for total revenue. This is also a good place to estimate COGS. Normally it would be specific to each revenue source, but in this example we are going to apply a single Cost of Goods Sold percentage (COGS%) to all revenue sources. At the bottom of the Revenue Lead Sheet you will see a COGS % row (row 34) you should input a percentage for COGS in these cells. Then, just like total revenue, you will link COGS $ to the Income Statement on the Financial Statements tab. Step 4: Develop your expense lead sheet. This should be the build-up of the assumptions you will use to forecast expenses. I left a large number of “other” expenses which you can change to be the specific expenses you think are relevant to the category (selling or general and administrative). You do not need to use all of these rows and can leave some blank, but things like rent, insurance, salaries, wages and benefits could go here. When you are done, link Total Selling Expenses and Total General and Administrative to the Income Statement on the Financial Statements Tab. Step 5: Prepare an opening balance sheet on the Financial Statements tab (there is a video for this “Opening Balance Sheet and Debt Amortization”). This will reflect your capital structure and will drive the amortization table you will create for debt (so you have to have some level of debt). The simplest opening balance sheet is to have some combination of debt and common stock on the liabilities and equity side, and an equal amount in cash on the