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