Collaborate on Proforma Template Word for Public Relations with Ease Using airSlate SignNow

Proforma template word for Public Relations

[Music] hello in this video I'm gonna walk you through the financial model example template I will show you the main inputs the core outputs reports and charts so let's get started [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 options 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 key in these cells and you have only online marketing in your model if you need boss 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 on high $2,000 and each next year it will be additional $20,000 the next step is to set up the cost per visit can be $1 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 D 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 thousands 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 it opportunities let's pretend this is 20 percent and it can be equal across all the years so 20 percent from 32.9 thousands of sales per two inches this is 6.8 roughly engage it opportunities and you can see this is also changeable by years the next step is to convert your engagement 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 we will set up everything revenue expenses Cox 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 EBG and EBT a percentage the cash flow chart per years and the cumulative cash flow also by years [Music] of the financial chart step you may see your main financial two guys on the two sets of charts which is the breakdown of two years by manses and the breakdown of five years by Montes so on the top you may see the abbot amount the next set of charts will show you the revenue break down 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 month for the two years and for the five years and on the last set of charts you will see the IBGE as a yellow line and the breakdown of this ebj which includes the revenue Cox and optics [Music] the operational chat step you may see your main operational matrix with the two ears broken down by manses and to the five years broken down by manses as well on the first set of charts you may see the new customers break down by your services or products also for two years by manses and with the five 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 fix-it expenses a BGA 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 details ation for example or fix it expenses or for variable expenses or for example for the revenue on 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 direct method operation investing and financing activities but more colopsis 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 broken down by five years and the selected year which you can change here you can down by Mathis you'll you may see the same information on the chart form the next set of tables and charts will show you the balance sheet mine keep ice broken down by five years and select TR by month and the last part will show you the cash flow statement break down for the five years and twelve months for the selected year as well as charts with the same information [Music] the benchmarking pystep we 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 is a percentage of revenue average monthly revenue per active customer and active customers per one directive GE ratio all these yellow cells are changeable for example you know the gross margin percentage for your industry for your country's sixty-five percent and we see this is change it on this chart immediately and to the right of this yellow sales we see the values calculated by the model based on assumptions which you sent revenue expenses cogs and all other assumptions steps on the graphical form you may see also comparison of gross machine populated by the model which is blue and industry gross margin which is orange the same net profit direct wages is a percentage of revenue average monthly revenue per active customer active customers per one direct FG [Music] you may see the revenue summary or the main revenue streams broken down by years and absolute values and as a percentage breakdown the same information is reported here on the charts also you are able to select specific year and to see the monthly run rate and REM your depth for these revenue streams on the bottom of top revenue report to me see the revenue breach and we see the total revenue for the starting year and for the engineer which may play using these setups and to me see the factors of growth in this revenue broken down by revenue streams the same idea is used on the top expensive step you may see top four expansive streams and all other expenses collapse it in one line also this is broken down by absolute values and percentage will go down the same information you will find on the charts also expenses the EPS muslin on rate is the ability to change the year and expenses breach this part 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 color you have some extra calculations related to revenue to depth 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 detail decks detailed explanation of what each tab does and what inputs you will find on this sheet and what kind of outputs can we find on the 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 stem [Music] on the revenue tab you may set up the main revenue assumptions for the model so let me show you how it works first of all you need to set up your services names or product names for example over in 1 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 statute number of customers if you have some active customers as of the start of the year so you can put some numbers for example 5060 for each service if you don't have any active customers you can just clean and start from zero active customs the next step is to allocate your new customers by different services so it's changeable by years for example for services a it can be 10 15 12 etc and you can adjust to have one Hydra percent on the total some other service for example 25 and 20 so now you have 100 percent for all the years the next set of assumptions is to setup your lifetime in manses let's pretend that in services a average customer will live in five months as services B type 6 10 12 and 15 months to right you may see the calculation effective customers by years depend of this lifetime if you change time say for example 8 can we see the changes in calculations this means that because of your new customers life more you'll have more active customers at the bottom you will see the total and the next step is to setup 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 billable billable hours per month on hydrogen 10 credit into any hydrogen 30 hydrogen 14 and the final step is to set up the price and this is an attitude step you may see the setup of marketing expenses seasonality assumptions across the year by manses to have yellow cells to set up your seasonality it can be for example 15 10 5 10 8 6 7 5 10 15 5 and 5 let me 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 1 hydrogen 1% 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 chart below with the graphical explanation of how your marketing expenses are broken down by manses if you don't really need the seasonality for marketing expenses you can just put 1/12 copy this across all the manses and you will see that your marketing expenses now is flat so no changes across the manses just equal amounts each month [Music] on the step you may find six categories or components of your one of them is predefined it which is direct salaries and wages I'll explain a bit later how to drive this on the direct wage step other five categories are available so you can change the names I don't know for example host gian so you can change the assumptions for example 15% of revenue and on the top you may see the main driver of calculation with the which is total revenue by manses here you may change the assumptions which is a percentage of total revenue below you may see the categories and calculations of components by manses and finally in income statement under total you may see all the components of cogs broken down by manses and record on by categories [Music] on the direct wage a step you have 19 categories to set up your direct account let me show you how it works mr. fall you have four different subcategories and let's go one by one first subcategory has five placeholders for example account executive or SDR and each one has its own biases and parameter parameter in this case this is 1 FG / X basis and basis this is new customers per month instead for example I will set 15 for Account Executives each 15 new customers within service a by months we require one additional FG the calculation of number of disk account you may see here this is by years also you can set up the annual salary per FTE of them $25,000 the annual salary rise five percent for three to enrich monthly bonus per cat count ten percent and pedal tax rate twelve percent if you don't pay them you can just clean this or this or both below you may see the calculation of your annual salary depends base annual salary and annual salary grows or raised below you may see the calculation of headcount by masses for these categories monthly base salaries by categories balances monthly bonus and payroll taxes also by categories and by months the next section consists of different bases which is active customers for example customer success so it can be per each to any active customers who have one new FG or headcount the same annual salary and your celery gross if it's needed monthly bonus and tax rate this shirt subcategory 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 annual salary salary gross monthly bonus and payroll tax rate and in the last section you can just set up manually example manager hire date for example March fire date can do the end of the model or some specific date again annual salary 50,000 you need to input the average number of the count can be one always or you can hire for example in year two two managers three four and five so the low you may see the calculation described it in March is the top march of the third date starting from January it's two then three and etc and finally an income statement under quacks you have direct salaries and wages which is a total of bonuses payroll tax [Music] on the admin wages step you can set up up to 19 categories for your admit staff let me show you how it works for example managers is hired a starting for example February 20 to the end of the model can adjust annual salary per one pit count for example $40,000 then you should input number of administer change into 2020 to be one the two three four and five they also can input any salary rise so example 3 percent of annual salary first year for next five and six also here we have set up for the monthly bonus of the five percentage per month and the payroll tax rate 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 accounts starts in February because we set up February start to fire here you can have monthly by salaries monthly bonus calculation and monthly base taxes which is perilous and under income statement you have total Edwin sellers wages section where we see the breakdown of all your accounts categories and they break down the values by months [Music] but 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 can we 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 wipe 15% of total revenue and below you may see the calculation all these 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] of the fix-it spends a step if we input up to 15 line items for your fix-it 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 the amount of $50 per day so you may see this amounts in here this calculated based on count of days within this month so obviously March 20 you have city 1 days that's why you'll have 50 grand less than 50 in April you have 30 days this means this will be $1500 also you have ability to input some growth rate year-over-year once you input this row straight you will see that your villages 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 with amount of $100 without any gross or start starting from March to August 24 you have four hundred dollars per month which is four 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 favorite 20 is the amount of $5,000 obviously should not entertaining Ross 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 started from join you 20 to 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 if we 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 quarterly 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 growth and the last option is annual payments or yearly payments you'll pay one time per 12 months starting from February to December 24 for each expense type you can use growth rate and the calculation we 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 mouths broken down the manses and by fix-it expenses line items [Music] on the capex step you may input up to 20 development expenses schedulers 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 monsters for example it means that this amount will be recruited February because of purchase date is February but wage development expenses will be in April 24 this office development and you have some balance of capex accounts payable or two months s I will give you another example other development expenses let's say much $25,000 zero time and delay this means that this amount is accrued in March and paid in March as well the total amount of development expenses you find in assets tab by default it has usable time for five years or 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 lunch date in April unified into here you can see capital expenditure when we see boom depreciation by manses for this amount and we see closing at 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 the balance sheet you may find fix it a set amount under non current assets and karbix 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 pramana total equity and post money total equity let's pretend that we have two founders on the one party - so total amount of shares for founder one can be ten thousand profound the number to twenty thousand let's imagine that cost of share will be two dollars and the data found in this February this means that investment for Faulkner 120 thousand dollars were founded to is forty thousand dollars 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 is $5 per share and number of shares is 1,000 so total amount of investment will be $5,000 we see that before the series 8 total equity was 16 thousand dollars after is $65,000 and investor one we have 3.23 percentage of shares and the shares of founder 1 and funded - also diluted thirty two point twenty six and four to six point 52 percentage you can also input some amounts for Sirius B at CDC 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 which shows you the total equity by ounces [Music] also on the top of the dashboard yep that's assumptions let me show you how it works so for the each depth are able to select the depth type there are 2 DEP types in the model which is energy and usual energy means that each monthly payment which consists your debt repayment plus interest expenses equal each month in case if you select usual your mind depth repayments will be equal parts and interest will be just interest on the depths close and balanced let me give you an example how it works so you might interest amount of the depth the launch date term B 60 months and interest can be 5% you may also input the grant you just just simple amount you just pay it 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 depth number to that number three total depth with grants these calculations in parts income statement 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 set up so currency inputs means that you can input all your drivers for the model using one currency we 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 and the input in the United States dollars you have your own 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 have intermodal 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 1000 I mean 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 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] you