Industry sign banking arizona business plan template now
[Music] hello in this video I'm gonna walk you through the financial model exam template I will show you the main inputs the core outputs reports and charts so let's get started the dashboard tab you can input the core inputs or drivers for your model you can see also the chord charts and called financials based on these assumptions so let's start from launch date this means that this is a date when your business will launch let's pretend to be April you have also run pop manses during this period your business you go and fail it but for example four months is this means that for the first months it will be 25 percent for the second months it will be 50 percent then 75 percent and starting from month number four and going forward it will be 100 percent of your total capacity under core inputs you can input daily traffic by weekdays so for example starting from Monday Friday you have to conduct leads and Saturday and Sunday for example five hundreds and you can input it for the first year which is 20 20 and going forward you can set up some grows or this traffic for example in 2100 15% 10% then 5% and 3% below you me see the calculation of traffic year-on-year multiple based on these growth rates and you may see the calculation of total leads based on this daily traffic assumptions and growth rates and this is broken down by manses the out step based on the count of mondays tuesdays vendors etc for each month and based on the assumptions which we may see on the dashboard next you can input customer and purchases assumptions which is conversion rate from leads to new customers by years let's pretend for the first year will be four percent five six seven eight percent also if your customer tend to do repeated sales you may input percentage of customer that will do repeated sales can be also inputted by years for example to call three three have four percent also you can input counter purchases per month reputed customer for example two two two then three and four and based on these assumptions we see the counter to chases by years in total also you are able to setup the seasonality of your sales for example if in January you have 20% seasonality this means that your average annual sale will be imparted by one hydrogen 20% if in February it will be minus 10% then you will multiply your average monthly sale across the year or or 90 percent if you don't need any seasonality you can just zero out this set of assumptions and you will not have any assumptions for the seasonality within the model on the product step you can input for the categories names like 1 1 1 2 2 2 etc and your name you need and you can set up the sales mix by products for example if you will input something wrong k2 percent your product number 5 which is 100% minus sample breakdown of Rios for products you'll be minus 40 percent you will see the red color this means input something wrong if you set the breakdown correctly and some of your first four categories it's less than 100 percent and your product key will be everything else until they're 100 percent the total always should be cutted percent and if you input everything correct this will be a gray color below you can set up the price for your product in dollars and my product categories by years the percentage of revenue after you input all these assumptions you may see the revenue breakdown by products and you can also review the revenue on the profitability chart and other core financials you have revenue as a total amount per years additionally you can input safety stock under inventory category on the dashboard as per 10 to 2 to 25 percent and based on this assumption you have the calculation of inventory under your badge the first part of reporting section consists of income statement cash flow balance sheet and the summary tab let's start from income statement so on the income statement you may find total revenue broken down by products broken down by products gross margin variable expenses salaries and wages fix-it expenses IBGE depreciation and amortization a bit interest expense net profit before tax tax expense and as a summary net profit after tax on the cash flow statement you may find cash flow from operating expenses to slope from racing expenses and cash flow from financing expenses broken down by its subcategories on the cash flow indirect you may find more collapse it view of cash loan statement also written down by operating activities investment activities and financing activities and as a total you may see the net increase in cash or cash movement on the balance sheet you may find current assets non current assets current liabilities long-term or non current liabilities and your equity the summary of statements you may find on the summary tab here you may see the income statement broken down by years by my in categories and to the right you may see the totality our breakdown by masses for example I can select 2021 and we will see all the P&L line items broken down by manses below you may see the charts with presentation of mine KP is related to the income statement the same idea for the balance sheet you may see more important keep eyes on the charts and on the line items breakdown and the same idea for the cash flow statement [Music] at the top of financial chart step you may see the revenue breakdown broken down by products and this information is presented for the two years by manses and for the five years by manses below you may see the chart for the cash balance operating cash flow broken down by cash inflow and cash outflow breakdown of EBT which is revenue Cox topics and the resulting EBT you may see as yellow line and also Abbott also broken down by manses for two years and for the five years on the operational charts you may see the sales mix which is count of salt units byproducts and productivity charts just average revenue per day and average topics per day working down by two years and five years by masses also here you may see the form force productivity which is revenue per employee and of experiment [Music] on the benchmarks KPIs you may find your mind industry-specific keep eyes which are changeable for your industry for your country for example gross margin industry benchmark sixty percent for your country you may see this change it on this chart industry gross margin is orange and blue this is gross margin calculated based on model the same way you may change profit margin for twenty percent and we see it on the chart below for the two first years you may find that your profit margin is less then industry benchmark and starting from year 2022 you have bigger amounts than industry benchmark also you have wages as a percentage of revenue average weekly revenue and average weekly net profit which marks [Music] on the top revenue tab you may see the breakdown of your revenue by products and also by years with absolute values and percentage breakdown the same information you may see on the charts below here we see the percentage breakdown and absolute values breakdown below you may seize the revenue depth and multi-run rate chart you can select the year and based on this year you will see the information revenue by products as absolute values and percentage revenue breakdown on the pie chart on the revenue bridge you may find the main revenue drivers of growth you may say that the first year and you may select the last year and between these years you will see the waterfall chart and you may see which are the main drivers of your revenue grows which specific products grows faster and which specific products grows slower [Music] on the top expenses tab you may find the breakdown of top for expenses categories and all other expenses collapse into other category here we see the breakdown of absolute values broken down by years with the total below and also to the right you may see the percentage breakdown of these expenses the same information you may see on the charts below which you may find the percentage breakdown and the absolute values breakdown and a couple of charts below when we find expenses depth and monthly run rate for selected TR you are able to change this here and when we see the absolute values and percentage breakdown on the pie chart and the expenses breach you will find the main drivers expenses gross between these two years this year's us are also changeable so you may select the first year and here we sell at the last year and you may see that total expenses starting 2020 will change to total expenses in 2024 by this waterfall [Music] on the breakeven tab you will find the calculation of revenue break-even level and break-even chart for this particular particular use case and we find that your revenue break-even level is less than actual revenue calculation this means that company is profitable [Music] a devaluation tab you may see the calculation of company valuation based on the cost of equity which may input here cost of loans you previously inputted in on the dashboard calculation of resource share you may see here there is also tax rate and here we find the weighted average cost of capital in the valuation model there is two valuation methods which is a BGA multiple and revenue multiple you must select one of them and below you may input multiple of methods based in this information we can see terminal value which is calculated on an elaborate free cash flow you may see the present value of one leverage free cash flow and PV and multiplicator valuation for this particular company [Music] the color coding in the model is very simple you may change any yellow cell in any yellow sheet within the model this means that this yellow cell has some input assumption or driver which imparts the calculation within the model blue sheets means that on these sheets there are some charts reports and other information which can be useful for reporting purposes on the tabs without color you have some extra calculations related to revenue to depth equity inventory a narration which is needed for the report reporting additionally you have contents tab which allow you to navigate across the model very simple so you may click on any report and you can go back it is broken down by reports assumptions statements and setup there is short explanation about what each tab does but if you want to know more you can go to how-to and to see more detail decks detailed explanation of what each dot does and what inputs you will find on this sheet and what kind of outputs and we find one condition as well any header of this section is also clickable so you may click on for example Boca sets and you go directly to the step [Music] on the register you can input your head column by categories this fire Empire date is annual salary use ability to input different number of employees per years this annual salary rise percentage is monthly bonus tax rate let me give you a couple of examples let it be see if you're sure going to hire in March 20 we are not going to fire him so the fire date will be December 24 so annual salary can be $50,000 and you see the one CEO over the time so you may see one see fuel which is one could count starting from March to the end of the model which is December 24 also you can input 5 percentage of salary growth rate you may see the amount by years connected to this annual salary and bucked advise annual salary rise let's set up 10 percent monthly bonus and 5 percent of tax rate so you may see below the calculation of salary broken down by manses monthly bonus which is 10 percent and 5 percent of monthly tech taxes related to the payroll another option to be admin account which will start April which will grow to the end of the model with anal Sileo so $2,000 let it be here number one two four six eight and ten head counts see percentage of annual salary rose five percentage of monthly bonus and five percentage of payroll tax rate so in here you may see total stuff numbers just to OTL number one twenty twenty started from year 2021 you have for that six eight and ten the last year of the model again calculation of salaries for this tool in this case for that counts population of bonuses and population of monthly based taxes we see in income statement total salaries and wages and here you may see the total amount of bonuses payroll taxes and wages for these accounts [Music] on the fixed expenses tab we input up to 15 line items for your fixed expenses let me show you how it works for example you have utilities you will start pay starting from March 20 till the end of the model which is December 24 and we see it here let's pretend periodicity will be daily use amount of $50 per day so you may see this amount here this calculated based on count of days within this month so obviously March 20 you have city 1 days that's why we'll have 50 credits at 50 in April you have 30 days this means this will be $1,500 also you have ability to input some growth rate year over year once you input this growth rate you will see that your utilities will grow or year over year let me give you a couple of other expenses types for example advertising let it start in March and finish in August 24 this will be on weekly basis is the amount of $100 without any gross so start starting from March till August 24 you have four hundred dollars per month which is for weekly payments each month and that's it another option is be weekly for example 500 dollars you can start from July for example and you'll have two payments which is to be weekly payments within two months $500 multiply it by two you have $1,000 per month again you can input some prostrate and you will see that your advertising expenses will grow here all year till the August 24 which is a last date of this expense type another option off is set up which can be one-time payment which will happen in February 20 is the amount of $5,000 obviously should not entertaining gross rates because this is just one-time fee and we see that office setup will happen in February 20 is this amount another option insurance let it be start from genu 20 till the end of the model and it can happen monthly with $1,000 per month is 5 percentage of gross first year 3 percentage of gross second year 2 percentage of gross short and what percentage year number 4 so it may see this calculation here starting from January 21 it will grow 5% which is $50 and starting from January 22 it will grow for 3% which is additionally $32 another option totally you may see that insurance will be paid $1,000 each quarter you can start it for example from February and this will be shifted to one month forward another option semi-annually in this case you have insurance payments once for half a year again with the percentage of gross and the last option annual payments or yearly payments will pay one-time for 12 months starting from February till December 24 for each expense type you can use growth rate and the calculation you may see in here also in income statement you may find total fixed expenses group if you will ungroup this section you can see these amounts broken down by manses and by fix it expenses line items well the variable expenses tab you can input your variable expenses is a percentage of total revision let me show you how it works for example bank fees and your bank fees is 2.5 percentage of your revenue so in the same way as your revenue grows over time you may see that when fees will grow as a percentage of this total revenue the same way you can input other variable expenses like for example 5% direct labor like 15 percent of total revenue and below you may see the calculation oh please variable expenses by manses so these expenses will be connected to the income statement tab section variable expenses and you may see these line items romances and break broken down by expenses types [Music] on the capex step you may input up to 20 development expenses categories let me give you a couple of examples so for example office development this purchase date February 20 with spending of ten thousand dollars and you also can input payment delay what does it mean let me set up two months is for example it means that this amount will be recruited February because of purchase date is February but wait development expenses will be in April 24 this office development and to have some balance
f capex accounts payable for two months let me give you another example other development expenses let's say much $25,000 zero five and delay this means that this amount is accrued in March and paid in March as well the total amount of development expenses can we find in assets tab by default it has use a full time for five years for the calculation of depreciation you may find calculation depreciation or development expenses in here here we may also find capital expenditure and closing net book value additionally you have up to six placeholders for other assets example other assets is usable time 10 years is cost of $25,000 and this launch date in April we may fight into here you can see capital expenditure and we see boom depreciation by monsters or this amounts and we see closing and Book value the total amount of depreciation you may find in income statement on the cash flow you may find cash flow from investing activities for these assets and on the balance sheet you may find fix it assets amount under non current assets and capex prepayment and capex payables as well [Music] the capitalization table you can input different founders and investors contributions broken down by different dates of finding is different cost of share or each series and you can see the dilution of shares after each round and free money total equity and post money total equity let's pretend that we have two founders on the 1 5 to 2 so total amount of shares for founder 1 can be 10,000 per found the number to 20,000 let's imagine that cost of share will be $2 and the data found in this February this means that investment for 500 won $20,000 for founder 2 is $40,000 in total they invest $60,000 which we see here the dilution is 34 33 to 67 percentage of shares so let's pretend that for serious a we have one investor and the date of issuance is my cost per share this $5 per share and number of shares is 1,000 so total amount of investment will be $5,000 we seeded before the serious a total equity was $16,000 after we $65,000 and investor one you have 3.23 percentage of shares and the shares founder 1 inform the two also diluted so to 2.26 and for two 6.52 percentage you can also input some emmaus for seriously HCSC the same way you can set up the date cost of share and up to five investors is up to five placeholders for number of shares the amounts of funding you may see in the cash flow in the ordinary equity risings and when we see the balance sheet it shows you the total equity by Monza's also on the top of the dashboard chef that's assumptions let me show you how it works so for the each depth are able to select the depth type there are two depth types in the model which is energy and usual energy means that each monthly payment which consists your depth repayment plus interest expenses will be equal each month in case if you will select usual your my depth repayments will be equal parts and interest will be just interest on the depths close and balance let me give you an example how it works so you may input some amount of the depth the launch date term you do 60 months and ingested 25% you may also input the grant which is just simple amount just paid in some specific months and that's it no repayments no charms in terms of interest so all the calculations of the depths when we see on the capital tab calculations for the depth number one tip number two deb number three total depth with grants these calculations impacts income statements interest expenses the cash flow interest page tab drawdowns debt repayments and on the balance sheet have the depth closing balance [Music] on the top of the dashboard you have currency denomination and taxes setup so currency inputs means that you can input all your drivers for the model using one currency you have currency outputs it can be the same as currency inputs and it can be different from currency inputs so let me give an example any input in the United States dollars well euro as an output and for this case you can set up currency exchange rate this is one point two for example in this case you will have in the model all your inputs in United States dollars all your outputs in euros and there will be conversion rate between currency inputs and currency outputs additionally you have denomination which means that you can denominator all your outputs on the reports in this example we have denomination is one thousand mean that your outputs is denominated by one thousand you can select Millions let me see now it is in million dollars and set also billions or without any denomination additionally you have corporate tax setup you can change this number and this will impart tax expenses in your income statement I hope you enjoyed my video thanks for reviewing this you can find more on my website in models lab calm and we'll see you later in the next videos [Music]