Learn How to Build Financial Forecast Models with ZERO Experience (Template Download)

Learn How to Build Financial Forecast Models with ZERO Experience (Template Download)


Hello and welcome to this video in this
video I would like to share with you a unique financial model on a business
model to forecast the income statement and cash flow statement of a business
this model can be used to do one year future forecasts either for a new
business or existing business as you can see we have tabs here new
business and existing business the structure of the both model is same the
ideal businesses for which this model can be used are small to medium-sized
businesses for example currently I am showing a business forecast for
an electronic store which sells up to five products it can be used for a restaurant
business a service industry business like consultancy as well but you can
always add more products to the model depending upon your business model this
kind of model is ideal for quickly forecasting 1-year performance and get a
realistic outlook about your profit performance and most importantly your
cash flow performance please keep in mind that this is a high-level or macro
level forecasting tool or a model you can always input more details and more
factors to make this entire model more comprehensive based on your business
model or type of business activities so let’s start with the overview and how to
use this model for your business first of all you should note that colors of
fonts used in this model so the blue color fonts are for the direct hard
coded inputs throughout the model cell with blue color can be used for
inputting values directly so here we have added inputs here the first date is
a direct input and various other places we have direct inputs with a blue
colour font the red colour fonts are used to change various key factors to
generate scenarios in the model these inputs are mainly drop-down selection
values and they are generated using data validation feature of the Excel so here
these are the red colour fonts and these are the key inputs which can be used to
manipulate the model don’t worry I will be explaining each of them in a moment
the red fonts are also used for error detection so everywhere there are
error checks and for that I have used red font as well the black color fonts
which are default fonts are used for formula values so all the black color
fonts are formulas and you shouldn’t touch them unless you know what you are
doing unless you know the formula very well the bold dark Brown fonts are used for the value which are derived from a separate
schedule in the model for example we are having three supporting schedules in this
model long-term bank loan a fixed assets and depreciation schedule, line of
credit or bank overdrafts schedule figures derived from this particular
schedules are interest on long term loan and loc and also depreciation are linked
here and they are represented in bold dark brown fronts the exception to this
font rule is only in one schedule which is trade of credit or a line of credit
scheduled here in this particular scenario even though we have a rule of
inputting these colors differently for different values here for a sake of
highlighting that you have a negative cash in the particular month I have made
a number of formatting using custom formatting so whenever there is a –
figure or there is a lack of cash in that particular month it will be
highlighted in red font so this is the only exception to our font color rule in
this entire model now let’s go through each element of this financial model so
on the top we have a key kpi’s which are linked to the particular result for
entire year and we’ll start with the sales so as I mentioned before this
particular model is designed for example for our consumer stores which sells five
products mobile phones smart TVs home theater system gadgets and accessories
and since this is a new business model this is a new business and if you are
doing any business you must have some feasibility analysis or estimate for
your sales each month so since we are starting from month of December and our
actual business operation will begin in month of January 2019 and so on so we
are forecasting what would be our first year in this particular business so
before we do that we have some analysis or some estimate for each product sales
figures in a month so this is a monthly sales we are expecting that we would be
able to do as a base at the most reasonable or most likely scenario would
be this much amount of sales which is $50302 combining
all these products and now based on this particular base sales since this
particular cells are colored in blue you need to manually enter
expected sales for this particular products all these products and also
for the entire month and based on that we’ll be manipulating our future performance
based on the linear or a selective approach now what is that? so let’s go
through linear sales so here we have percentages so linear means we have
$12,000 as a base sales for mobile phones if I choose linear selection or linear
method of forecast and if I make the linear forecast percentage of 0 each
month my sale would be same , ie $12000 but suppose I’m more optimistic and I’m
assuming that each month I’ll be increasing the sales for mobile phones
by suppose 10% the figures will change so based on this $12,000 10% add on that
will have $13,200 and you have 10% $ 13200, you will have $ 14,520 and you add 10% to $ 14520 in march, you will have $15972 and so on so when you choose a linear method and put your particular percentage here
they have a month on one growth of sales or your product as you can see here so
you can have a positive or negative you can have a zero as well so if I put for
example negative nine percent so each month based on this base sales my sale is
reducing by nine percent so this kind of linear forecasting may not be
practical in any business scenario in the real world we will not have any kind of
linear increase in sales each month so I have given an option to have a selective
sales as well so now in selecting for example if I choose selective for mobile
phones and I input figures here so this section which is a blue font again is a
hard key input so you can input figures and manipulate it so for example instead
of 10% linear growth I’m having a selectively growth of say 25 % in
month of January from our base sales so my sales expected as a base was $12,000 but
I’m expecting 25 % boost in the first month itself
so my forecasted sale for this particular month would be 15,000 which
is 1.25 or 25% above $12,000 but next one is our expert
a downfall so let’s make a -25% so based on $15,000 my sales will reduce to
$11,250 of previous month and so on I input the selective percentages here
and my sales will be calculated accordingly so this is a more of a realistic
approach especially when you’re seasonal sales products for example mobile phones
tend to have increased in sales when you have holidays so suppose your holidays
are falling in the month of December and November or December you can increase
your sales accordingly this applies to all the products as well so if you
choose selective and your input for Smart TVs would be in this particular
row and then accordingly you can choose the percentage 25%, -15% , 5%, -5%, 15%, – 25% accordingly your sales will be adjusted
but keep in mind that this particular percentages are applying to the previous
month not the particular base sales right ? the
basis is only used for the linear method of your forecasting and growth as you
can see these figures are already changing based on your inputs so this
way you can have many number of scenarios for forecasting your sales for
this particular business so this very comprehensive and also I would call it a
fantastic approach to forecast your sales growth in a short term like one
year now below that we have a cost of sales now cost of sales for this kind of
inventory business would involve three key components besides cost of sales so
if you know basics of cost of sales is derived from opening stock you add
purchases into that and deduct the closing stock so if I unhide here I have
done the same thing here so since this is a new business will have some
inventory so before start any kind of business new to some inventory so since
this is a fast-moving goods like mobile phones Smart TVs home theater and
accessories this we have to buy from some wholesaler right so before we start
with this we need some form of inventory so they assume here that we have a
$ 17,037 worth of Inventory and then that would be our opening stock for the month of
January since this is a blue font we have to input these figures directly but this
will be taken as opening stock in of January now we have purchases here we
have closing stock now for the purpose of modeling you cannot manipulate all
these particular figures at the same time and have some meaningful analysis
so we can only manipulate one particular KPI so in this case you’re manipulating
cost of sales by using a GP percentage so here we also have a base GP so since
in the beginning as I mentioned we have some form of estimation for sales
figures at the same time we need to have a some form of gross profit percentage
forecast as well so mobile phones will be having 25.5% as a base gross profit
Smart TVs will have a 17.5% same for the home theater systems 20.2% for gadgets
and for accessories will have the highest gross margin of 55% so the start
of modelling we need to have some form estimate for your cross profit as well
so we are calculating the cost of sales by reversing the gross profit percentage
here now as I mentioned this is inventory business so cost of sales
formula would be opening stock plus purchases minus closing stock but since
we can only forecast cost of sales we need to forecast the other two we have
the opening stock we have the cost of sales based on this manipulation so we
need to derive purchases and closing stock in real world scenario we will
have this kind of figures readily available from your books of accounts
for the purpose of forecasting we need to put some form of logic so I have
approached this kind of method where we know the cost of sales based on our
models and keeping 15% cost of sales as my closing inventory this also you can
change it according to your liking so and according to your business you can
make it to 25 percent or 20 percent as well for each month or a different month
so here I have input various percentages 15 % to 12 % so this would be my closing Inventory
based on my cost of sales now I have open inventory I have a closing
inventory now I need to derive purchase now you already know the formula for
cost of sales right? or cost of goods sold in this case so the formula is
opening stock plus purchases minus closing stock now we have a cost of
goods sold we have closing stock and we have
opening stocks we need to apply this formula in a reverse manner
derive the purchases for the month so here you can see the formula first thing
is cell I50 what is I50 is my cost of sales I am reducing the opening stock and
we’re adding that closing stock so this is the exact reverse of the original formula
so we’ll get the purchases for the month now we want to double check that this is
right so actual formula applying here so for entire products I’m taking opening
stock plus purchases minus closing stock this would amount to $35,289 which is
also SUM of this so this is matching here and matching here so this error
check ensures that I roll all logic is correct for the cost of goods sold now
if you have any kind of business for example you have a consulting business
for which you want to use this model you won’t need to create this kind of
section opening stock purchases closing the stock you’ll have a direct cost of goods
sold and you can manipulate it accordingly, we have total cost of goods
sold now based on our percentages and here also we have two approaches linear
and selective so if you choose linear for example let’s select linear and this
particular percentage is our gross profit manipulation for example if I
have a base cross profit of 25.5% for the mobile phones
and I want to increase the gross profit by 10% each month so here the
cost of goods sold will be calculated in reverse manner 25.5%
and a 10 % boost in your gross profit for the month of January
will increase your gross profit and reduce the cost of sales so accordingly
this figure is calculating and this would be a linear percentage so each
month gross profit will be increased by 10% and reduce our cost of sales
accordingly based on this base GP but as I mentioned before this may not be the
case for all businesses practically this figure may not be accurate esimate at all all
this would be a mistake if you assume that your gross profit will increase by
10% each month so for that we have additional scenario called
selective now you can go here and input your figures here so here you can
manipulate the gross profit which should be applied to the base GP this formula
is affecting directly to gross GP so for example in month of
January I’m assuming I will have 15% less gross profit compared to the base
gross profit 25.5% and accordingly this is will be calculated so all these ones
you can manipulate accordingly so this would amount to our total cost of sales
and we have a direct cost which is custom duty, international freight and
local Freight now since these costs are directly relate to our cost of sales so
you can say purchases you can directly add a formula like this so whatever your
consumption was you can apply the freight or you can always apply the same
to your purchases as well so however you want to model you can do the same but
for in this particular scenario I applied the same to cost of sales
obviously in the real world scenario we’ll have more accurate estimation of
this cost as well if I add other direct cost to cost of goods sold I’ll have a
cost of sales which is here and to derive cross profit I have to subtract
total cost of sales from the sales figure let’s hide this so $ 51,635, -$ 39 389 we’ll about $12,246 worth of gross profit in month of January and
gross profit percentages on sales so $12,246 which is our gross profit
divided by $ 51 635 our sales for the month will give us 23.7% worth of gross
profit percentage in month of January and so on now below that we have our
operating expenditure now this operating expenditure is mainly fixed in most of
the business scenarios but here in this scenario I’ve added one more dynamic
calculation which can be a practical scenario in any kind of business as well
so here the property rent is linked to the sales so to explain this better
let’s go to arrange each tab and this is the table from which I am deriving so
for example all these for cost which is property rent vehicle lease rent overhead
salary and utilities which are here are depending upon the sales volume of that
month so if so our total sales for the month is less than $ 51,000 our
property rent will be $1,800 he currently $500
overhead salary would be $1,500 and utilities bill would be $450 but
if our sale increased from $51,000 or it increased more than 51,000 but less than
55,000 all these for overheads will increase accordingly if it goes beyond
51,000 but less than $60,000 the overheads would be like this and above $60,000
the overheads ould be like this so by applying a Nested IF function here I
have calculated the overheads for this particular month based on the sales
value so everywhere I am selecting this particular value entire sets of all five
products and applying these conditions to derive the overheads all these four overheads for particular month right below that we have finance costs for LOC which is a
temporary loan finance costs for loan which is a long-term loan and
depreciation since this fonts are in dark brown and in bold they are coming
from a separate a schedule which we shall explain you in a bit now this would
amount to our total overheads and if you minus total overheads from the gross profit you will get the net profit so that will conclude our income statement our profit
and loss statement right below income statement we have a cash flow statement
now this cash flow statement is prepared considering direct method of cash flow
so here are some basic knowledge of Finance and Accounting you should know
that there are two method of cash flow used in business and reporting it is
direct method of cash flow and indirect method of cash flow, but the Direct method of Cashflow is more realistic and easy to understand for non finance people as
well so direct method of cash flow start with the collection of your
business so in our business we have five products which we are selling in this
particular scenario we have our two kinds of sales channels , one is cash sales and one is credit sales, so cash sales is a percentage as you can see here and you
also have a variable and fix so what do you mean by fixed? fixed means each month
we have a fixed percentage of cash sales of our total sales for mobile phones we
have a 10 percentage of our total sales would be our cash this each month so
whatever would be our sales, for example if you are selling $30,000 worth
of mobile phones in month of January the 10% would be $1320 and the balance
would be credit sales and this is a fixed sign
replying but if you unhide this we also have a variable scenario so each month
you can input percentage of your cash cells and accordingly these figures will
be calculated so each month you can input the figures or percentage of your
cash sales and this particular figure will be updated accordingly but before
that you have to select variable and if I choose 25% so 3,300 would be my cash sales
for the month of January which is 25% of total sales the balance would be my
credit sales, so you have a fix and variable both options which you can manipulate and
create many scenarios for your business now below that we have a credit sales and
these are the credit days which we are offering to our customers so in any
business if you want to do or you want to grow your business you need to offer
some form of credit to your customers so in our case we have total 4 blocks of
credit if I choose 0 days it will be total cash sales so it will be zero
credit or you can say that customer is paying within the month if you choose 30
days the customer will pay after 30 days so for example if we had a 9900 worth of
credit sales for the mobile phones in month of January if you give 30 days
guided sales to all your customers you’ll be receiving the money same in
month of February and accordingly if you change to 60 days that money will be
received in month of March if you make it 90 days you’ll be receiving the same
amount in month of April so you need to manage your credit period very
effectively otherwise your cash position will be always negative and you’ll be
end up being lot of interest to your bank overdraft so below this is a
collection from your credit sales so as I explained before whatever your
credit period is based on this your collection will be impacted now if you
sum your collection from credit cells and cash sales here you will get the
total cash inflow for the month in row 109 here now below total cash inflow we
have a cash outflow section here we are manipulating purchases so if you
remember we did calculated our purchases by reversing the formula of cost of
goods sold so if I unhide here so this particular section is our purchases so
this we have to pay suppliers and how you are paying is
modelled here so same like our sales section we have a certain percentage of cash
purchases so it can be fixed or variable so if you unhide this we have a option
to do a fixed percentage of purchases which is whatever percentage you want
like 10% 20% 25% or you can go with variable where you have to input here
directly hard-code your values here and according to your cash purchases will be
calculated here and the balance would be your credit purchase, in real world
scenario and to have a best cash flow performance for any business, you need to
obtain as much as credit period possible the standard days I assume would be 60
days and your credit period from supplier should be always more than your
credit you giving to your customers if the case is reversed then you’ll have
the worst cash flow performance from the business so in this scenario let’s model
it to 60 days for our majority of purchases now here also the calculation
is working accordingly for example if you are buying 30 days for mobile phones
your credit is 30 days from supplier you’ll be paying for the January you will be
paying in February and so on if you have 60 days for the Smart TVs your
$5210 which was the credit purchases you’ll be paying in the month of March
and accordingly it will be calculated so as I mentioned your credit period from
supplier should be higher always higher than your credit period you are giving
to customer this should create a positive cash flow performance
throughout your business and throughout your business model now this would be
your payment to suppliers this figure and your Total cash outflow and
payment to suppliers would be each month calculated here in row 137 so below that
we have a direct other cost payment and overheads and finance cost so other direct
costs are custom duty international freight and local Freight so custom duty
you must pay at the time of you purchase or you import the product so this will
not have a credit from the government body whoever is collecting the custom
duty if you have it you can model it accordingly but here I have kept 0 days
that means whatever customer to incur for the man will have to be settle in
that month so that would be a cash outflow in that particular month now
when did I say purchases and local Freight you can have supplier a credit
period granted to you here I’ve assumed 30 days carried from international
freight forwarder in 90 days for the local Freight so accordingly whatever
expenditure incurring you can pay it later on this will increase your overall
cash flow performance now for indirect overheads we have four kind of indirect overheads as I explained before they are attached to your sales for the lease
rental for the property throughout the world it is collected always in advance
but how much at once you can always figure it out with the landlord of the
property so here in this case if you put monthly whatever expenditure incurred
will be paid out monthly but if you choose quarterly the land all will be
collecting your rents quarterly net ones so if you choose semi-annually so
whatever end you have for entire year will be collected twice in a year so if
it is a calendar it will be January and first July now for lease rental you can
always ask for credit from your supplier so you can choose whatever credit you
have so if you have more credit better you’ll be having a better cash flow
performance throughout the year now salaries and wages will have to be
settled each month so none of your employees will stay if you have a credit
period with them right so it doesn’t make sense so we need to set up the
salaries each month similarly for utilities expenditure
unless you have a specific scenario where you can get credit this has to be
settled each month so it will be cash outflow as you incur you have to pay it
out now below that we have finance costs from the long term loan which is coming
from a schedule so this is interest I am showing it here because what the
definition of cash flow from operation you to include interest as well so this
would amount to your total operating cash flow or I should say operating cash
outflow and right below that in Row 153 we have cash flow from operations so this
is nothing but your total sum of cash inflow and outflow so for the month of
January we have a negative position of $ -12,865 because we are
starting business and we haven’t collected
anything from our credit sales right? below that in row 154 there is a KPI
called OCF conversion so how much your cash flow performance of the month is
compared to your net profit should always try to achieve percent of your
profit net profit as a cash flow from operation in ideal environment so in the
month of January we have negative performance as you know because we are
starting the business both fathers have second month except that in the month of
July we always have a better cash flow performance then our profit which is
more than hundred percent in order also we are doing very well now this will be
depending upon how you are set up your credit period so you have to keep that
in mind and you can model this out now below that we have a capex spend so this
is more of a bankbook a scenario of a bank book for our business so initially
we have spent $ 87500 on capex because this is a new
business so we need to bring in some capital assets the schedule for which is
down here I will explain you in a bit now any sale of asset with it will be
cash inflow and repayment of loans so this is the principal portion of the
loan this is the interest this is the principal so both we are settling and
you are showing it separately for the purpose of deriving cash flow from
purely operations and this is the other cash flow so that’s the reason I am
separating the same now there is a finance cost on our LOC which I will explain
you like why it is coming and there is credit line inflow or line of credit
inflow and payout as well so that I will explain you in a moment now this all
will amount to total net cash flow for the month so what a month of January we
have a net cash flow $ 3523 but most of them is positive because of you are
bringing the line of credit temporarily because from the operation we did $-12865 cash flow and here is the opening and closing balance of
your cash and this is the error check which should always be 0 now let’s go to
our equity and loan schedule which is our the first schedule this our first schedule section
and we brought in $52000 worth of our own capital and we
borrowed the rest so how we are arriving this figure? so basically my capex requirement was $87,500
and I needed inventory worth of $ 17,037 as I’ve seen before so some of
these two would be $ 104537 so some of these two is $104537 that is the total
capital initially I need to start this business so 52,000 I brought in my own
money so the balance would be my loan and that interest rate on loan is 4.5%
this is a blue phone so you can change this percentage accordingly this is a
yearly interest and total loan term is 3-year so how to repay this loan over
period of 3 years now this is the loan schedule so this is the opening balance
since this is a new business is a new loan and I’m using a payment function or
PMT function to derive the installment of the loan which is inclusive of
interest and principal so if I calculate the interest on the loan outstanding
I’ll get these figures which are we are linking in the P&L statement above or
income statement I should say so 197 is coming from this particular schedule
from row 179 and the balance of principal in Row 180 is $1563 which
is the total installment which includes interest minus Interest will get the actual
principal payment of the loan which we are showing here so it is coming from
this particular cell or I should say row 180 and balance would be our outstanding
loan and it will be carry forward at each month now this is the most complex
and a little bit tricky to understand trade finance or line of credit or I
should say overdraft schedule we have 7.5% % because usually any
loan which is short term will always have a higher interest than the
long-term loan now since we are starting the business we spend all our cash in
our capex in inventory so we had a zero cash at the start of business this is
our assumption for the month of January as you know we have done a negative cash
flow from the business in row 153 so cash flow from operation is $-12865 which total negative cash performance and $5,000 is
our minimum cash balance to maintain our business so this figure we need
all the time in our business to sustain our business to pay our fixed commitment
even the month is really bad and we didn’t collect money or we didn’t sell
well we need to have this amount of cash in our business all the time otherwise
you may not run our operation or we can open our shop even so you can assume
that this is very critical for our business to run at the basic level now
since we need $5,000 and we have negative cash flow so total we need is
actually $ 17865 so whatever negative we did plus we need
positive cash right five thousand so we need total $ 17864 and this we will be borrowing from our bankers so we have agreed a
temporary line of credit or bank over draft that bank will finance us
immediately when there is a need for cash so we have need of cash in month of
January which is $ 17865 so that will be our
cash inflow here in here which is coming from this this particular balance so
here I’m calculating what exactly I need and it is coming here as cash inflow
that’s why we have a positive cash balance of $3523 in the
month of January now each month this will be evaluated
whenever we have a positive cash and which is above $ 5000 we
don’t need a line of credit and also we have access cash we can pay it off so
for example in month of February we have a positive cash flow from operation so
$ 20313 and we have met our commitments like we have
paid the loan and then whatever cash remaining we are paid off towards our
line of credit so we don’t have to incur interest so this interest of one one two
we we incurred because we have borrowed this amount so the interest is whatever
loan we borrowed seven point five percent divided by twelve so that is a
monthly interest one one two so whenever we have a positive cash flow and we can
able to repay the loan we should repay in order to reduce our interest cost
that is what we are doing each one this is the line of credit schedule and apart
from January we don’t have any balance so interest is also nil right below it
we have a fixed Assets and depreciation schedule now this schedule is a very
macro level or a basic level schedule you can manipulate this kind of schedule you
know prepare a very comprehensive schedule I have a comprehensive schedule
already prepared which is called advanced Excel model for depreciation
accounting using straight-line method that is a separate product there’s an
entire course on how to work on that schedule and also you can download this
should do it independently without Anna Lindh with the course but if you enroll
into the course you will get the schedule free so I have link in the
description of this video where you can find those schedule or that course and
you can use that particular schedule to do a comprehensive calculation for the
depreciation and fixed assets addition deletion for profit or loss on sale of
assets and many other factors so here we have this basic assumption for this
particular schedule so here the first the assumption is the addition deletion are
in the same section so there is no separate section whenever you have
addition or deletion I am using the same section same row as you can see for in
individual items so these all furniture and fixtures computer and other assets
so these are the initial assets which is 87,500 which are spent and the rate of
depreciation is a percentage term but since this is a straight line method you
can input directly the useful life as well so this is the breakup of our fixed
asset which you have bought and this is the yearly depreciation and whenever
there is a addition these are positive and there is a deletion that is a cross
cost of the asset is deducted it is a negative figure and in row 204
whenever there is a deduction that means you are selling the asset so to $50.00
we have sold that is a computer equipment we have sold for $ 250 and the gross
cost of the same is $500 so we have to reduce it so the depreciation will also
reduce in that particular month accordingly if there is an addition here
there is addition of computer as well and in July we have added more
furnitures but we have sold furniture in September so when there is a selling of
asset we have to have the cell value of the same which should we cash inflow now
the second assumption is that the duration is suppose a large the start of
the month to simplify the calculation because we are not calculating parity
depreciation here we are assuming that this events are happening that is
addition deletion it start of the month in the last assumption is a powerful
loss on the asset whenever you are selling very ignoring in ideal world
whenever there is a profit or loss on sale of asset , you have to add it in your P&L
statement but here I ignored it the the reason why because I don’t want to
complicate overall exercise of this model this model is to have a overview
of your business over one year and the purpose of this model is to have a
operational performance forecast not your non business or non core activities
like your business is to sell these Goods right these four products you need
to sell and optimize your overall performance your business is not to sell
fix assets so that’s the reason the detailed calculation for the fix assets
and it’s individual components like profit and loss on sale fixer is ignored
but as I mentioned if you want to go in more detail about depreciation
accounting and you want to get the most advanced straight-line method
application model I will leave a link in the description where you can get the
same now this is the depreciation which is a straight line depression which is
fixed depending upon the addition or deletion if there is a deletion the
depreciation is adjusted accordingly and these figures are linked here in the
model above in the P&L statement as you can see if you go up this is row 74
where the depreciation calculation is coming from this schedule now that
concludes the overall cash flow statement and how you can model the same
now traditionally financial modeling exercise is done with three statements
which are CORE to the any financial modelling size for example these three
statements are income statement balance sheet and cash flow statement now in
this exercise of traditional financial the modeling we need to calculate the cash
flow or cash flow performance using indirect method of cash flow which is
more logical approach if you know the financial accounting basics in our model
exercise we have used cash flow from direct method or a direct method cash
flow here I have used the same because it’s easier to understand for any layman
or any non finance or a non technical finance person so anybody can understand
if you have a basic business knowledge as what is cash coming in which will be
your cash inflow from customers how much you’re going to collect and how much you
are going to pay but in direct measure of cash flow is
little bit different where the cash flow starts with the net profit now somebody
can argue that the model that is before this up to this particular level is
incorrect because you don’t have balance sheet or your overall cash flow from
operation or net cash flow is not correct to prove that their overall
matter of cash flow that is this particular method of direct method of
cash flow is working fine is accurate I have prepared indirect method of cash
flow and balance sheet for these businesses well this is completely
automated based on inputs of entire exercises done entire calculation is
done and there are error checks as well which are linked to this mean error
check so here we are starting with the net profit of the business and
accordingly we are adding depreciation we are adding the interest on long-term
loan so here I have a comment why I’m adding it so you can read through that
and then we have a movement so indirect method of cash flow takes the movement
from the balance sheet so here I prepared a balance sheet based
on our assumptions in the model and accordingly I am calculating the balance
sheet and indirect method of cash flow now I’m matching the each and every KPI
that is net cash flow so first thing we are matching is the closing cash balance
which is matching and second is net cash flow so this net cash flow from
operations for indirect method will always match with the direct method so I
Cell I230 is the net cash flow from indirect method of cash flow which is should
match which ideally should match to our direct method of cash flow so that is
cell I163 that is I163 so if we go up a net cash flow is matching
similarly operating cash flow is also matching so $12865 if you remember was our negative cash flow from operation in month of
January which is matching and accordingly each month so this ensures
that our method of cash flow which is direct method of cash flow even though
it is not directly linked to the net profit of the business is correct and it
makes sense all these errors are being summed up here and should always be zero
or very close to zero figure so if it is like around five dollars you should not
go and investigate that much but if it is more than hundred
dollars or every showing $15 $25 you should definitely investigate to find
out whet what is wrong going on here now here on the top we have this key
kpi’s which are I used shapes Excel shapes and I have linked the formula
from here so directly cell link is there I have used some custom formulas with &
percentile I would use the concatenation feature of Excel and accordingly
whenever you change anything you see the changes here right on the top so these
are your key KPIs you can always add more kpi’s according to your business
here as well now there are various control checks in these models to ensure
that overall model is performing at a highest accuracy you need to have this
all these values to be 0 so everywhere we have a checks in place and
accordingly your overall error check should always be 0 so everywhere
wherever there is a check I have made a sum and user absolute function to know
that overall model is accurate and there are no errors so this was the model for
the new business exactly same format is forward for the existing businesses so
you can see the format is same only difference is that whatever your
existing business data is you need to bring in here so this sale is not any
assumed sales this is the actual size of your previous month for December and
these figures are also actual figures from your actual data so everywhere
there is a blue figures in you to input it for your existing business and most
importantly for the schedules you need to input your actual capex figures here
and also your balance sheet should be actual as well so the figures for
indirect cash flow also should come in from your actual figures and balance
sheet as well as I mentioned so wherever there is a blue phone you need to input
actual figures from your existing books of account and your models should work
just fine just as explained in our new business model the KPIs are same and
these are the ranges which you can use so this is the cash purchase ratio which
were selecting so you can input many other factors or any other factors you
want this is the interest rate this is the frequency for your rentals this is
the term cash or credit this is a linear or selective for the growth fixed
and variable for your cash purchases which are amount of your cash purchases
fixed or variable these are the days for your credit so I’m calculating the days
and accordingly the formula is using offset and we look up function based on
this data now this section of schedule is prepared to calculate the rentals so
this figure of rental is coming from our model so this is for the new business
model this is for existing business and accordingly we are using the simple sum
formula to have a quarterly approach or semiannual approach for your rental
payments this particular table I already explained this is the our overheads
based on the sale value or sale volume this is the dashboard section for
existing business so I have prepared this kind of custom charts and this
dashboard is for the existing business so this particular dashboard is driven
by the data from the model itself so this is the data preparation section and
these are the charts so ONLY difference between the two dashboard is
that this has a dark background and this has the white background I should say
very light gray background so the first chart shows the performance of the
business that is sales in GP so this is the actual sales for all the products
and this is the gross profit in blue bar below that we have a gross profit
percentage a net profit percentage so each one how much is your percentage of
your gross profit and your net profit now this green bar represents the
highest profit in entire year so in month of September we had the highest
gross profit for all the products and we had the lowest cross profit in the start
of the year because it was our first month of the business so obviously the
performance was not the best so this particular chart is dynamic accordingly
it will change so in that profit instead of September we have the highest net
profit in month of December so can usually see how your overall modeling figures are working and this right side chart is to show individual
products so what is the performance of your individual product so mobile phone
smart TV home theater system gadgets and accessories so what is their sales
overall for the year and gross profit so sales in GP and the sales and GP
percentage exactly same conditional formatting has been applied so ever
there is a highest it will be shown in the green bar and the lowest will be the
red bar now for this particular scenario the cash flow performance chart you have
used collection payments and sales so how much is your sales compared to that
how much is your collection how much you are paying so your sales chart should
always be up and collection chart should be right next to it and your payments
chart should we always have some substantial difference to have a
positive cash flow throughout the year as you can see we had the highest cash
flow in the month of February and the lowest in month of January but then the
second lowest was in month of July this donut chart represents the share of each
product how much we are selling so the values are converted to percentage and
you can see that highest sale is for the mobile phones and then the accessories
and then we have gadgets and then Smart TVs and at last we have home theater
system you can add your own charts as well and if you find any difficulty
modifying this chart or you want to create your own dynamic chart please let
me know in the comment section below so these are the data which we are deriving
from the model itself so this is the data I have converted to thousands and
I’m calculating lost profit so individual products now in this
particular chart we don’t have individual net profit across profit
right so we didn’t model that way but you can always do that you can always
create additional rows to derive gross profit and net profit for each product
but for sake of simplicity since we need is only for this particular chart I have
just allocated each or at for example we don’t have individual overheads for the
direct cost so if we go here this cost this cost are not individually
segregated I should go to the profit loss treatment and show you here that
this cost are not individually broken down for each product but you can do the
same so if you apply this percentage to mobile phones accordingly you will have
the breakup of each cost similarly, for overheads, you can allocate
the same based on the direct cost or cost of sales ratio which I have done
here already so whatever the cost of sales is I’m deriving your figure there
is a percentage I am segregating the same to each individual products so that’s
how we are deriving individual performance for gross profit for each
product and also net profit the dashboard as I mentioned for the
existing business is working in the same fashion the charts are same the logic
behind is the same only that background is dark and this other function I have
used in preparing this model so it’s quite simple there are no fancy or
really complex function only function in to take care is the offset function
which I have used because offset function is volatile if your model is
quite huge your spreadsheet will be slower if you use too much offset fucntions I have used offset function only to derive this dynamic credit days and adjust our
collection based on the days input so if you go here in this particular scenario
I am using offset function in conjunction with vlookup function that’s
the only place I’m using offset function wherever there is a day’s credit I am
using the offset function to get the accurate collection according to the
credit days now one more thing you should note that there is no circular
referencing here because I designed the model in such a way that we can avoid
the circular referencing which is sometimes important in financial modelling
exercise especially when we have situation of rolling credit or line of credit
which we have here but I have designed the model in such a way that I avoid
that particular scenario so the if you know about circular referencing it can
cause lot of errors in the model if you apply the circular referencing in your
financial model so ideally in business world whenever there is a financial
model any user or if you represent your
financial model for the purpose of analysis to a banker or prospective
investor if he sees the circular referencing in the model
he might reject your model without even looking at it so that’s the reason I
have avoided circular reference again this entire model exercise now
since we have completed the overview of the model let’s see this model in action in
check and test the various scenarios so here let’s manipulate the sales and see
how these KPIs are changing if I change this to selective my overall revenue is
changing by a very substantial amount if I change linear the revenue is going
down and gross profit a net profit is affected and cash flow is also affected
similarly if I change the cross profit so for example if I change this to
linear and increase my gross profit by 20% I will have increase in gross profit
net profit now for the purpose of cash flow if you change these credit days to
the minimum possible which is 30 days we will have the ultimate cash flow like
the best performance ever I should say of $237K which is way beyond our net
profit but that is not always the case so let us make it worst possible so much
making 90 days here 120 days here 120 days here and 150 days here so in this
case from $237K we have gone down $3557 only our overall Interest cost on
LOC has increased so you can see we have $2714 you are paying in the
interest cost only this is how you can see how your credit period and how your
collection on timely basis is most important for your cash flow performance
and overall health of your business so this should never be the case you should
always try to keep a very tight leash on your creditors and ensure that they are
paying on time and you should always design your business model in such a way
that you shouldn’t extend too much credit to your customers and give them
special discount for an early payment and employ different tactics to collect your
debtors really fast this will improve overall health of your business
very very fast similarly if you change your credit period to 30 days everywhere
I just copy this down your overall cash flow will go down and as I mentioned
before your credit terms with your supplier should be the highest you
should stretch your credit period as much as possible without spoiling the
relationship of course with your supplier so you have to be rational
about it so try to negotiate that if you’re going to buy more products or
you’re gonna buy more volume in a particular, you need a special
credit from them so that way you can manage your cash flow accordingly so as
you can see by changing few key important factors like credit days and
also credit days from suppliers how your all our business is changing and
how your performance is so since we are in a good cash flow position your
overall interest on LOC has gone down substantially compared to the worse
situation we saw earlier so this is how you can model your business performance
for a short-term period of one year using this kind of cash flow model I
should say a business model you need not to be accountant to use this model as I
mentioned you need to have a basic knowledge of your business that is a
profit and loss income expenditure and if you were some basic knowledge of
accounting it will be much easier to understand this but as I mentioned that
is not a prerequisite to use this model so anyone can use this model this model
is designed for non finance and non technical finance people or non
technical accounting people I should say but as I mentioned before we have a
cross-check with our indirect method of cash flow and also balance sheet so as
long as this error checks is nil your overall model output is accurate and
your data is reliable so you can present this model to a prospective investor of
if you are seeking a loan if you’re taking a venture capitalist you can present
this model for a short time and get your loans so if you want to get a short term
loan from a bank or any prospective lender you can use this kind of model to
quickly forecast your overall business the performance over next year and show the
prospective lender or investor how your business will look like in one year so
now the question is how you get this model so link for the same in the
description of this video so go to that link and you can download this model and
if you have any question in using the model of you want to adjust it I
will support one-time changes in this model so if you have any question or
clarification I will support you also in charts if you want to man you plate or
want to adjust it I will support it one time now if you have any questions or if
you need any clarification you’re welcome to ask in the comment section
below and you can find my website link you can contact me directly by the
contact form please let me know any questions you have regarding the model
any logic or any suggestion you have to improve the model I’ll be happy to take
that input and improve the model now if you want to create this kind of model
step-by-step or to learn how to build this kind of model from scratch there is
an entire course which I have develop the link for the same is in the description
of this video so go there and check it out there you will find this model as
part of the course so if you are enrolled into the course you’ll get this
model as a free resource and you can also learn how to build this model from
scratch so you will know each nitty-gritty and each formula how I
applied and also course includes the basics of all these functions as well so
I’ve explained the basics of these functions so if you don’t have advanced
knowledge of this function you’ll have the clarity as well also I’m preparing
these charts in their course step-by-step so you’ll learn the
advanced charting techniques as well thank you very much for watching and let
me know if you have any questions so if you have any comment provide your
feedback in the comment section below thank you so much

You May Also Like

About the Author: Oren Garnes

Leave a Reply

Your email address will not be published. Required fields are marked *