Excel File Edit View Insert Format Tools Data Window Help C Q 8 @ Thu Dec 19 7:05 PM AutoSave AH ? CE .. Copy of 2-Company Financial Model_24F - Saved Q Search (Cmd + Ctri + U) Ad Hominem X Do Paraphrasing X Home Insert Draw Page Layout Formulas Data Review View Automate Comments Share ctx=0%... D Relaunch to update : Default Page Break Preview Zoom 100% E I Page Layout Normal Show Zoom to Window Macros Custom Views 100 Zoom to 100% Selection E42 X V fx A B C E F G H J K L M N O 14 Income Statement 15 Revenues S 192 $ 200 $ 209 $ 218 Growing based on Revenues Growth Rate 16 Cost of Revenues S 132 $ 138 144 $ 150 Revenues minus Gross Profit Gross Profit 62 68 Revenues multiplied by Gross Margin R&D 11.00 11 $ 12 Research & Development - - R&D% multiplied by Revenues SG&A Including Depreciation 33 $ 34 36 Selling, General & Administrative - - SG&A% multiplied by Revenues EBIT 18 5 19 5 Earnings Before Interest & Taxes - - Gross Profit minus R&D minus SG&A Net Interest Income 0 Net Cash (Cash - Debt) prior year multiplied by Interest Rate Pretax Profit 18.82 EBIT plus Net Interest Income Taxes Pretax Profit multiplied by Tax Rate Net Profit 12 Pretax Profit minus Taxes 25 26 Balance Sheet Cash 10 5 12 Cash this year plus Change in Cash Accounts Receivable 20 $ 21 $ 22 $ 23 Grow based on Revenues (constant Receivable Turns) PP&E 51 5 59 Property, Plant & Equipment 30 Accounts Payable 14.0 $ 14 $ 14 $ 15 Grow based on Cost of Revenues (constant Payables Turns) Debt Equity 5 52 5 64 Prior Year Equity plus Current Year Net Profit minus Current Year Dividends 34 Cash Flow Statement 35 Net Profit 12 From Income Statement 36 Depreciation 21 Prior Year PP&E multiplied by Depreciation Rate Change NTWC ( 1 ) $ (0) S (0) Change Net Trade Working Capital (sum of Row 38 and Row 39) Decr(Incr) in Accounts Receivable (1) $ 1) $ (1) Prior Year Accounts Receivable minus Current Year Accounts Receivable Incr(Decr) in Accounts Payable (0) S 1 $ Current Year Accounts Payable minus Prior Year Accounts Payable CapEx $ (11) $ (11) $ [12) Capital Expenditure - - Revenues multiplied by CapEx% - - this should be a negative value or zero Dividends S Net Profit multiplied by Payout Ratio - - this should be a negative value or zero 42 Change in Cash 2 Sum of all of the above items (do not double count Change NTWC) 43 44 Return on Equity (ROE) 23% Current year Net Profit divided by prior year Equity 45 46 Develop Model MODEL Income Statement MODEL Balance Sheet MODEL Cash Flow Statement A MODEL Template + Ready & Accessibility: Investigate Microsoft Excel 100% 19 "tv 4 WExcel File Edit View Insert Format Tools Data Window Help C Q 8 @ Thu Dec 19 7:05 PM AutoSave AH ? " CE .. Copy of 2-Company Financial Model_24F - Saved Q Search (Cmd + Ctri + U) Ad Hominem X Do Paraphrasing X Home Insert Draw Page Layout Formulas Data Review View Automate Comments Share ctx=0%... D Relaunch to update : Default Page Break Preview Zoom 100% ED I Page Layout Normal Show 100 Zoom to 100% Zoom to Window Macros Custom Views Selection F73 X V fx =INDEX(H73:M73, 1,$K$34) B D F G H K M N O The model should include: A column of end of 2022 Balance Sheet actuals A column in which you will calculate the 2023 actuals based on the information provided To avoid 'Circular' logic in a financial model, current period (year) values on the Income Statement and Balance Sheet need to reference the end-of-year Balance Sheet values of the previous year. A column for 2024 forecasts A column for 2025 forecasts eate formulas in your financial statement that pull the below values in the grey cells in for 2022 and 2023. Cell F56 you will select which of the scenarios in Column H through L to select when completing the 'Analyze Using Model' Sheet. When developing the model utilize the "Baseline' scenario. 50 our model should link (by formulas) to the above data rather than copying them into another sheet. 51 ou will have to create the logical plumbing (formulas) such that when the below changes the logic will work. 52 ou can assume that: Accounts Receivable Turns remain constant (based on prior year balance sheet divided by current year Revenues). 54 Accounts Payable Turns remain constant (based on prior year balance sheet divided by current year Cost of Revenues). 55 Cash Cow w/ 56 Baseline Baseline Cash Cow Star Dog Leverage Dividends Definition 57 In the grey cells below are Balance Sheet values at the end of 2022: Cas $10.4 $10.4 $10.4 $10.4 $10.4 $10.4 $10.4 Accounts Receivable $20.0 $20.0 $20.0 $20.0 $20.0 $20.0 $20.0 PP&E $50.6 $50.6 $50.6 $50.6 $50.6 $50.6 Property, Plant & Equip Accounts Payable $14.0 $14.0 $14.0 $14.0 $14.0 $14.0 $14.0 Debt SO.0 $0.0 SO.0 $0.0 SO.0 $29.0 $0.0 Equity $52.2 $52.2 $52.2 $52.2 $52.2 $27.2 $52.2 65 In the grey cells below are Income Statement Ratios for 2023: Ratio Drive Values Revenues Growth Rate Revenues 4.3% 4.3% -2.3% 13.3% 2.3% 4.3% -2.3% Gross Margin Revenues 31.2% 31.2% 36.2% 36.2% 22.2% 31.2% 6.2% Gross Profit / Revenue R&D Revenues 5.5% 5.5% 2.5% 5.5% 3.5% 5.5% 2.5% Research & Developme SG&A % Including Depreciation Revenues 16.5% 16.5% 14.5% 16.5% 19.5% 16.5% 14.5% Selling, General & Adm Tax Rate Pretax Income 37.0% 37.0% 37.0% 37.0% 37.0% 37.0% 37.0% Taxes / Pretax Profit Payout Ratio Net Profit 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 100.0% CapEx % Revenues 5.4% 5.4% 3.4% 9.4% 5.4% 5.4% 3.4% Capital Expenditures In the grey cells below are Balance Sheet Ratios of 2023 Income Accounts and end of 2022 Balances. You can assume these ratios continue. Ratio Driver Values Depreciation Rate PP&E 4.2% 4.2% 4.2% 4.2% 4.2% 4.2% 4.2% Depreciation / PP&E Interest Rate Net Cash (Cash - Debt) 4.0% 4.0% 4.0% 4.0% 4.0% 4.0% 4.0% Net Interest (Interest E 80 Develop Model MODEL Income Statement MODEL Balance Sheet MODEL Cash Flow Statement A MODEL Template Ready Accessibility: Investigate 100% 19 "tv 4