Pro Forma Analysis: For each of the six performance criteria: SALES, NET INCOME, ROA, ROE, and STOCK PRICE state a specific goals/objectives, and compare your proforma results in your financial statements with the Goals/Objectives:
This tests whether your plan works (i.e., whether it achieves your objectives). For example: if your objective was to achieve Net Income of $5,000,000 in Year 5 and $8,000,000 in Year 6, compare the Net income from the pro forma with these two objectives. State whether your objectives are met by your plan.
Although Stock Price cannot be measured directly from your pro forma statements, it can be estimated indirectly. Calculate your historical Price-to-Earnings Ratio (i.e., P/E Ratio) and use that ratio with your proforma earnings to estimate your future stock price.
***I am attaching Pro Forma Finacial Statements and Actuals (Historical) Financial Statements.
Ignore Actual Y5Q1 use proforma. Forcast is for Y5Q1-Y6Q4
Instructions Data Entry Instructions Data may be entered in any shaded cell. The tan cells are linked to the forecast and prodplan workbooks and thus the data in them are taken directly from the workbooks. If you enter data in those workbooks first, you will not have to enter data in the tan cells. If you enter data in the tan cells, that link will be broken and you will have to enter data in the tan cells each quarter. Data must be entered in the blue cell. Worksheet Instructions There are five worksheets of linked financial statements in this file. 1. Consolidated Year (Move between worksheets by clicking) 2. Current Quarter (on worksheet tabs below) 3. 2nd Quarter (Enter data in shaded areas after) 4. 3rd Quarter (reading instructions at the ) 5. 4th Quarter (bottom of this sheet ) Each worksheet contains the following statements 1. Pro Forma Income Statement 2. Pro Forma Cash Flow Statement 3. Pro Forma Balance Sheet Fill in each quarterly statements by entering data in the colored cells. The consolidated year will be calculated automatically. For each quarter: 1. Complete the Pro Forma Income Statement first. 2. Next complete the Pro Forma Cash Flow Statement. 3. Finally complete the Pro Forma Balance Sheet. See Chapter 10 of the Player's Manual for detailed instructions for completing the financial statements. But first entered the data needed below. Data Which Need To Be Entered Only Once 1. Enter your World and Company numbers. World:28 Company:2 2. Your Home Area on each quarterly statement is already labeled. Label the other market areas. Home (Enter 3 area labels) Market areas >>> AreaMerica 1Merica 3Pandau 3. Accounts receivable collection periods vary by market area. % of current sales collected this qtr Revise as necessary50%50%50%40% Data Which Need To Be Entered Each Quarter 1. Enter Year and Quarter designations Cur Qtr2nd Qtr3rd Qtr4th Qtr Year:5555 Quarter:1234 2. Exchange rates vary by quarter. Revise as necessary. Exchange rates for qtr. 11.001.001.007.76 Exchange rates for qtr. 21.001.001.007.58 Exchange rates for qtr. 31.001.001.007.61 Exchange rates for qtr. 41.001.001.007.65 Now enter data into the worksheets. After The Simulation Has Been Run When you are ready to enter new data after the simulation has been run, shift the data previously entered one quarter to the left by clicking the "Move All" button below. Or shift quarters individually beginning with "Move 2 to 1." This will move data from quarter 2 to 1, 3 to 2 and 4 to 3. Then enter new data into the quarter 4 worksheet and make any needed adjustments to quarters 1, 2 & 3. Macros To Shift Quarters One Worksheet To The Left. Move data from quarter 2 to quarter 1 worksheet: Move data from quarter 3 to quarter 2 worksheet: Move data from quarter 4 to quarter 3 worksheet: Move data from all worksheets down one worksheet: The changes will not be permanent until the workbook has been saved. Move 2 to 1 Qtr 2 > Qtr 1 Move 3 to 2 Move 4 to 3 Move all Year Please read the Instructions work sheet first. THE BUSINESS POLICY GAME CONSOLIDATED PRO FORMA INCOME STATEMENT (000s) World:28Year:5555 Company:2Quarter:1234 ======= Net Sales: to Customers5420059548096 to Affiliates (cost + 20%)---- to Liquidators (at cost)0000 Total Sales5420059548096 ======= Beginning Inventory1049177617561803 Goods Manufactured2886237823782378 Purchases from Affiliates---- ------- Goods Available3935415441354182 ------- Ending Inventory1776175618031138 ------- Cost of Goods Sold2160239823313044 Less Value Added Tax105132121175 ======= Gross Profit3154-253035014877 ======= Advertising Expense241251264277 Sales Salaries161164661169 Sales Commissions162186175233 General Selling Expense372372298371 Transportation Expense222209194256 Sales Office Depreciation12111111 ------- Total Selling Expense1169119316041317 ======= Research & Development20202020 Total Training Expense50505050 Storage Expense44558318610 Executive Compensation183183183183 Other Expense01525252 ------- Total Admin. and Gen. Exp.297963623915 ======= Total Operating Expense1466215622272232 Operating Profit (Loss)1688-468612742645 ------- CD Interest0025 Capital Gain (Loss)0000 ------- Loan Interest0000 Bond Interest3803838 ------- Net Profit (Loss) Before Tax1650-468612382612 ======= Less Income Tax597392416943 ------- Net Profit (Loss) After Tax1053-50788231669 Foreign Currency Adjustment8--- Comprehensive Income (Loss)1061-50708311677 ======= Dividends from Subsidiaries---- Dividends to Parent---- Dividends to Shareholders5204183 ------- Added to Accumulated Earnings1001-50787821586 ======= < end="" of="" pro="" forma="" income="" statement="">>>>>>>>>>>>>>>>> THE BUSINESS POLICY GAME CONSOLIDATED PRO FORMA CASH FLOW WORK SHEET (000s) World:28 Company:2 Year:5555 Operating Receipts:Quarter:1234 ======= Collect from Last Qtr Sales3314281532853098 Collected from Current Sales2604302128563873 CD Interest0025 Net Sales to Affiliates---- Sales to Liquidators0000 Subsidiary Dividends Rec'd---- ------- Total Receipts5919583761426977 ======= Operating Expenditures:---- Interest Paid3803838 Production Cost2837253823702370 Purchases from Affiliates---- Operating Expense1513190421672181 Taxes Paid644703523537 ======= Net Operating Cash Flow92569110821888 ======= Investment Receipts:---- CDs Matured00100100 Fixed Assets Sold0000 Investment Expenditures:---- CDs Purchased0100200200 New Equipment0000 Sales Office Investment0000 Plant Investment0000 Subsidiary Stock Purchased---- ======= Net Investment Cash Flow0-100-100-100 ======= Financing Receipts:---- Loans from Bank0000 Stock Sold to Parent---- Bond Sale0000 Stock Sale0000 Financing Expenditures:---- Dividends Paid5204183 Dividends to Parent---- Bank Loans Repaid0000 Bonds Repurchased0000 Stock Repurchased0000 ======= Net Financing Cash Flow-520-41-83 ======= Beginning Cash Balance3155397645665468 Net Cash Flow for Quarter8215909021672 Cash Balance End of Quarter3976456654687140 Required Loan (+ interest)0000 ======= < end="" of="" cash="" flow="" work="" sheet="">>>>>>>>>>>>>>>>>>>> THE BUSINESS POLICY GAME CONSOLIDATED PRO FORMA BALANCE SHEET (000s) World:28 Company:2 Year:5555 ASSETS:Quarter:1234 ======= Cash Balance3976456654687140 Time Certificates of Deposit0100200200 Accounts Receivable2815328530984223 Inventory1776175618031138 ------- Total Current Assets856797071057012701 ======= Net Sales Office1319130913001291 Net Manufacturing Plant4493446344344405 Net Manufacturing Equipment2788266725432425 Equity in Subsidiaries---- Other Investments0000 ------- Total Fixed Assets8599844082778121 ======= Total Assets17167181471884720822 ======= LIABILITIES AND EQUITY: ======= Accounts Payable921708731733 Bank Loans0000 Taxes Payable7035235371118 ------- Total Current Liabilities1624123112681851 ======= Bonds Outstanding1500150015001500 ------- Total Liabilities3124273127683351 ======= Capital Stock9500950095009500 Accumulated Earnings7421000 Accumulated Foreign Cur. Adjust-2765000 ------- Total Equity14156950095009500 ======= Total Liabilities and Equity17280122311226812851 ======= Current Quarter First quarterly work sheet belowUsing m1 as home area M1 sale priceM1 unit salesM1 sales revenue THE BUSINESS POLICY GAME9.81031009.4 PRO FORMA INCOME STATEMENT (000s)--Quarter1unit sales to affiliatesupcsales revenue to affilates World:282074.791190 Company:2Consoli-HomeM1 units producedgoods manufacturedexchange rate Year:5 dated AreaMerica 1Merica 3Pandau31214946 ========M2 purchase orderM3 purchase orderSereno purhcase oder Net Sales: to Customers54201773 Administrator: Administrator: sale price per unit x expected unit sales 9.8x103,000116714258184696969 to Affiliates (cost + 20%)-1663 Administrator: Administrator: Unit production cost x unit sales to affiliates x (1+20% markup); 4.79x207,000x1.2000M1 ending inventory unitsm2 ending inventorym3 ending inventory to Liquidators (at cost)00 Jean Loo: Jean Loo: See pages 178 and 179.000601515 Total Sales54203436116714258184m1 ending inventory costm2 ending inventory costm3 inventory cost ========2878686 Beginning Inventory1049166 Administrator: Administrator: Last quarter's ending inventory from balance sheet report c. 3573921040 Goods Manufactured28862538 Administrator: Administrator: unit production cost x units produced; 4.79X312002704advertising y2q4assumed change Purchases from Affiliates-0 Jean Loo: Jean Loo: See page 180, 7. 738 Jean Loo: Jean Loo: unit purchase order x upc x1.29250465% --------salary/person y2q4number of sales peopleassumed inflation rate Goods Available393527041095131737443123% --------commision/unit y2q4assumed changeunits sold Ending Inventory1776586 Administrator: Administrator: First in, first out; see page 182. See page 130, figure 7.1.46358610920.25%103 -------- Cost of Goods Sold216021186327312652general selling expenses/persongenral selling expense per unit soldfixed general selling Less Value Added Tax105---81840.237.5 ======== Gross Profit315413185356944714transportation cost to customer ========0.1 Advertising Expense241747474147 Sales Salaries161534441176cost of sales officestraight line deprecition for 31.5 yearsfactor per quarter Sales Commissions1627136448940031.50.0079365079 General Selling Expense3721069490633 Transportation Expense22216 Jean Loo: Jean Loo: See page 185. 7389338R&D y2q4assumed change Sales Office Depreciation1233319723% --------training expense y2q4assumed change Total Selling Expense11693233243411402683% ========storage cost per unitnumber of units Research & Development2020---0.129 Total Training Expense5050000 Storage Expense443 Jean Loo: Jean Loo: See page 186.182024exe compensation each plantexe each sales office Executive Compensation18375 Jean Loo: Jean Loo: See page 186. 25254505025 Other Expense00 Jean Loo: Jean Loo: See page 186.000 -------- Total Admin. and Gen. Exp.2971484345474 ======== Total Operating Expense14664713673861876 Operating Profit (Loss)16888471683082838 --------bond outstanding end of y2q4interest rate per year CD Interest00 Jean Loo: Jean Loo: See page 187.---200010% Capital Gain (Loss)00 Jean Loo: Jean Loo: See page 187.000 --------consolidated net profit before taxtax rate Loan Interest00 Jean Loo: Jean Loo: See page 188. ---0.39 Bond Interest3838--- -------- Net Profit (Loss) Before Tax16508091683082838 ======== Less Income Tax597269 Administrator: Administrator: 440x.39 -22-22-240/6 66 Administrator: Administrator: See page 185. 39% tax rate 1201107 -------- Net Profit (Loss) After Tax10535401021881731dividend from overseas subsidiary to parent Foreign Currency Adjustment8 Jean Loo: Jean Loo: See page 190.----1385 Comprehensive Income (Loss)10615401021881731 ======== Dividends from Subsidiaries-535 Jean Loo: Jean Loo: See page 198.--- Dividends to Parent--1021881903 Dividends to Shareholders5252 Jean Loo: Jean Loo: See page 198.--- -------- Added to Accumulated Earnings100110230-0-172dividend to