eSignature Hotel Business Plan Template Made Easy

E signature hotel business plan template

[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] the dashboard you can set up your court peoples or drivers for the model see the call financials and some chord charts as an output of the model first of all let's start the setup from available rooms but the assumptions you have three categories here from Monday to Thursday Friday Saturday and Sunday so once you click at any yellow cell you may see this notification window is explanation of what you are supposed to input here I believe it will help to understand to navigate the model easily so let's start the setup for variable rooms per day then put 120 20 and plus 10 rooms each next year so for Friday to Saturday and Sunday group it can be different amount or it can be the same let's pretend this is different 105 and 10 groups additionally each next year and for stand is the same the next step is to setup your revenue per room or ADR this is daily revenue so again you have the same groups and you have ability to set up the HR across the groups and across the years let's pretend it's $150 plus $5 each next year and it's imagine it's the same or all weekday groups the next step is to set up your other revenue which is events revenue and some bar and restaurant ready if you don't have for example events in your hotel you can just clean these assumptions if you have you can adjust count of events per year 200 quite a little tiny I didn't 4261 it in age and the average revenue per event per than 1000 each next er plus $50 in average per un another two revenue streams bar revenue and restaurant level just to top your total annual amount fifty thousand plus five thousand H next year like example another very important assumption this is Cox percentage obviously your for my revenue streams you have different margins so you can set up the Cox percentage for rooms it can be 20 percent for example because all the years or events it can be 25 26 57 which a 289 or stand here like that the next section which you can input on the dashboard this is working capital assumptions this is breakdown of your accounts receivable and accounts payable by four groups from 0 to 30 days from city 1 to 16 from 61 to 19 and from 90 to 120 so this is a simple breakdown 50% the first category training the second 25 search and 100% - previously groups you give you 5% if you input something wrong like this you have - 35 percent which is wrong so you just need to to keep the last category positive so once you set up all the revenue all the expenses wages assets assumptions you may review your core financials revenue breakdown by marine revenue streams profitability chart which include DVD revenue and EBT a percentage the cash flow broken down by operation investing financial and net cash flow and the cumulative cash flow also broken down by invested financial operating cash receipts and cash payments and the added cash balance [Music] when this is an ally to tap you can input mind seasonality assumptions first of all on the top you can input the rooms occupancies analogy it is broken down by weekdays the first category is Monday Tuesday Wednesday Thursday second Friday and Saturday and short Sunday so you can input your equipment seasonality across the monsters within the year for example 70 75 60 65 etc or if you don't need any seasonality you can just input 50 percent for example and copy this for Monday and Thursday group or you can copy this across all three groups so you'll have static seasonality across all weekdays and across all months the next step is to setup event seasonality means the breakdown of total count of events across the year for example 15 10 5 12 etc so to the right you have the check if it shows the red color means something's wrong instead total is not what hided percent now it is one credit and 7 percent so you need to adjust something for example each in percent to June so now you have total 100 percent and check is green means that everything is ok here and the final section allow you to input the other revenue seasonality assumptions which is broken down by bar revenue and restaurant rarely the same idea you can input assumptions across the year and see the chip if you don't need any seasonality you can just input 1/12 and copy this across all the manses in this case you have fled seasonality across the year this works as well for event seasonality assumptions [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 for fixed expenses or for variable expenses or for example for gravity 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 the 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 while 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 okay down by masses 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 KP is broken down by five years and selected TR by Monza's and the last part will show you the cash flow statement break down with the five years and twelve months for the selected year as well as charts with the same information [Music] on the top revenue tab you can see the revenue breakdown by rooms revenue events revenue by revenue and restaurant revenue also you may see the breakdown of absolute values by years and percentage allocation by years the same information you may see on the charts below so Hyrum you see the percentage allocation and the absolute values allocation on the revenue depths and monthly run rate charts we see the detail ATR which you can change here you can see the absolute values and percentage allocation of your revenue streams on the revenue bridge you may see the main drivers of your revenue growth between two years years are changeable also so you can setup starting from Twenty twenty one to twenty two twenty three on the left side of the chart to me see twenty twenty-one revenue on the right side to miss it when two twenty three revenue amount and in between you may see the main components of your revenue growth on the financial chart step you may see two sets of charts which is two years broken down by monsters and five years looking down by monsters whose main financial keep you eyes on the top you may see the revenue breakdown by manses booms revenue events revenue bar revenue and restaurant revenue the next set of charts will show you the operating cash flow broken down by cash inflow which is blue and cash outflow in orange color the next set of charts will show you the cash balance by manses the next will show you the a BGA as yellow line and the components which is revenue cogs and optics and the final couple of charts will show you the Abbott amount reckoned of diamonds is for two years and for the five years [Music] on the operational chat step you may see the productivity tip ice on two sets of charts so from the left side to me see two years broken down by manses and from the right side to me see five years broken down by manses so on the top set of charts let me see the average revenue per day which is a blue line and a orange line and we see the average topics per day on the bottom you may see the workforce productivity charts so as a blue line you may see the revenue per one employee and as an orange line you may see the ox per watt employee per month on the top expenses step you will find the breakdown of top for expenses categories and all other expenses collapse it into other category you may see the breakdown of absolute values broken down by years with the total below and also to the right you may see the percentage breakdown of these expenses the same information you may see on the charts below which you may find the percentage breakdown and the absolute values breakdown and a couple of charts below you may find expenses depth and monthly run rate for selected TR you are able to change this year and to me see the absolute values and percentage breakdown on the pie chart and the expenses bridge you may find the main drivers of expenses growth between these two years this year's us are also changeable so you may select the first year and here we select the last year and you may see that total expenses started in 2020 will change to total expenses in 2024 by this waterfall at 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 on the valuation tab you may see the calculation of company valuation based on the cost of equity which you may input here cost of Lords you previously inputted in on the dashboard calculation of resource share you may see here there is also tax rate and here we find the weighted average cost of capital in the valuation model there is two valuation methods which is a VGA 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 an elaborate free cash flow and PV and multiplicator valuation for this particular company [Music] on the beach market by step you can compare industry KPIs we're so small little KPIs means that calculated using the model assumptions so we have gross margin percentage profit margin percentage revenue for per available room in dollars the coupons are a percentage and gross margin for available rooms in dollars so on the yellow cells you can see the industry specific benchmarks and the cells are changeable for example you know that the gross margin for your country for your type of hotels is 60% so you may see to change it on the chart to the right and to the right of this yellow cells you may see the section with calculations means that these values are calculated based on the model assumptions the same information we see on the charts so gross margin profit margin revenue per available room coupons rate and gross margin per available rooms in dollars and all the charts your values calculated in the model are blue and industry benchmarks are orange [Music] the color cotton in the model is very simple you may change any yellow cell in any yellow sheet within the model this means that this yellow cell has some input assumption or driver which imparts the calculation within the model blue sheets means that on these sheets there are some charts reports and other information which can be useful for reporting purposes on the tabs without color you have some extra calculations related to revenue to depth equity inventory and narration which is needed for the report reporting additionally you have contents tab which allows 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 and we find one condition as well any header of this section is also clickable so you may click on for example Boca sets and you go directly to the step on the register you can input your head column by categories this higher Empire date is annual salary with ability to input different number of employees per years his annual salary rise percentage is monthly bonus tax rate let me give you a couple of examples let it be CEO you're going to hire in March 20 you're not going to fire him so the fire date will be December 24 so annual salary can be $50,000 and see the one CEO over the time so you may see one see fuel which is one could count starting from March to the end of the model which is December 24 also you can input five percentage of salary growth rate you may see the amount by years connected to this annual salary and ducted by Spaniel cellular eyes let's the top 10 percent monthly bonus and 5 percent of tax rate so you may see below the calculation of salary broken down by manses monthly bonus which is 10% and 5% of month taxes related to the payroll another option to be admin account which will start April which will grow to the end of the model whose annual salary of $30,000 that have been here number 1 2 4 6 8 and 10 head counts sue percentage of annual salary rose 5 percentage of monthly bonus 5 percentage of payroll tax rate so in here you may see total staff members which is 240 year number 1 2020 started from year 2021 you have 4 that six eight and ten the last year of the model again calculation of salaries for this tool in this case for what counts population of bonuses and calculation of monthly based taxes we see in income statement total salaries and wages and here you may see the total amount of bonuses payroll taxes and wages for this it comes [Music] well 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 but fees will grow as a percentage of this total revenue the same way you can input other variable expenses like for example 5% direct labor like 15 percent of total revenue and below we may see the calculation well 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] on the fix it expends a step will be input up to 15 line items for your fixed expenses let me show you how it works for example you have utilities you will start pay starting from March 20 till the end of the model which is December 24 let me see it here let's pretend periodicity will be daily use 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 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 here over here once you input this growth rate you will see that your utilities will grow or year over year let me give you a couple of other expenses types for example advertising let it start in March and finish in August 24 this will be on weekly basis this amount of $100 without any grows so start starting from March till August 24 you have four hundred dollars per month which is for weekly payments each month and that's it another option is be weekly for example 500 dollars you can start from July for example and you'll have two payments which is to be weekly payments within two months $500 multiply it by two you have $1,000 per month again you can input some 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 520 is amount of $5,000 obviously you should not input a Negro straits 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 join you 20 to the end of the model and it can happen monthly with one thousand dollars 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 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 of gross and the last option with annual payments or yearly payments you'll pay one time per 12 months starting from February to December 24 for each expense type we 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 to ounces and by exit expenses line items you may input up to 20 development expenses categories let me give you a couple of real examples for example to see the kitchen other development expenses you can input purchase date for each category example February and March you can input expansion two thousand dollars ten thousand dollars for example you can also use payment delay feature which means that if you will select for the kitchen - manses you will pay in April of the kitchen and for example three months is for other development expenses which means that you will pay for it in June 20 the total amount of development expenses connected to the asset step you see it here by default use the full time for development capex is five years let me see a calculation of depreciation and closing net book value we are also able to input up to six other assets example other capex is launch date for example June with three years beautiful time it's $5,000 cost let me see the calculation here so in this step you have calculation of capital expenditure book depreciation and close the net book value the total amounts you may see in income statement tab in depreciation section for the cash flow you can see the fixed assets capital expenditure and in the balance sheet you may see fixed assets as its closing Book value and capex repayments in case of prepayment prepaid these amounts and if you'll pay in some answers after we'll have kept xpl because it's a top two and three months experimental a you may see that we have kept expel I can also move here or select 0 manses in this case capex payable is zero you have just fixed assets amounts broken down by monsters also on the top of the dashboard have that's assumptions let me show you how it works so for the each depth are able to select the depth type there are two types in the model which is energy and usual and YouTube means that each monthly payment which consists your debt repayment plus interest expenses equal each months in case if you will select usual your my depth repayments will be equal parts and interest will be just interest on the depths close and balance let me give you an example how it works so you might input some amount of the depth the launch date term to be 60 ounces and interest can be 5% you may also input the grant which is just simple amount which is paid in some specific months and that's it no repayments no charms in terms of interest so all the calculations of the depth 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 parts income statement interest expenses the cash flow interest page tab drawdowns debt repayments and on the balance sheet we have the depth closing balance [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 pramana total equity and post magnitude of equity let's pretend that we have two founders on the 1 5 to 2 so total amount of shares for founder one can be 10,000 per found the number to 20,000 let's imagine that cost of share will be $2 and the data found in this February this means that investment for founder one is twenty thousand dollars were founded two 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 with the $5,000 we seeded before the series a total equity was $16,000 after is $65,000 and investor one who have 3.23 percentage of shares and the shares found one and form the two also diluted thirty two point twenty six and forty six point fifty two percentage you can also input some amounts for Sirius B HCSC the same way you can set up the date cost of share and up to five investors is up to five placeholders for number of shares the amounts of funding you may see in the cash flow in the ordinary equity risings and when we see the balance sheet 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 if 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 a new 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 this example we have denomination is 1000 mean that your outputs is denominated by 1000 you can select Millions let me see 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] [Music]