Collaborate on Free Pro Forma Template Excel for Production with Ease Using airSlate SignNow

Free pro forma template excel for Production

[Music] hello in this video I'm gonna walk you through the financial model itself template I will show you the main inputs the core outputs reports and charts so let's get started [Music] on the dashboard tab you may input your core inputs or drivers for the model see the core financials and core outputs on the charts so let's set up this model and we will start from units of measurement we have two options here to set up a great units of measurement and mass units of measurement please note that each yellow cell has its own explanation what you are supposed to input just easier to navigate across the model and to understand what you're supposed to input here so let's set up the average for example in square feet and the mass in pounds so the next step is to stop your total and acreage by years for example five thousand first year and additional one thousand occurs each next year the next step is to locate this total acreage by ownit acreage and lizard or rented a courage for example 30 percent 35 40 45 and 52 the own it and the rest obviously will be lizard in the next section you may see the absolute values of acreage in square feet based on this allocation and total land acreage and the next step is to set up your purchase cost per square foot and the rent cost per square foot per month for example 10,000 and additional thousand dollars will be the purchase cost per square foot and the rent cost can be refunded 350 400 450 and 500 the calculation of rent you may see on the variable expenses land rent cost on the SS step you may see the total on Atlanta Mount and the capital expenditure by manses in Section number 2 and once you set up everything all the expenses wages and have any assumptions you may review your core financials the revenue breakdown profitability cash flow and the cumulative cash flow chart [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 have it 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 for fixed 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 in 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 they selected here which you can change here or can down by months 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 breakdown for the five years and twelve months for the selected year as well as charts with the same information [Music] the benchmark API step you may compare up to five by jamar keep ice with your industry standards for example gross margin the industry standard is 60% for your country or your business and we see on this chart gross margin in the steel keeper exchange it which is orange here you have also net profit wages as a percentage of revenue harvest productivity pounds per square foot per year and the acreage square foot per direct FTE all this yellow cells which are industry keep your eyes are changeable net profit can be 20% for example for your industry for your country and to the right of this yellow cells you may see the values calculated by the model driven by assumptions you need to input it before on the revenue expenses and wages tips the same information you may see on the charts which is gross margin net profit wages is a percentage of revenue harvest productivity and acreage square foot per directive key on each chart you may see the orange this is industry specific benchmark and as a blue value this is a value calculated by the model [Music] on the financial charts step you may see your mind financially guys under two sets of charts which is the breakdown of two years by manses and the breakdown of five years by Montes on the top you may see the abbot amount the next set of charts will show you that when you 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 EBG which includes the revenue Cox and optics [Music] the pressure of chat step in we see two sizing charts just break down two years by months and five years by month the top you may see the acreage allocation by five categories of products the next set of charts will show you the production amount pounds in this case exclusion the losses and the last set of charts will show you the sales volume broken down by products and also regard by Vance's for two years and five years [Music] on the top expenses tab you will find a breakdown of top for expenses categories and all other expenses collapse into other category let me see the breakdown of absolute values broken down by years with the total below and also to the right to me 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 and we find expenses depth and monthly run rate for select the TR you are able to change this year and you may see the absolute values and percentage breakdown on the pie chart and the expenses bridge you will find the main drivers of expenses gross between these two years this year's us are also changeable so you may select the first year and you will sell at the last year and let me see that total expenses started in 2020 will change to total expenses in 2024 why this waterfall [Music] of the break-even 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 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 imported 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 we must select one of them and below you may input multiple of methods based on this information we can see terminal value which is calculated on an elaborate free cash flow you may see the present value of on laboratory cash flow and PV and multiplicator valuation for this particular company on the top revenue tab you may see the breakdown of your revenue by products and also by years whose 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 see the revenue depth and multi-run rate chart we can select the ER and based on this year you will see the information of 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 mr. the last year and between these years you will see the waterfall chart and to me see which are the main drivers of your revenue grows which specific products grows faster and which specific products grows slower [Music] the color coding in the model is very simple you may change any yellow cell and 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 we have some extra calculations related to revenue to depth equity inventory an aberration 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 on what each dot does and what inputs you will find on this sheet and what kind of outputs and we find on this sheet 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] the revenue tab you can input your revenue assumptions related to the model first of all you need to set the product names all these cells are adjustable for example instead of product a you can sell tomatoes for example etc the next step is to allocate your acreage by this protected by years for example 10% for product a in the first year that twelve fifteen eighteen and twenty the first product in this allocation is calculation means that 100% minus previous for allocations if you input something wrong for example ninety percent here fifteen for the second product 24 short 25 for fourth you have minus fifty which is one hundred percent minus tomb of previous four this means that this is wrong you will see the red color this means you need to adjust something in the model to have the gray color which means that everything is okay here the next step will show you the acreage location square fits by years across the products to understand what the amount in absolute values and the next step is to set up your productivity or production per harvest this is now in pounds per square foot but again on the dashboard you will be able to change the matrix so you can set the acres and kilograms for example if you would like by default you will see the square feet and pout so you can set this productivity by products like land it had it in ten add it to any or editon fourteen hundred and sixty and this is again per one harvest where to set up count of harvests I will show you a bit later the next step is to stop your losses ratio obviously you have losses so you can input percentage of losses for each harvest means it for example four percent five six seven eight this is changeable by products and by years you can also just zero out if you don't need any losses as well as if you don't need for example for products if you need just three products you can just adjust values and for example 50 to 525 and you will have in your model on these three products in this case you can clean all the assumptions related to product number three and number four that's also an option let me switch it back so the next step is to set up the sales price per pound this is also by years so you can set for example ten fifteen twenty twenty-five and thirty it's obvious and the last step on this step is to set up sales period means that after harvest you sell all your production within six months if you don't need this you can just put one months and all this all the production will be solved within one month after the harvest the next step is to stop count of harvests on the seasonality tab so you have five products and you have demands this allocation started from January to December and you can just select on this means you have first harvest in March then you can select on for August this means a second your harvest will happen in August and the same for each product so you can select just one harvest for example in May for product B and it works for everything if you just need to turn off the select off and you just turn it off the harvest in the main that how that works on the cost of goods sold tap you can input up to five different categories or components of your cost of goods sold the first line item is reserved for direct sellers and wages which is calculated from direct wages tab so this is already calculated you don't need to change any assumption here and also you have five line items some of them are predefined for example seeds nutrients other if you don't need you can just type anything you want like type a Type B Cox or any name you would like and you have two options here to calculate as $1 per square foot per month or the percentage of total revenue so you have seats for example $20 per square foot or you can input seeds as a five percentage of revenue like this on the top you have the main drivers like total revenue total acreage total production in pounds below you have the calculations of these categories by manses and by products and in income statement you may find under Cox section these calculation also categorize it by category and by Monza's [Music] on the direct wager step you will input your direct wages assumptions we have up to nineteen categories broken down by three sections let me show you how it works so first five directed counts you can adjust as a parameter means that 1fg pair basis and the basis in this cases per square foot and this can be 1fg per 200 square foot or it can be one if cheaper on hydrogen fifty square foot for the type number two and depending on the acreage you will have the calculation of direct employees here also you can set up the annual salary per one FTE which can be for example twenty thousand dollars you can input and your celery rice five percent for example per year below you me see the calculation of annual salary depending on by salary and for depend upon annual salary rise we have also also monthly bonus which can be ten percent for example and the tax rate which is payroll tax example twenty percent twenty twelve percent in this case below you me see the calculation staff numbers account numbers salaries monthly bonus monthly base taxes the next category or type is to drive your direct headcount as a parameter pair pounds of production means it per 10,000 of pounds production you'll have one FG again you can setup salary sell your eyes on sleep bonus payroll tax rate and you will see the amount of account which is needed depended on the production driven by the revenue assumptions and the final tab in this case is to set up your directed count as just hired date for example february twenty fire date can be the last date of the model and the basis is manual entry you can set up the salary as well ten thousand dollars and you can adjust numbers of director please menu 2 4 6 8 and 10 an income statement under Cox section we see the direct service at radius calculation so it's a part of Europe cost of goods sold [Music] but the variable expenses step you can input up to five different categories or components of your variable expenses the first line item is reserved for land read cost which you set up on the dashboard here so you have amount of rent cost per square foot per month so the calculation we seen here in the first line item also you have up to five another categories some of them are predefined it you can change the names like our expenses to for example or something like that any name you would like and you have four different types of how you would like to calculate it as dollar per square foot per month can be changeable also three for example as dollar per 1 pound of production for example for packaging and the one dollar for example as dollar per square foot per month can be four and two line items for the calculation of as a percentage of revenue for example 2 percent of revenue on the top you have the main drivers which is total revenue total acreage and total production below you may find the calculation for these categories by manses and in the income statement under variable expenses schedule you may see this value so located by categories and allocated by month [Music] on the fix it expends a step if we 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 the amount of $50 per day so you may see this amount still 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 featured hundreds and 50 in April you have 30 days this means this will be featured hundreds dollars 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 over year over year let me give you a couple of other expenses types for example advertising let it start in March and finishing August 24 this will be on weekly basis with 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 five hundred dollars you can start from July for example and you'll have two payments which is to be weekly payments within two months five hundred dollars multiply it by two you have $1,000 per month again you can input some growth rate 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 setup which can be one-time payment which will happen in February 20 is the amount of $5,000 obviously you should not entertain across rates because this is just one time feet and you me see that office set up to happen in February 20 is this amount another option insurance let it be start from genu 20 to the end of the model and it can happen monthly with $1000 per month is five percentage of gross first year three percentage of gross second year two percentage of gross short and one percentage year number four so it may see this calculation here starting from January 21 it will grow for 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 which 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 growth and the last option annual payments or yearly payments you'll pay one time for 12 months starting from fabrizzio December 24 for each expense type you can use growth straight and the calculation you may see in here also an income statement you may find total fixed expenses group if you will ungroup this section you can see these mounds broken down romances and by fix-it expenses line items [Music] on the admin wage a step you can set up up to nineteen categories for your it means stuff let me show you how it works for example managers this hired date starting for example from February twenty till the end of the model can adjust annual salary per one head count complete for example forty thousand dollars then you should input number of admitting please let's pretend in the 2020 it will be one the two three four and five they also you can input any salary rise so for example three percent of annual salary first year for next five and six also here you have setup for the monthly bonus example five percentage per month and the payroll tax rate for example twelve below you may see the calculation of annual salary depends on the final salary for the base year and for the annual salary rise also here you can see the calculation of number of head counts starts in February because we setup February start to higher here you can have monthly base salaries monthly bonus calculation and monthly based axis which is parallel axis and under income statement you have total has been salaries and wages section where we see the breakdown of all your accounts categories and they break down the values by months [Music] on the capex step you may input up to 20 element expensive schedulers let me give you a couple of examples so for example office development mr. chase date February 20 we're spending of $10,000 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 cause of purchase date is February but which development expenses will be in April 24 this office development and you will have some balance of capex accounts payable or - manses let me 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 and we find in a set step by default it has use a full time for five years for the calculation of depreciation you find calculation depreciation for 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 for example other assets is useful time 20 years this cost of $25,000 and this lunch date in April let me fight between here you can see capital expenditure and we see publicly she ation by manses or 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 of these assets and on the balance sheet you may find fix it a set amount under non current assets and capex prepayment and capex payables as well [Music] also on the top of the dashboard have debts assumptions let me show you how it works so for the each depth are able to select the depth type there are two DEP types in the model which is energy and usual energy means that each monthly payment which consists your debt repayment plus interest expenses will 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 balanced let me give you an example how it works so you might input an amount of the depth the launch date term due 16 ounces and interests can be 5% you may also input the grant which is just simple amount just pay it in some specific months and that's it no repayments no charms drops 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 that number three total depth with grants these calculations in parks income statement interest expenses the cash flow interest rate tab drawdowns debt repayments and on the balance sheet have the depth closing balance 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-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 profound 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 per 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 is $5 per share and number of shares is 1,000 so total amount of investment will be $5,000 we seeded before the series a total equity was $16,000 after is $65,000 and investor one we have 3.23 percentage of shares and the shares found one and for the two also diluted 32 point 26 and purchase 6.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 finding 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] on the top of the dashboard you have currency denomination and taxes it up 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 and the input in the United States dollars you have 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 a 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 means that your output is denominated by 1000 you can select Millions you may see now it is in million dollars you can set also billions or without any denomination additionally you have corporate tax setup change this number and this will impart tax expenses in your income statement I hope you enjoyed my video thanks for reviewing this and you can find more on my website in models lab calm and we'll see you later in the next videos [Music] [Music]