Small Business Plan Tool User Manual
Contents
1. Small Business Plan Tool User Manual Prepared by S BizT Team http www smallbizplantool com Powered by MBM Consulting http mbm consulting com Prepared by S BizT team http ww smallbizplantool com Powered by MBM Consulting http mbm consulting com List of Acronyms and Abbreviations S BizT A financial model to obtain financial projections of a company The word S Biz T is exchangeable to financial model or spreadsheet or model DCF Discounted Cash Flow DLOC Discount Lack of Control DLOM Discount Lack of Marketability KD Cost of Debt KE Cost of Equity Prepared by S BizT team http www smallbizplantool com Powered by MBM Consulting http mbm consulting com Introduction 1 1 Overview This users manual is a step by step guidance to use a Small Business Planning Tool or S BizT By following through this users manual user will have a clear idea on how this financial model works and how to plan their business financial projection appropriately 1 2 Purpose of the S BizT S BizT is an MS Excel based tool created to perform financial projection of a business By inputting projection assumptions users can plan on how to run their business well Not all type of businesses can be planned using S Biz as different type of businesses requires different elements of revenue and cost generators as well as its accounting treatment S Biz fits for planning several business2. 149 150 151 152 AVERAGE DAYS ACCRUED EXPENSES OF DAYS Operating Expenses consists of General Administrative Personnel Insurance Transportation and Other Operating Expenses In projecting Operating Expenses user can choose one of the input calculation method options which are Input Method or Gross Margin Method subsequently S BizT will calculate it accordingly During the course press the Base Case button regularly to check your Ending Cash Balance as presented in row 7 A B C G S BizT et comprehensive 196 184 249 161 310 330 Base Case Assumptions Sheet Prepared by S BizT team http www smallbizplantool com Powered by MBM Consulting http mbm consulting com e 5 2 4 Other Income Expense 9 Base Case Assumptions Sheet 171 OTHER INCOME EXPENSE 172 173 OTHER INCOME 174 OTHER EXPENSE 175 OTHER INCOME EXPENSES 176 Put in numbers for Other Income and Other Expense accordingly in the provided cells Ensure that you have calculated the aggregated Other Income and Other Expense beforehand You can always calculate any data you have in spare worksheets before you incorporate itinto the assumptions sheets Add any of the spare worksheets whenever you need it 5 2 5 Tax and Dividends 9 Base Case Assumptions Sheet RRR Reon vg cIGI amp amp AA AGG RMA ATA SSS 17 D TAX E DIVIDENDS Put in numbers for Income Tax Rate Tax Payable Days and Dividend
3. 276 SHORT TERM LOAN DRAWDOWN USS aff SHORT TERM LOAN REPAYMENT USS 278 SHORT TERM LOAN OUTSTANDING USS 279 YEARLY INTEREST RATE To YEAR 280 281 SHORT TERM LOAN 2 282 SHORT TERM LOAN DRAWDOWH USS 283 SHORT TERM LOAN REPAYMENT USS 284 SHORT TERM LOAN OUTSTANDING USS 285 YEARLY INTEREST RATE a WEAR 286 Along with the growing of the company operating activities are increasing as well and the common consequence is the company experiences short of working capital short of cash to operate the company Short Term Loan is where user can plan their working capital loan due to the need for additional working capital Each of Shor Term Loan item is for a single loan facility On the sample above the company already has a single US 17 000 outstanding of Short Term Loan facility from the previous period lf the company has more than one Short Term Loan facility therefore you have to put in the loan amount accordingly They fall under the cells of the SHORT TERM LOAN OUTSTANDING post User can also put in additional loan facilities amounts into the cells under the SHORT TERM LOAN DRAWDOWN post if the company need more fund If you see your company has excess cash therefore you can repay the loan into the cells under the SHORT TERM LOAN REPAYMENT post Put in the INTEREST RATE accordingly During the course press the Base Case button regularly to check your Ending Cash Balance as presented in row 7 AIBIC D E
4. CURRENT EARNINGS CHG IN COMMON STOCK NET WORK INCREASE 26 144 24 073 Boi Bie S ie SR eS eS Prepared by S BizT team http www smallbizplantool com Powered by MBM Consulting http mbm consulting com 5 2 Assumptions Base Case Scenario Assumptions sheet is where user has to put in numbers to project your company s financial statements Assumptions sheet consists of A General assumptions Operating assumptions Other income expense assumptions Tax assumptions Dividends assumptions Other balance sheet items assumption Capital expenditure assumptions Equity owner s capital assumptions Short term loan assumptions Long term loan assumptions c rommoow Details on each item will be explained in section 5 2 2 up to 5 2 10 in this User Manual 5 2 1 Projection Period SSSA LLG SAM GSS LAG AAG GGG HAAG HG AAA GGA S BizT allows user to project their company financial on monthly basis for the first year projection and yearly basis up to 10 years The years of the period depend on the Projection Start Date the user input in the following section 5 2 2 General Assumptions Base Case Assumptions Sheet BERRI COUNTER gg LHWWW WW FWicgyv A GENERAL ASSUMPTIONS PROJECTION START DATE LAST FINANCIAL REPORT DATE CURRENCY NUMBER OF DAYS Put in the Projection Start Date and the type of Currency The financial model will automatically calculate your latest historical financial statements
5. http mbm consulting com e Input all of the assumptions in the provided cells in light brown color coded accordingly e User might need helps from an experienced person in inputting all the assumptions in this Valuation sheet B D E F S BizT S npie yet comiprefenstve BASE CASE Business and Equity Valuations 55 PRESENT VALUE OF FREE CASH FLOWS 236 962 57 TERMINAL VALUE 58 PV OF TERMINAL VALUE AT A PERPETUAL GROWTH RATE 281 038 59 SURPLUS AS OF 31 Dec 12 89 050 50 COMPANY VALUE 608 950 62 NET AS OF 31 Dec 12 37 000 63 EQUITY VALUE 574 950 65 pLoc 114 390 66 EQUITY VALUE AFTER DLOC 457 561 68 DLOM 114 390 69 EQUITY VALUE AFTER DLOM 343 170 e After inputting the assumptions above you will get your overall company s and company s equity values Outputs 6 1 Description The output worksheets in the financial model are the result of calculating all the numbers inputted in the assumptions worksheets 6 1 1 Financial Statements Ppd F E H i J K L M N 1 S BizT Sample yet comprehensive 2 i 3 5 BASE CASE 7 Financial Statements 8 9 10 12 13 INCOME STATEMENT 14 SALES 225 450 250 500 275 550 305 600 305 600 305 600 305 600 15 COST OF GOOD SOLD 157 815 175 350 192 885 213 920 213 920 213 920 213 920 16 DEPRECIATION 12 000 12 000 12 000 429 442 442 454 17 18 GROSS PROFIT 55 635 63 150 70 665 91 251 91 238 91 238 91 226 19 20 OPER
6. Payout Ratio accordingly in the provided cells Prepared by S BizT team http www smallbizplantool com Powered by MBM Consulting http mbm consulting com 5 2 6 Other Balance Sheet Items 9 Base Case Assumptions Sheet 13 PROWLER QGyw WW Wy vy MK MW wy gf 1 F OTHER BALANCE SHEET ITEMS 150 OTHER CURRENT ASSETS 191 OTHER NON CURRENT ASSETS 192 OTHER CURRENT LIABILITIES 193 OTHER NON CURRENT LIABILIMES Since Other Balance Sheet are usually non substantial items therefore projecting them in a certain manner is not common hence they will be projected automatically by the model the same amount as the last position in the previous period 5 2 7 Capital Expenditure Base Case Assumptions Sheet G CAPITAL EXPENDITURE 197 198 EXISTING FIXED ASSETS 199 200 201 202 USEFUL LIFE 703 204 BUILDING 4 INITIAL ACQUISITION COST USS a 205 REMAINING USEFUL LIFE YEARS 206 207 BUILDING 2 INITIAL ACQUISITION COST USS 208 REMAINING USEFUL LIFE YEARS 209 210 BUILDING 3 INITIAL ACQUISITION COST USS 211 REMAINING USEFUL LIFE YEARS 212 213 BUILDING 4 INITIAL ACQUISITION COST USS 214 REMAINING USEFUL LIFE YEARS 215 216 BUILDING 5 INITIAL ACQUISITION COST uss 217 REMAINING USEFUL LIFE YEARS Prepared by S BizT team http www smallbizplantool com Powered by MBM Consulting http mbm consulting com Base Case Assumptions Sheet EOCENE GG GG QQ qq yk EGQQGGGLAAAAAYAAYQ A5
7. date The Currency you input will be used throughout the S BizT Prepared by S BizT team http www smallbizplantool com Powered by MBM Consulting http mbm consulting com 5 2 3 Operating Assumptions e Sales 9 Base Case Assumptions Sheet 10 11 12 13 PERIOD COUNTER 1 2 3 22 23 B OPERATING ASSUMPTIONS 24 a o 27 28 PRODUCT 1 29 PRICE B50 60 30 VOLUME 1000 a 400 31 SALES GROWTH 1000 10 00 1000 32 SALES OF lt PRODUCT 1 gt 300 65 000 65 000 65 000 33 34 AVERAGE DAYS RECEIVABLE OF DAYS ee ee 35 36 PRODUCT 2 lt PRODUCT2 gt 37 PRICE 6 amp 6 38 VOLUME i tee E ra 39 SALES GROWTH HVERR 1000 10 00 10 00 40 SALES OF lt PRODUCT 2 gt S 3005 60 000 60 000 60 000 41 42 AVERAGE DAY S RECEIVABLE OF DA SS Ss a 43 In projecting Sales user can choose the input calculation method options which are Input Method or Growth Method and subsequently S BizT will calculate it accordingly S BizT allows user to plan selling up to 5 products but if user has more than 5 products user can input it under Other Products However user has to ensure that user has calculated it on average basis beforehand User can always calculate any data in spare worksheets before incorporating it into the assumptions sheets Add any of the spare worksheets whenever you need it Ra tio sL owl 35 _RatiosHighCase _ Put in each of your product names in
8. in the spreadsheet and a concise tutorial 3 Dashboard Provides graphical presentation of all of the financial ratios and snapshots of income statement amp balance sheet for the selected year generated by the financial model 4 InputSection gt Separation sheet between opening sheets section and input sheets section 5 HistoricalFS Inout sheet for actual historical data of balance sheet and income statement 6 BaseCaseAssumptions Inout sheet for your operating capital expenditures and financing activities assumptions as your base case scenario 7 HighCaseAssumptions Inout sheet for your operating capital expenditures and financing activities assumptions as your high case scenario 8 LowCaseAssumptions Inout sheet for your operating capital expenditures and financing activities assumptions as your low case scenario Prepared by S BizT team http www smallbizplantool com Powered by MBM Consulting http mbm consulting com No Worksheet Function or Purpose 9 Valuation An input as well as an output sheet for calculating and presenting business or company value and equity value 10 OutputSection gt Separation sheet between input sheets section and output sheets section 11 FS Output sheet for historical and projected income statement balance sheet and cash flow statement 12 Break Even Output sheet for breakeven of Sales calculation result in term of certain currency in USD etc depending on the
9. the adequacy accuracy or completeness of or makes any representation or warranty express or implied with respect to the information contained in this Model or on which this Model is based or any other information or representations supplied or made in connection with this Model or as to the reasonableness of any projections contained in this Model MBM does not provide any assurance that the Model is free from errors to any recipients of the Model The recipients are responsible for conducting their own tests and assessments on the Model to ensure that it is free from errors This Model has been designed so the user can change some of the assumptions and other relevant data inputs in order to consider alternative outcomes The model has limits and may not produce valid results for all possible combinations of input data errors and potential errors may thus go unnoticed MBM is not responsible for inaccuracies failure of the model or calculation errors due to the user s input or inadequacies This Model may include certain forward looking statements estimates and projections of anticipated future performance for illustration purposes only No liability to any person is accepted by MBM and its Affiliates in relation to the distribution of this Model in any jurisdiction The Model is provided by electronic means such as Internet emails or other media devices MBM does not guarantee that the file is free from Computer viruses or other factor
10. user has calculated it on average basis beforehand You can always calculate any data you have in spare worksheets before you incorporate it into the assumptions sheets Add any of the spare worksheets whenever you need it RatiosLowCase e Average Days Payable is your company payables turnover in number of days unit o Average Days Inventory is your company inventories turnover in number of days unit Average Days Accrued Expense is your company accrued expenses turnover in number of days unit During the course press the Base Case button regularly to check your Ending Cash Balance as presented in row 7 AJB C D G S BizT omprehensive 1 2 3 5 BASE CASE 7 o 9 i E 78 382 126 823 196 184 249 161 ENDING CASH BALANCE Base Case Assumptions Sheet Prepared by S BizT team http www smallbizplantool com Powered by MBM Consulting http mbm consulting com e Operating Expenses Base Case Assumptions Sheet 132 133000 GENERAL amp ADMINISTRAT pEr GEA EXPENSE INPUT 135 G amp A EXPENSE GROWTH RATE NEAR 136 GENERAL amp ADMINISTRATIVE EXPENSE uss 137 AVERAGE DAYS ACCRUED EXPENSES 2 OF DAYS 134 140 PERSONNEL 141 PERSONNEL EXPENSE INPUT USS PERSONNEL EXPENSE GROWTH RATE EAR PERSONNEL EXPENSE USS AVERAGE DAYS ACCRUED EXPENSES OF DAYS INSURANCE EXPENSE INPUT USS INSURANCE EXPENSE GROWTH RATE YEAR INSURANCE EXPENSE USS 1 000 142 143 144 145 146 148
11. 86 29 85 oa OPERATING PROFIT a 48 980 55 830 62 612 85 751 85 188 84 583 83 905 OPERATING PROFIT MARGIN E q1 21 73 22 29 22 72 28 06 27 88 27 68 27 46 Prepared by S BizT team http ww smallbizplantool com Powered by MBM Consulting http mbm consulting com Ratios worksheet is an output sheet for a broad range of financial ratios It consists of profitability ratios Management efficiency ratios liquidity amp leverage ratios EBITDA and EBITDA related ratios capital structure ratios cash flow summary and valuation summary At the top side of the worksheet there are DUPONT ratios as a summary of financial ratios calculated beneath Aside from Ratios worksheet there are RatiosBaseCase RatiosHighnCase RatiosLowCase and AllRatios worksheets where each of those sheet presenting financial ratios for each case AllRatios worksheet is where all cases of financial ratios presented Prepared by S BizT team http ww smallbizplantool com Powered by MBM Consulting http mbm consulting com Disclaimer The Financial Model the Model or S BizT has been prepared by MBM Consulting Services MBM This model is not intended to be used in evaluation of any project transactions or investments Acceptance by a Recipient of the Model constitutes its agreement to and acceptance of each of the terms and conditions set forth in this agreement None of MBM nor tts Affiliates accepts liability or responsibility for
12. ATING EXPENSE 6 655 7 321 8 053 5 500 6 050 6 655 7 321 21 22 OPERATING PROFIT 48 980 55 830 62 612 85 751 85 188 84 583 83 905 23 24 INTEREST EXPENSE 3 500 5 775 4 970 213 207 201 195 25 OTHER INCOME EXPENSE 45 45 45 45 45 45 45 26 27 PROFIT BEFORE TAX 52 435 61 560 57 687 85 583 85 026 84 427 83 755 28 29 INCOME TAX 15 934 17 535 20 191 29 954 29 759 29 549 29 314 30 31 NET PROFIT 36 501 44 025 37 497 55 629 55 267 54 878 54 441 32 33 DIVIDEND 10 357 11 398 13 124 19 470 19 343 19 207 19 054 34 35 CURRENT EARNINGS 26 144 32 627 24 373 36 159 35 924 35 670 35 386 Prepared by S BizT team http ww smallbizplantool com Powered by MBM Consulting http mbm consulting com FS worksheet is where you can find Income Statement Balance Sheet and Cash Flow Statements both for historical data and financial projections as the results of calculations of inputted assumptions 6 1 2 Break Even Point ajajejecj pope el a S BizT Simple yet comprehensive 2 a 5 BASE CASE te Break Even Point EJ EJ 10 13 VARIABLE COSTS TOTAL VARIABLE COSTS USS 145 815 163 350 180 885 213 491 213 478 213 478 213 466 16 FIXED COSTS TOTAL FIXED COSTS us 6 655 7 324 8 053 5500 6 050 6 655 7 324 CONTRIBUTION MARGIN SALES us 225 450 250 500 275 550 305 600 305 600 305 600 305 600 VARIABLE COSTS us 145 815 163 350 180 885 213 491 213 478 213 478 213 466 22 CONTRIBU
13. F G H l J K L M N o S BizT u yet comprehensive gt _BASE CASE HIGH CASE LOW CASE BASE CASE ENDING CASH BALANCE 6 5 249 161 Base Case Assumptions Sheet Prepared by S BizT team http ww smallbizplantool com Powered by MBM Consulting http mbm consulting com 5 2 10 Long Term Loan te S e IS 307 310 311 312 313 314 315 316 317 318 219 320 321 Prepared by S BizT team http www smallbizplantool com Base Case Assumptions Sheet ENIAC 0 0 wlEorHrarmd oa QAAGQQQ A AS J LONG TERM LOAN LONG TERM LOAN 1 LONG TERM LOAN 2 LONG TERM LOAN DRAWDOWN USS LONG TERM LOAN REPAYMENT USS LONG TERM LOAN OUTSTANDING USS YEARLY INTEREST RATE Yo WEAR LONG TERM LOAN DRAWDOWN USS LONG TERM LOAN REPAYMENT US LONG TERM LOAN OUTSTANDING US YEARLY INTEREST RATE fo EAR Long Term Loan is where the company borrow money with the purpose of to fund the company s investment in Fixed Assets i e Buildings Machineries and Equipment While land is usually funded by the equity of the company Each of Long Term Loan item is for a single loan facility On the sample above the company already has a single US 20 000 outstanding of Short Term Loan facility from the previous period If the company has more than one Long Term Loan facility therefore you have to put in the loan amount accordingly They fall under the cells of the LONG TERM LOAN OUTSTANDING post Loan to equity ratio is var
14. NEW FIXED ASSETS LAND NEW ACQUISITION uss NEW CAPEX FOR BUILDINGS uss NEW CAPEX FOR MACHINERIES uss NEW CAPEX FOR EQUIPMENTS uss JERR RRR ESES Se sic sie Capital Expenditure consists of Existing Fixed Assets and New Fixed Assets Each of them consists of Buildings Machineries and Equipment For Existing Fixed Assets user has to input the aggregate book value of each type of asset overall economic useful life and its remaining useful life For New Fixed Assets user has to input the purchasing plan of each type of asset During the course press the Base Case button regularly to check your Ending Cash Balance as presented in row 7 G H t comprehensive BASE CASE HIGH CASE Low CASE 196 184 249 161 310 330 Base Case Assumptions Sheet SEE GC CCG CCC Gww e e Tw r 50 r gt te e gt qqq kkk YX ppp rrLLALWLXrnnEG 5 2 8 Equity Owner s Capital Base Case Assumptions Sheet ERODE GC G Gg Ww 50 9 KYL __AIIIAAA_YVM J H EQUITY OWNER S CAPITAL PAID IN CATAL INCREMENTAL uss S ee ed el e Paid in Capital under Equity Owner s Capital section is where if the owner of the company injecting more money into the company from their own pocket Prepared by S BizT team http www smallbizplantool com Powered by MBM Consulting http mbm consulting com 5 2 9 Short Term Loan 9 Base Case Assumptions Sheet y3 L SHORT TERM LOAN 275 SHORT TERM LOAN 41
15. TION MARGIN us 79 635 87 150 94 665 92 109 92 122 92 122 92 134 23 FKEDCOSTS us 6 655 7 324 8 053 5 500 6 050 6 655 7 324 24 INTEREST EXPENSE us 3 500 5775 4 970 43 207 201 195 OTHER EXPENSE us 0 0 o 0 0 0 0 26 TAXES us 15 934 17 535 20 191 29 954 29 759 29 549 29 314 NET INCOME us 53 546 56 520 61 452 56 442 56 105 55 716 55 304 CONTRIBUTION MARGIN 35 32 34 79 34 35 30 14 30 14 30 14 30 15 BREAKEVEN IN SALES AMOUNTS OF MONEY us 73 858 85 042 96 677 118 336 119 479 120 774 422 162 32 33 BreakEven worksheet is an output sheet for amount of breakeven of Sales calculation result in term of certain currency in USD etc depending on the users currency selection in the general assumptions sheet 6 1 3 Financial Ratios EE S BizT Simple yet comprehensive B jocs BASE CASE Financial Ratios ele j 12 13 DUPONT ANALYSIS Pi RETURN ON EQUITY ROE Etn 26 59 27 01 21 32 26 23 22 29 19 35 17 06 Pg RETURN ON SALES ROS d En 16 19 17 57 13 61 18 20 18 08 17 96 17 81 m ASSET TURNOVER ATO kiin 0 91 0 78 1 01 0 80 0 68 0 61 0 54 ASSET LEVERAGE ALEV ee 1 80 1 98 155 1 80 1 82 177 176 a8 a PROFITABILITY m SALES p 225 450 250 500 275 550 305 600 305 600 305 600 305 600 A SALES GROWTH is di m NA 11 11 10 00 10 91 0 00 0 00 0 00 i GROSS PROFIT _ _ _ ill 55 635 63 150 70 665 91 251 91 238 91 238 91 226 Bg GROSS PROFIT MARGIN 24 68 25 21 25 65 29 86 29 86 29
16. n the business nature of your particular company The way to input their assumptions is the same with the way with how to put in assumptions in Base Case scenario except the GENERAL ASSUMPTIONS part where they are already linked to the base case scenario assumptions sheet During the course press the High Case or Low Case buttons regularly to check your Ending Cash Balance as presented in row 7 AIBIC DI E F G H l J K L M N o S BizT Simple vensive BASE CASE CLICK I ENDING CASH BALANCE z 249 161 Base Case Assumptions Sheet 5 4 Valuation BC DE F BizT Sanple yet comprehensive BASE CASE Business and Equity Valuations J TARGET CAPITAL STRUCTURE 16 DEBT TO TOTAL CAPITALIZATION 17 EQUITY TO TOTAL CAPITALIZATION fy COST OF DEBT KD 20 COST OF DEBT BEFORE TAX 21 MARGINAL TAX RATE 22 KD AFTER TAX 23 COST OF EQUITY KE 25 RISK FREE RATE 26 MARKET RISK PREMIUM 27 BETA TI COST OF EQUITY 30 WACC 3 8 7 5 PERPETUITY GROWTH RATE 36 DISCOUNTS FOR LACK OF CONTROL DLOC 37 DISCOUNTS FOR LACK OF MARKETABILITY DLOM e Investors and banks usually need to know how much your business is worth therefore S BizT provides a valuation calculation sheet The Valuation sheet is where you can calculate your business company and company s equity value using the DCF method Prepared by S BizT team http ww smallbizplantool com Powered by MBM Consulting
17. ng http mbm consulting com For ease of use access these worksheets are light brown color coded tabs in which input assumptions among others relating to historical data and assumptions are entered and indicated in light brown color coded cells In order to maintaining the model integrity mathematically correctness as well as user friendliness of the spreadsheet only cells with light brown color coded are accessible All assumptions are inputted via the Input Worksheets Calculation Worksheets calculations are made on the inputted assumptions in order to derive an output Calculation Worksheets are used to avoid complex nested functions and macros The calculations worksheets are hidden to maintaining the model integrity and to avoid any mathematical error within the financial model Output Worksheets outputs of the financial model based on the calculations made on the input assumptions are presented here These worksheets tab are highlighted in dark grey which provides full financial statements income statement balance sheet and cash flow statement break even calculation result and financial ratios of the company 3 3 Worksheet List A list of worksheets and their functions is tabulated below in the sequence in which they are currently found in the financial model No Worksheet Function or Purpose 1 Opening Provides information such as Disclaimer Agreements 2 Menu Provides summary contents for all sheets contain
18. s which may affect the integrity of the electronic file or the system which receives it In this agreement Affiliates means in relation to any person its holding companies companies under the control of such holding companies and subsidiaries and its respective directors officers employees and agents or any other person that is controlled by controls or is under common control with any of the foregoing persons For the avoidance of doubt by opening this file the user is by default unconditionally accepting all of the above terms Prepared by S BizT team http ww smallbizplantool com Powered by MBM Consulting http mbm consulting com
19. the provided cells The provided numbers in the model are for illustration only user has to remove the unnecessary numbers and put in the assumption numbers for each post PRICE VOLUME SLAES GROWTH and AVERAGE DAYS RECEIVABLE accordingly During the course press the Base Case button regularly to check your Ending Cash Balance as presented in row 7 H 4JA B C DIE F S BizT Simple yet compret BASE CASE re a ENDING CASH BALANCE Base Case Assumptions Sheet 249 161 Prepared by S BizT team http www smallbizplantool com Powered by MBM Consulting http mbm consulting com Cost of Goods Sold COGS amp Inventory 9 Base Case Assumptions Sheet COST OF GOODS SOLD COGS amp INVENTORY OPTION PRODUCT 4 GROSS MARGIN METHOO COST OF GOOD SOLO S 20 000 20 000 20 000 20 000 20 000 GROSS MARGN 80 O 3 O 30 COST OF GOOD SOLD OF lt PRODUCT 1 gt USS 45 500 45 500 45 500 45 500 45 500 AVERAGE DAYS PAYABLE 3 SS as a1 eee re 7 AVERAGE DAY S INVENTORY z DSS SE LA Sse Ew I ponmMinT DON iT Ps In projecting COGS user can choose one of the input calculation method options which are Input Method or Gross Margin Method subsequently S BizT will calculate it accordingly S BizT allows user to plan COGS up to 5 products as well as Other Products related to the Sales assumptions user has input beforehand For the Other Products user has to ensure that
20. types as follows Manufacturing Retail store Trading S BitzT Financial Modeling Best Practices 2 1 Requirements under S BizT Best Practices for Financial Modelling S BizT is developed based on MS Excel 2010 employing best practice standards to the extent possible given the complexity of the transaction and the various input assumptions Among others in compliance with S BizT best practices for financial modeling the following steps were taken e Separation of worksheets containing input worksheets for historical data and assumptions calculation worksheets and output worksheets e Complex nested functions are avoided and minimization of use of macros e Reduction eradication of circular calculation thus avoiding circular reference problems e Password protected to ensure security of data e Logical flow from top to bottom left to right within worksheets and across workbook Financial Model Structure amp Worksheets 3 1 User s Manual Approach In order for the user to easy in understanding how the model works S BizI team has pre populated the assumptions worksheets with dummy numbers The company prowded is an imaginary company 3 2 Worksheet Classification In general each worksheet has been categorized as follows Input Worksheets input assumptions entered on these worksheets represent inputs to the financial model Prepared by S BizT team http www smallbizplantool com Powered by MBM Consulti
21. users currency selection in the general assumptions sheet 13 Ratios Output sheet for a broad range of financial ratios 14 RatiosBaseCase Output sheet for a broad range of base case financial ratios results from your base case assumptions input 15 RatiosHighCase Output sheet for a broad range of high case financial ratios results from your high case assumptions input 16 RatiosLowCase Output sheet for a broad range of low case financial ratios results from your low case assumptions input 17 AllRatios Output sheet for a broad range of all cases financial ratios that would assist you in comparing the results of ratios from all cases Prepared by S BizT team http www smallbizplantool com Powered by MBM Consulting http mbm consulting com Opening 4 1 Menu amp Tutorial This worksheet provides summary contents for all sheets contain in the spreadsheet and a concise tutorial on how to use S BizT gt ANO NG Menu amp Tutoria CAECUBATIONS OK NY LELY Ant ok N Follow the 5 simple steps below Press the Base Case button regularly to check the cash position onrow7 4 2 Dashboard This worksheet provides graphical presentation of all of the financial ratios and snapshots of income statement amp balance sheet generated by the financial model DASHBOARD REPORT SEEEEGEES 8 IK B Ji BE hed ii NNN Wu a i i B Hetiete ilde lt i 18 B SEEEESES Prepared by S BizT team http
22. www smallbizplantool com Powered by MBM Consulting http mbm consulting com Using S BizT 5 1 Historical Data The only sheets user can put in numbers are sheets with light brown color coded tabs HistFS BaseCaseAssumptions HighCaseAssumptions LowCaseAssumptions and Valuation User can only put in numbers in the light brown color coded cells This is to maintain a user friendliness of the model The provided numbers in the model are for illustration only user has to remove the unnecessary numbers and put in user s historical financial statements numbers accordingly What user needs to do are 1 Enter the company business name at the provided cell as pointed by the arrow at the picture bellow 2 Continue with entering the business historical financial statements User only can input this historical financial statements cells if user has historical financial statements If that is not the case then just leave all the light brown color coded cells empty faAlB c D E F G I S BiZT SGRELE yer comprehensive Small Bisiness Planning Tool en da bi CALCULATIONS EL istorical Financial ents af WO on 11 INCOME STATEMENT 12 REVENUE 13 COST OF GOOD SOLD 14 DEPRECIATION J 15 16 GROSS PROFIT 55 150 17 18 OPERATING EXPENSE 19 OPERATING PROFIT INTEREST EXPENSE OTHER INCOME EXPENSE PROFIT BEFORE TAX 52 61 57 INCOME TAX NET PROFIT 36 504 44 025 37 497 DIVIDEND PAID
23. y from 70 30 80 10 or other ratios depending on the company negotiation with the bank User can also put in additional loan facilities amounts into the cells under the LONG TERM LOAN DRAWDOWN post if you need more fund Put in the INTEREST RATE accordingly Long Term Loan is reduced according to loan repayment schedule based on the company s cash availability for debt services During the course press the Base Case button regularly to check your Ending Cash Balance as presented in row 7 A B C Bele G i J K L M N o S BizT et comprehensive P Bse ease menot Low erse BASE CASE ENDING CASH BALANCE 126 823 196 184 Base Case Assumptions Sheet NA RY SAAS NA A VAARAN N A N AAAA CO AAAA RRR RR RRR ROR NN 3 x AAAA SSSA Ss 7FC SSS wou PQ vw SST NR Qe SQ s Sy SX SS SN SS A ANN A M N N SNR NS SAN A No SSS SN Se 3 PERIOD COUNTER Scgo ee 3 SV qaqqo DQ Qw7q FFT oT TRTeRTNT XG NS SG XG SS MS W WY SS 3 Powered by MBM Consulting http mbm consulting com 5 3 Assumptions High Case amp Low Case Scenarios F4 Jepni mpn i m md mrp jf pm a Jah sacs Ae eS nitir pm a Len cS oo r m fs bm ji im ji coe mull A gmi Mm fl iii Meas tes i oa mf im im d er a Smi a al an ii mea n b ia pi J s APSE S l MA i e a pi a d GATY DP r a F a a High Case Assumptions and Low Case Assumptions are other assumptions input for different scenarios depending o
Download Pdf Manuals
Related Search