Collaborate on Free Pro Forma Template for Research and Development with Ease Using airSlate SignNow

Free pro forma template for Research and Development

[Music] hello in this video i'm gonna walk you through the financial model excel template i will show you the main inputs the core outputs reports and charts so let's get started on the dashboard you can input your core drivers for the model under core input section input assumptions for the equity waterfall review the core financials and profitability chart by years so let's start from the core input section first of all we have construction costs which is a summary of construction cost sheet i'll explain a bit later how it works for now this is just output number the next step is to input your loan to cost ratio for example 50 and that will drive your construction one which is in this case half of the construction costs then coupon serum popping months is three that means that those months will have 63 the next month you'll have 67 and starting from the months number three you'll have 100 percent of your occupancy the next step is to set up the construction start date for example my and it will drive construction long launch as well the operation start date means that the first month when your business will start generate revenue for example october 20 hold period example 115 months and that will drive your sale date which is construction start date plus 115 months in this case this is december 29th the next step is to set up your exit cap rate for example seven percent or four percent and that will drive your sales price the next step is to stop your sales expenses five percent which is a percent of sales price you can drive this assumption as you would like as well so here you have the albums which is sales price sales expenses and net sales price at the bottom you may see the debt service coverage ratio which is driven by depth assumptions and core financials the depth assumptions i will explain in a bit once you set up everything which is inputs wages expenses and assets you may review your core financial step and review your profitability chart by years which includes the revenue ppg and everyday percent by years on the top right section of the dashboard you have equity waterfall with either hubble's assumptions and outputs so you have four sections here equity contributions promote structure partnership level returns and property matrix so under equity contributions you should set up the allocation between general partners investors and limited partners investors contributions let's pretend general partners investors share is five percent and other 95 percent is for limited partners to the right you may see the calculation of amounts for these contributions on the next section you can input the promote structure assumptions which is hardly two three and four irr so let's pretend for huddle two there are hardware will be seven percent parallel three twelve and sixteen for hard number four and you have the gp promote depending on hurdle 5 10 and 15 for example so you may see to the right how this promote impacts the shares of general partner and limited partner so for the hard one you have 5 and 95 which is equity contribution share for the huddle 2 you have 9.8 and 90.3 percent 14.5 85.5 19.3 and 80.8 percentage the next section will show you the partnership level returns which is cash outflow or cash contributions which is the same amount 28 and 536 thousands here you can see the profit broken down by limited partner and general partner the irr percentage and multiple on investment capital or equity multiple you can see also some property matrix for the acquisition and exit like purchase price per room and total which is priceless that proceeds per room in total exit sales price per room in total and finally net sales proceeds per room and total as well on the equity waterfall tab you may see all the calculations by months for the equity contributions promote structure so you can see the net cash flow for distribution summary of investor level returns by months return of capital and hard law preferred return hardly 2 calculation hard builds recalculation huddle 4 some rich automatics and some risk matrix [Music] on the kpi step you may see the main investors kpis and outputs which are partnership level returns property metrics property level return matrix and sensitivity analysis under partnership level returns all kpis broken down by limited partner and general partner kpis you may see the cash outflow or cash contributions profit irr percentage and multiple on investment capital or equity multiple under property matrix you may see the acquisition and exit kpis which are chase price per room and total purchase price less debt proceeds per room in total the sales price per room in total and net sales proceeds also per room and total under property level return matrix you may see the kpi kpis broken down by unlabeled and leveraged cash flow which are irr percentage multiple on investment capital total cash invested total revenue total profit and cash on cash return or average free and clear charm [Music] on the sensitivity analysis tab you may see the different scenarios of your metrics like irr multiple on investment capital and profit depending on different hold periods and exit cap rates so these yellow cells are changeable for example you may say the middle point for your whole period 80 months so you'll have scenarios for hold period 56 months 68 months is 80 months is 92 and 104 months also you can set the middle point for your exit cup rate for example six percent so you will have scenarios for two four six eight and ten percent of exit cap rate at the sensitivity tab you may see the values for your irr depending on these exit cup rates and hold periods if you would like you may see the profit amount let's give a couple of seconds to update this step so you may see different profit amounts depending on the exit cup rate and whole period also on the top left cell you may see the profit calculated by the model the same way you have multiple on investment capital scenarios so you can see the value calculated by the model and different scenario assumptions and outputs on the income statement tab you may see your main components of profit and loss which is revenue sales and wages variable expenses gross operating profit management fees fixed expenses fbga total depreciation and amortization reserves for replacement habit interest expense net operating income gain loss on sale and finally net income each category has its own subcategories so for example if you would like to see the sub components of fixed expenses you can click on this plus button and you will see the utilization of fixed expenses the same works for revenue for salaries and wages like this and for variable expenses for management fees depreciation amortization as well on the cash flow you may see your cash flow from operating activities from investing activities from financing activities the cash flow and direct method you may see the same information in more collapsed form which is easier to review and on the balance sheet you may see your current assets non-current assets current liabilities non-current liabilities and your equity finally on the summary tab you may see the 10 years summary or the income statement and here you may click and select specific year and you will see this year broken down by months the same information you may see on the charts below then you may see the main kpis of your balance sheet also for the 10 years and for the selected year with charts and finally the cash flow statement information for the 10 years and the selected year by months with the charts which will show you the operating investing and financing activities [Music] the top revenue tab you may see your revenue breakdown by main components which are rent revenue and other revenue also you may see the breakdown of absolute values by years and percentage allocation by years the same information you miss you on the charts below which is breakdown of percentage and absolute values on the revenue depth and monthly rand rate you may review specific year which you can select here for example 2021 and here you may see the absolute values for this year and percentage allocation on the pie chart finally on the revenue breach you may see the main components of your revenue rolls so you can set for example 2028 start here is 2022 so you may see the 2022 total revenue on the left side of this chart from the right side to me c total revenue for 2028 which we just selected and in between you may see the main drivers of your revenue growth which is rent revenue and other revenue with absolute values of these growths between these two years on the top expense step you may see top four expense line items and all other expenses collapse it into one line which is other here also you may see the absolute values breakdown by 10 years and percentage allocation broken down by 10 years the same information you may see on the charts below when you see the percentage allocation and absolute values allocation by main expense categories on the expenses depths and on monthly android chart you may see the specific year broken down by absolute values on the charts on the left side and percentage allocation on the pie chart on the expenses bridge you may see the main drivers of your expense growth so for example i can say 2022 to 2027 and on the left side i may see the total expenses for 2022 on the right side i may see the total expenses of 2027 and in between you may see the main drivers of this expense growth sellers and wages in this case property tax lisa rental expense interest expense and other with absolute values on this chart [Music] the chat step you may see the main financial kpis by months for the next 10 years on the top you may see the net operating income by months the next chart will show you the ebj breakdown which consists of revenue salaries and wages variable expenses management fees fixed expenses and as a resulting blue line you may see the total apga by months the next chart will show you the operating cash flow broken down by cash inflow and cash outflow the next chart will show you the revenue breakdown by rent revenue and other revenue and finally on the last chart you may see the construction cost breakdown by months and by main groups which are acquisition slash land purchase soft costs hard costs and other costs [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 we 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 may 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 tab [Music] on the inputs tab you can input information about your property units so you have 40 placeholders and you can treat them as unit types or specific property units which means that for example studio apartments you can input 40 different units or it can be separate building one which is obviously one unit in this case all these yellow cells are changeable so you may just put any name you would like here you have count of units again for unit types it can be more than one in case of specific property unit it will be one the total square feet for this unit type or unit group average occupancy by groups average rent per month per occupied unit average other revenue per month per occupied unit and average dollars reserved for replacement for available units per month so for example one thousand dollars rent per month other revenue can be fifty dollars per month and fifteen dollars for replacement additionally you have yearly growth rate for this rent other revenue and replacements so it can be five percent for example next year two percent the 22 23 and 24 and thereafter you can put just one percent and to the right you may see the yearly growth multiple which is calculated based on this percentage by years on the seasonality tab you can input your unit's occupancy rate seasonality factors let me explain how it works for example in january i'll put minus seven percent and what does it mean imagine you have average seasonality for union group a which is eighty percent this means that in january it will be eighty minus seven percent which is seventy three percent for february it will be eighty minus five percent 75 in may for example it will be 80 plus 8 which means 88 all these yellow cells are changeable and also below you may see the chart with graphical visualization of this occupancy rate seasonality allocation by masses [Music] the construction cost step you can input your construction expenses broken down by four main categories which are acquisition and land purchase group soft costs hard costs and other costs each category has its own subcategories first line item for the acquisition land purchase is defined for the land purchase you should not change this name here it is gray but you can change this binding for example 3 million or even 0 out if you don't need to purchase the land you have also some predefined subcategories which are most typical for each of the groups but you can change these names as well or even remove these line items if you don't need them let me show you how it works so first of all you need to set up as we just discussed the category name then you should input the spending next you have different growth methods you have up to four different methods and i will walk you through one by one so for example if you will select the straight line first of all it's the simplest one you need to set up the start date and end date by default the start date is connected to the construction start date and end date is kind of three months is here but you can change it to six months or whatever amount of months you need so if you select the gross method a straight line you'll have the straight amount just this three millions broken down by nine months you have also check here to double check that this amount and amount by months is the same so it's just easier for you to understand how it works the next way is steady growth so you have steady growth each month first month is around 67 thousands the next 13 thousands 200 thousands let me show you how it works if i will insert you a small chart or better understanding so this is steady growth if i will select the bell curve i i need to input the bell curve shape you may see now this cell is yellow this means that i need to select something by default you will have some curve but you can change it to spiky you can see that this is spiky bulky of medium less spiky and flat oh this is more flat well curve so this amounts are spread here and the final option is to select as a gross method custom so you now you have this line as inputs it's yellow now check is not okay because we need to spread this amount so i can input one million here another million here and another million here so now check is okay so it's kind of more manual or custom way to input your spending by months obviously start date and end date is also not needed here because you input everything manually let me change it back i hope that makes sense the same idea for all other soft costs hard costs and other costs and as a final result you have the total construction costs and it's breakdown by months [Music] on the vm expenses tab you can set up your variable expenses and management fees so you have two categories here variable expenses assumptions with 15 placeholders and management fees assumptions with 6 plus covers some of the names are predefined these are most typical names for these categories but you can rename them as well for example utilities you can rename other variable expenses or any other name you would like so here you have also the notification or explanation how it will be calculated all these variable expenses will be calculated as a percentage of total revenue so for example for admin in general for 20 20 21 then each next year you can add for example zero half percent which will be calculated as a percentage of total revenue the same idea for all other land items for the management fees you have three options to input management fees as a percentage of total revenue as a percentage of rent revenue or as a percentage of other revenue id is the same you can just set up the percentage by years so below you have the main drivers for the calculation which is revenue breakdown rent revenue other revenue and total revenue and below you can see the calculation of variable expenses and management fees by lane items and by months and finally in the income statement under variable expenses section and under management fees section you can see the values by months and broken down by lane items on the fixed expenses tab you have 25 plus holders to set up your fixed expenses line items so let me show you how it works first of all in the categories column you need to input the name of this fixed expenses line item for example utilities next column is launch or start date by default this is connected to the operation start date on the dashboard but you can change it as well for example december 20 the end date is also connected to the sales date on the dashboard but you can change it for example january 29 then you have eight different periodicity approaches so let me show you how it works one by one for example one time you spending of one thousand dollars so obviously if you select at one time you don't need end date just one date and one spending we have in december twenty one thousand dollar the next you can select the daily basis and you will have thirty one thousand dollars because in december you have thirty one day then january 31 000 again february is 28 000 etc till the january 29th the january 29 is the last date when you have this category of fixed expenses another option is weekly in this case you'll have 4 payments each month 4 thousands per month if you will select biblically you will have two payments roughly each month so this in this case you'll have two thousand dollars in case of monthly we'll have steady one thousand dollars per month till the end which is january 29th in case of quarterly you will have one payment per quarter started from december 20. as you may see here if you will select semi-annually you'll have one payment per each half a year starting from december 20 and the last approach this yearly will have 1 000 each year in december cool thing which you have in the model this is yearly growth rate for example if i will select monthly august 20 for example and i want to add five percentage of growth the next year then four percentage into 2022 and two percent each next year so you may see that started from january 21 you will have instead of one thousand dollars one thousand fifty dollars then january 22 within the year you'll have 100 1092 dollars and etc till the end of the model so you'll have steady growth of this fixed expense by years and finally in income statement in the fixed expenses category you will see the breakdown of your fixed expenses buy land items and by months as well [Music] on the wages tab you have 19 placeholders to set up your staffing assumptions so let me show you how it works on the categories column you need to set up the position name for example ceo the higher date by default is connected to the operation start date on the dashboard and fire date is connected to the sales date on the dashboard as well but if you need you can use the drop down to select the needed date for example for ceo the hired date will be may and the fire will be still the sales date then you need to input the annual base salary for example fifty thousand dollars the next step is to set up your a monthly bonus for example five percent and payroll tax rate for example twelve percent in the next section you can input the staff count or ft count so it will be one for example across all the years let me give you another example for example manager you want to start hiring in september 20 and june 28 will be the last date or fire date so you may see immediately 29 is gray here so you don't need to input any fts here so the annual base can be for example forty thousand dollars is three percent of monthly bonus with the same twelve percent tax rate you want to hide 2 in 2020 4 6 8 10 12 and steady 12 till the end of the model so additionally you are able to set up the annual salary rice position or annual salary growth it can be for example five percent across all the years or below you may see the calculation of your annual salary depending on annual base and depending on the salary rise percentage below you may see the total headcount calculation or total fte calculation by months calculation of salaries by months bonuses by months and payroll taxes and finally in income statement under salaries and wages category you may see the breakdown of your salaries and wages by positions and the amounts by months on the assets tab you have seven placeholders to set up your assets categories or groups first two lines are predefined which is land cost and other construction cost and these costs are linked to the table of construction costs so the land purchase amount is 2 million and everything else is in the second line item which is other construction costs obviously land cost should not be depreciable so useful time years is zero so you may see in here the calculation of capital expenditure and the closing net book value and no depreciation for land and for the second line item you can input some useful time in years for example 25 years so you may see the capital expenditure by months you may see the book depreciation and closing netbook value also you have five different placeholders for other construction costs or assets for example asset two so you may input some purchase date for example may 20 1 million of asset costs and 5 years of usable time so you may see below this asset 2 1 million spent in may and the depreciation for the next five years and close your net book value for the next five years so in income statement tab we can see the total depreciation amortization amount by months on the cash flow you may see the fixed assets capital expenditure by months and on the balance sheet we can see under fixed assets the closest net book value of these assets at the top right corner of the dashboard you have up to three placeholders for the loans first line item is predefined for the construction loan so the amount is given by construction cost multiplied by loan to cost ratio the launch date is connected to your construction start date so you can play with the full term you can 115 months for example interest only period count of months is when you will pay just only 10 months is for example annual interest rate 12 percent and one of the two types which is usual or nut let it be energy let's set up another line item as an example so other loan let's pretend the amount is half a million dollars the launch will be april 20 for example the full job 60 months is no interest period annual interest percent is 10 and the type is usual so you have once more line item to set up your depth assumptions and yeah that's how it works and in income statement you will find the interest expense in cash flow let me find interest paid total draft drop downs total debt repayments and to the balance sheet you may find your total debts closing netbook value on the top of the dashboard you have currency and denominator assumptions so let me show you how it works first of all you have a section with currency inputs and currency outputs if you would like to see all the model assumptions and outputs in the same currency for example in united states dollars you can just keep default values and keep exchange rate as a default as well but for example if you would like to input all the assumptions in united states dollars and see the outputs in euros you need to input the currency exchange rate in this case this can be 1.2 for example so what happens in all the reports now you can see the numbers in euros the same for charts and all statements like income statement cash flow balance sheet and all other reports another cool option is to use denominator by default you have 1 000 what does it mean that it means that for the all reports and charts you have one thousand dollars as a denomination means that this is in thousand dollars or euros in this case but if you would like to see it in millions you can just change this denomination and instead of thousands you will see millions you can use just one and you will see in dollars so no denomination applied 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 you