Create Your Excel Pro Forma Template for Logistics Effortlessly

Excel pro forma template for Logistics

[Music] [Music] on the dashboard you can set up your core inputs or drivers and see the core financials and core charts related to the model so let's start the model setup from the launch date for example your business will launch my 20 also you can see that each yellow cell has this notification window is explanation of what you're supposed to input in this cell so it will help you to understand and navigate across the model easily so the next step is to set up your marketing budget and visitors assumptions you have two options in the model which is online marketing and offline marketing if you don't need for example offline marketing you can just clean these cells and you will have only online marketing in your model if you need both just adjust these assumptions as needed and it will drive your model so let me show you how it works for example for online marketing you can put annual marketing budget for example one hundred thousand dollars and each next year it will be additional twenty thousand dollars next step is to set up the cost per visit it can be one dollar 1.2 1.4 1.6 and 1.8 by years the same idea for offline marketing and as a result you can see the total visitors which you have by years and the next step is to convert these visitors to new customers across the conversion rates across your sales funnel so first of all you have conversion rates from visitors to sales opportunities let's pretend this is 50 for example it can be changed by years as well 52 54 56 58. so 67 000 of visitors will be converted to 33.9 thousands of sales opportunities in 2020. the next step is to convert your sales opportunities to engage in opportunities let's pretend this is 20 and it can be equal across all the years so 20 from 32.9 thousands of sales opportunities this is 6.8 roughly engaged opportunities and you can see this is also changeable by years the next step is to convert your engaged opportunities to new customers this is the final step of your sales funnel within the model so you can put 10 for example 11 12 13 and 14. so 10 from 6.8 thousands of engaged opportunities will give you 679 new customers for the 2020. so after you will set up everything revenue expenses wages and all other assumptions you may review your core financials and review your revenue breakdown by services or products your profitability which includes revenue ebga and evga percentage the cash flow chart by years and the cumulative cash flow also by years [Music] on the financial charts tab you may see your main financial kpis on the two sets of charts which is the breakdown of two years by months and the breakdown of five years by months on the top you may see the habit amount the next set of charts will show you the revenue breakdown by products the next operating cash flow broken down by cash inflow and cash outflow the next set of charts will show you the cash balance by months for the two years and for the five years and on the last set of charts you will see the abga as a yellow line and the breakdown of this fbga which includes the revenue and opex on the operational charts tab you may see your main operational matrix for the two years broken down by months and for the five years broken down by months as well so on the first set of charts you may see the new customers breakdown by your services or products also for two years by months and 45 years by month [Music] on the income statement tab you may see your main components of your profit and loss which is total revenue total cost of goods sold gross margin total variable expenses total admin salaries and wages total fixed expenses fbga depreciation and amortization habit interest expense net profit before tax your corporate tax and as a result net profit after tax please note that each category has its own subcategories so you may click on this plus button and see the utilization for example for fixed expenses or for variable expenses or for example for the revenue the cash flow statement you may see your cash flow broken down by cash flow from operating activities cash flow from investing activities and cash flow from financing activities the same information you may see on the cash flow statement in a direct method operating investing in financing activities but more collapsed form just easier to see the information here and the balance sheet will show you the breakdown of your current assets non-current assets current liabilities non-current liabilities and equity by its subcategories the summary of three statements you may find on the financial statement summary tab on the top you have income statement broker done by 5 years and the selected er which you can change here broken down by months you will you may see the same information on the chart form the next set of tables and charts will show you the balance sheet main kpis broken down by five years and select tr by months and the last part will show you the cash flow statement breakdown for the five years and 12 months for the selected year as well as charts with the same information [Music] on the benchmark apis tab you may see five industry-specific benchmarks and you may see the comparison of these benchmarks with what is calculated in your model so you have gross margin percentage net profit percentage direct wages as a percentage of revenue average monthly revenue per active customer and active customers per one direct fte ratio all these yellow sales are changeable for example you know the gross margin percentage for your industry for your countries 65 percent and we see this is changed on this chart immediately and to the right of this yellow sales you see the values calculated by the model based on assumptions which you set revenue expenses and all other assumption steps on the graphical form you may see also comparison of gross margin calculated by the model which is blue and industry gross margin which is orange the same net profit direct wages as a percentage of revenue average monthly revenue for active customer and active customers per one direct fte [Music] you may see the revenue summary for the main revenue streams broken down by years as an absolute values and as a percentage breakdown the same information is reported in here on the charts also you are able to select specific year and to see the monthly run rate and revenue depth for these revenue streams on the bottom of top revenue report you may see the revenue breach and you may see the total revenue for the starting year and for the engine year which you may play using these setups and you may see the factors of growth of this revenue broken down by revenue streams the same idea is used on the top expenses step you may see top 4 expensive streams and all other expenses collapse it in one line also this is broken down by absolute values and percentage background the same information you may find on the charts also expenses debts monthly on rate usability to change the year and expenses breach these factors of expenses grows from one year to another year [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 impacts 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 caller you have some extra calculations related to revenue to debts equity inventory and everything 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 data detailed explanation of what each tab does and what inputs you will find on this sheet and what kind of outputs you may find on this sheet as well any header of this section is also clickable so you may click on for example book assets and you go directly to this step [Music] on the revenue tab you may set up the main revenue assumptions for the model so let me show you how it works so first of all you need to set up your services names or product names for example offering one or whatever name you need also if you don't need all five services or products you can just clean assumptions for fourth and fifth and have only three offerings the next step is to set up your starting number of customers if you have some active customers as of the start of the year so you can put some numbers for example 50 60 for each service if you don't have any active customers you can just clean and start from zero active customers the next step is to allocate your new customers by different services so it's changeable by years for example for services eight it can be 10 15 12 etc and you can adjust to have 100 percent on the total some other service for example 25 and 20. so now you have 100 for all the years the next set of assumptions is to set up your lifetime in months let's pretend that in services a average customer will live in five months so this is b type 6 10 12 and 15 months to try it you may see the calculation of active customers by years depends of this lifetime if you change samsung for example 8 you may see the changes in calculations this means that because of your new customers life more you'll have more active customers on the bottom you may see the total and the next step is to set up the pricing first of all you should understand of how much in average billable hours per month per active customers each service will require for example in services a average customer you'll have 100 billboard billable hours per month 110 120 140 and the final step is to set up the price and this is an edited step you may see the setup of marketing expenses seasonality assumptions across the year by months so you have yellow cells to set up your seasonality it can be for example 15 10 5 ten eight six seven five ten fifteen five and five as you may see i just set up the breakdown of marketing expenses by year and i see that check is red that's because the total is 101 percent means something is wrong here so let's fix it in october 14 so now check is green and check is 100 which means everything is good here you may see also the charts below with the graphical explanation of how your marketing expenses are broken down by months if you don't really need the seasonality for marketing expenses you can just put one divided by 12. copy this across all the months and you will see that your marketing expenses now is flat so no changes across the months just equal amounts each month [Music] on the cox tab you may find six categories or components of your cogs one of them is predefined which is direct salaries and wages i'll explain a bit later how to drive this on the direct wages tab other five categories are available so you can change the names i don't know for example hosting so you can change the cox assumptions for example 15 of revenue and on the top you may see the main driver of calculation for the clocks which is total revenue by months and here you may change the assumptions which is a percentage of total revenue below you may see the categories and calculations of cox components by months and finally an income statement under cox total you may see all the components of cokes broken down by months and rakadan by categories [Music] on the direct wages tab you have 19 categories to set up your directed count so let me show you how it works first of all you have four different sub categories and let's go one by one first sub category has five placeholders for example account executive or sdr [Music] and each one has its own basis and parameter parameter in this case this is 1ft per x basis and basis this is new customers per month means that for example i will set 15 for account executives each 15 new customers within service a by month will require one additional fte the calculation of number of this account you may see here this is by years also you can set up the annual salary per fte for example 25 000 the annual salary rise for example 5 4 3 2 average monthly bonus market count 10 and payroll tax rate 12 if you don't need them you can just clean this or this or both below you may see the calculation of your annual salary depends on base annual salary and annual salary growth or rise below you may see the calculation of head count by months for these categories monthly base salaries by categories by months monthly bonus and payroll taxes also by categories and by months the next section consists of different basis which is active customers for example customer success so it can be per each 20 active customers you'll have one new ft or headcount the same annual salary annual salary growth if it's needed monthly bonus and tax rate the third sub category has a which is billable hours means that for each for example 160 hours you need engineer so if you have 320 billable hours you will need two engineers obviously the same idea of annual salary salary growth monthly bonus and payroll tax rate and in the last section you can just set up manually for example manager with higher date for example march fire date can be the end of the model or some specific date again annual salary 50 000 you need to input the average number of but count can be one always or you can hire for example in year two two managers and three four and five so below you may see the calculation just higher than march as we set up march of the start date starting from january it's two then three and etc and finally in income statement under quarks you have direct salaries and wages which is a total of bonuses payroll tax [Music] on the admin wages tab you can set up up to 19 categories for your admin staff let me show you how it works for example managers this higher date starting for example from february 20 till the end of the model you can adjust annual salary fire one account for example forty thousand dollars then you should input number of admin employees let's pretend in the 2020 it will be one then two three four and five that also you can input any salary rise so for example three percent of annual salary first year four next five and six also here you have set up for the monthly bonus for example five percentage per month and the payroll tax rate for example 12. below you may see the calculation of annual salary depends on the annual salary for the base year and for the annual salary rise also here you can see the calculation of number of headcounts starts in february because we set up february start of higher here you can have monthly base salaries monthly bonus calculation and monthly base taxes which is payroll taxes and under income statement you have total admin salaries and wages section where you may see the breakdown of all your accounts categories and they break down the values by months [Music] on the variable expenses tab you can input your variable expenses as a percentage of total revenue 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 bank fees will grow as a percentage of this total revenue the same way you can input other variable expenses like for example five percent direct labor like 15 of total revenue and below you may see the calculation of these variable expenses by months so these expenses will be connected to the income statement tab section variable expenses and you may see these line items by months and break broken down by expenses types [Music] on the fixed expenses tab you may input up to 15 line items for your fixed expenses let me show you how it works for example we have utilities you will start pay starting from march 20 till the end of the model which is december 24 let me see it here let's pretend periodicity will be daily with amount of 50 per day so you may see this amount in here it is calculated based on count of days within this month so obviously in march 20 you have 31 days that's why you will have 1550 in april you have 30 days this means this will be 15 hundred dollars also you have ability to input some growth rate year over year once you input this gross rate you will see that your utilities will grow over 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 with the amount of 100 without any growth so start starting from march till august 24 you have 400 dollars per month which is four weekly payments each month and that's it another option is b weekly for example 500 you can start from july for example and you will have two payments which is two b weekly payments within demands five hundred dollars multiplied by two you have one thousand dollars per month again you can input some gross rate and you will see that your advertising expenses will grow year over year till the august 24 which is the last date of this expense type another option office setup which can be one time payment which will happen in february 20 with amount of five thousand dollars obviously you should not input any gross rates because this is just one time fee and you may see that office setup will happen in february 20 is this amount another option insurance let it be start from january 20 till the end of the model and it can happen monthly with one thousand dollars per month is five percentage of growth first year three percentage of gross second year to percentage of growth third and one percentage year number four so you may see this calculation here starting from january 21 it will grow for five percent which is fifty dollars and starting from january 22 it will grow for three percent which is additionally 32 dollars another option quarterly you may see that insurance will be paid 1 thousand dollars 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 will have insurance payments once for half a year again with the percentage of gross and the last option is annual payments or yearly payments you'll 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 months and buy fixed expenses line items [Music] on the capex tab you may input up to 20 development expenses categories let me give you a couple of examples so for example office development is purchase date of february 20 with spending of 10 000 and you also can input payment delay what does it mean let me set up two months for example it means that this amount will be accrued in february because of purchase date is february but paid development expenses will be in april 20 for this office development and you will have some balance of capex accounts payable for two months let me give you another example other development expenses let's say march twenty five thousand dollars with zero payment delay this means that this amount is accrued in march and paid in march as well the total amount of development expenses you may find in a set step by default it has useful time for 5 years for the calculation of depreciation and we find calculation depreciation or development expenses in here here we might also find capital expenditure and closing net book value additionally you have up to six placeholders for other assets for example other assets with useful time of 10 years with cost of 25 000 and this launch date in april you may fight it in here you can see capital expenditure and we see book depreciation by months for this amount and you may see closing net 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 fixed assets amount under non-current assets and capex prepayment and capex payables as well [Music] at the capitalization table you can input different founders and investors contributions broken down by different dates of finding is different cost of share for each series and you can see the dilution of shares after each round and pre-mining total equity and post money total equity let's pretend that we have two founders on the one found the two so total amount of shares for founder one can be ten thousand for founder number two twenty thousand let's imagine that cost of share will be two dollars and the date of finding is february this means that investment for farmer one is twenty thousand dollars for founder two is forty thousand dollars in total they invest sixty thousand dollars which you may 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 may cost per share is five dollars per share and number of shares is one thousand so total amount of investment will be five thousand dollars you may see that before the series a total equity was sixteen thousand dollars after sixty five thousand dollars and investor one we have three point twenty three percentage of shares and the shares of under one and founder two also diluted 32.26 and 46.52 percentage you can also input some amounts for series b and series c 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 finding you may see in the cash flow in the ordinary equity horizons and you may see the balance sheet it shows you the total equity by months [Music] also on the top of the dashboard we have depth assumptions let me show you how it works so for the each depth we are able to select the depth type there are two dip types in the model which is annuity and usual annuity means that each monthly payment which consists your debt repayment plus interest expenses will be equal each month in case if you select usual your main depth repayments will be equal parts and interest will be just interest on the depth closing balance let me give you an example how it works so you may input an amount of the depth the launch date term will be 60 months and interest can be five percent 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 that number one dip number two step number three total debts with grants this calculations impact income statement interest expenses the cash flow interest paid tab drawdowns debt repayments and on the balance sheet you have the debt 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 when you input in united states dollars you have euro as an outputs and for this case you can set up currency exchange rate this is 1.2 for example in this case you will have in the model all your inputs in the 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 denominate all your outputs on the reports in this example you have denomination is 1000 means that your outputs is denominated by 1000 you can select millions you may see that now it is in million dollars you can set also billions or without any denomination additionally you have corporate tax setup you can change this number and this will impact tax expenses in your income statement i hope you enjoyed my video thanks for reviewing this you can find more on my website finmodelslab.com and we'll see you later in the next videos [Music] you