Build a Dynamic 3 Statement Financial Model From Scratch

Summary

In this instructional video, Kenji guides viewers through creating a dynamic three-statement financial model linking the balance sheet, income statement, and cash flow statement. He details the process from organizing the income statement with key assumptions to building out the other components and ensuring the model's interconnectivity. The model serves as a foundation for financial analysis and valuation methods like discounted cash flow and M&A modeling. Kenji also offers a free downloadable file and touches on formatting and formula techniques in Excel.

Takeaways

• 📊 The video provides a step-by-step guide to creating a dynamic three-statement financial model that links the balance sheet, income statement, and cash flow statement.
• 🔗 The model begins with organizing and building an income statement based on a set of assumptions, which is essential for financial analysis and serves as a foundation for valuation work like discounted cash flow or M&A models.
• 📈 The script demonstrates how to format and calculate the income statement, including adjusting for items like revenue, discounts, and cost of goods sold (COGS).
• 🏢 It explains the importance of distinguishing between historical and estimated figures in financial statements, particularly when dealing with balance sheets as they represent a snapshot at a specific time.
• 💻 The video script includes Excel shortcuts and functions like `EDATE`, `ALT`, and `CTRL` combinations for efficient data manipulation and formatting within the financial model.
• 🔑 Key financial concepts such as EBITDA (Earnings Before Interest, Taxes, Depreciation, and Amortization) are introduced, highlighting their significance in profitability measurement and company comparison.
• 🛠️ The script covers the creation of a depreciation schedule, which is crucial for allocating the cost of tangible assets over their useful life, affecting the income statement and balance sheet.
• 🔄 The importance of linking the three financial statements is emphasized, ensuring that changes in one statement are reflected in the others to maintain accuracy and consistency.
• 💡 The video mentions the use of assumptions for accounts receivable, payable, and deferred revenue, which are dynamically calculated as percentages of revenue or COGS, affecting the balance sheet.
• 📉 The script details the process of forecasting future financial figures based on historical data and percentages, which helps in projecting the company's financial health.
• 🌐 The video concludes with the creation of a cash flow statement, which is vital for understanding the company's liquidity and is used to reconcile the balance sheet's cash figure.

Q & A

What is the purpose of creating a dynamic three-statement model?

A dynamic three-statement model is used for regular financial analysis and as the foundation for valuation work such as discounted cash flow, LBO, or M&A models.

What are the three main financial statements that the video focuses on linking together?

The video focuses on linking the balance sheet, the income statement, and the cash flow statement.

What is the first step in building the financial model as described in the video?

The first step is to organize and build the income statement using a set of assumptions.

How does the video suggest formatting the income statement in Excel?

The video suggests using Excel shortcuts like Alt + H6 for indents, Ctrl + B for bold, and Alt + HB for borders to format the income statement.

What is the significance of using 'EDATE' function in the income statement?

The 'EDATE' function is used to automatically calculate the end date for each year, which is useful for a statement that covers a range of periods like a full year.

How are the assumptions for the financial model represented in the Excel file?

Assumptions are represented in blue and are hardcoded, meaning they are typed in directly without being part of a formula.

What is the reason for making certain assumptions a percentage of revenue?

Making certain assumptions a percentage of revenue allows for dynamic calculations that adjust based on the revenue, ensuring the model remains proportional and accurate.

Can you explain the meaning of EBITDA as mentioned in the video?

EBITDA stands for Earnings Before Interest, Taxes, Depreciation, and Amortization. It is a measure of a company's profitability that allows for comparison between companies by omitting the effects of taxes and other non-cash expenses.

What is the role of the 'Fixed Assets Schedule' in the financial model?

The 'Fixed Assets Schedule' helps in calculating depreciation and amortization, which are then linked back to the income statement to account for the cost allocation of assets over their useful life.

How does the video suggest handling the interest expense in the income statement?

The interest expense is handled by linking it to the balance sheet where the interest payment is calculated based on the interest rate and the debt amount.

What is the final step to ensure the accuracy of the three-statement model?

The final step is to create a cash flow statement and then use it to fill in the missing cash balance in the balance sheet. The accuracy is confirmed when the balance check shows all zeros.

Outlines

00:00

📊 Introduction to Building a Three-Statement Financial Model

Kenji introduces a tutorial on creating a dynamic three-statement financial model that interlinks the balance sheet, income statement, and cash flow statement. He mentions a downloadable file for practice and outlines the steps: organizing the income statement with assumptions, creating schedules for complex items, building the balance sheet, and linking all three statements. The model's utility is highlighted for financial analysis and valuation work such as discounted cash flow, LBO, or M&A models. The Excel file structure is also briefly discussed.

05:01

🔍 Formatting and Filling the Income Statement

The process of setting up the income statement begins with formatting tips like removing gridlines and customizing date formats to represent fiscal years. Kenji formats the income statement for a hypothetical lemonade stand startup, detailing steps like indenting items, adding borders, and adjusting cell formats. Assumptions for revenue, costs, and taxes are hardcoded in blue, signifying static values. He demonstrates how to make these values dynamic by linking them to the income statement, using formulas and formatting to reflect percentages of revenue.

10:02

📈 Completing the Income Statement with Formulas

Kenji continues by filling in the income statement with formulas for calculating gross revenue, discounts, net revenue, and costs. He explains the use of absolute and relative cell references in formulas and the importance of formatting for clarity. The video script includes step-by-step instructions for calculating COGS, gross profit, operating expenses, and EBITDA, emphasizing the significance of EBITDA in comparing company profitability. The process of copying and pasting formulas for automation is also covered.

15:04

🏭 Depreciation and Amortization in the Fixed Asset Schedule

This section focuses on the fixed asset schedule for calculating depreciation and amortization. Kenji explains the concept of depreciation as a cost allocation over an asset's useful life. The script details the setup of a depreciation table, including the allocation of costs for various assets like a lemon crusher, ice machine, and refrigerator, and how to handle assets with different lifespans. The process of linking the depreciation back to the income statement is also discussed.

20:07

💼 Building the Balance Sheet and Linking to Other Statements

The script moves on to constructing the balance sheet, starting with formatting historical and estimate figures, and explaining the difference between actual and estimate data. Kenji covers how to calculate totals for assets, liabilities, and equity, and the importance of the balance check for accuracy. He also details the process of creating dynamic links for accounts receivable, payable, and deferred revenue based on percentages of revenue or COGS, and how to forecast these figures for future periods.

25:11

🚀 Finalizing the Balance Sheet and Preparing the Cash Flow Statement

Kenji wraps up the balance sheet section by discussing non-current assets, accumulated depreciation, and net fixed assets. He explains the handling of debt, equity, and retained earnings, including how to calculate interest payments. The script also introduces the creation of the cash flow statement, starting with the net income and operating activities, and explains the rationale for adding back depreciation as it's a non-cash charge. Changes in accounts receivable and payable are discussed in the context of their impact on cash flow.

30:17

💼 Summarizing the Cash Flow Statement and Model Validation

The final paragraph deals with completing the cash flow statement by addressing investing and financing activities, including CAPEX and debt repayments or borrowings. Kenji explains how these elements affect cash flow and the importance of ensuring the balance sheet and cash flow statement are consistent. The script concludes with the validation of the model by checking that the balance sheet balance is zero, indicating correct calculations throughout the financial model.

Keywords

💡Dynamic Three Statement Model

A dynamic three statement model is a financial tool that interlinks the balance sheet, income statement, and cash flow statement to provide a comprehensive view of a company's financial performance over time. It's dynamic because it can be adjusted for different scenarios and time periods. In the video, the creator walks through the process of building this model step by step, starting with the income statement and eventually linking it to the balance sheet and cash flow statement.

💡Balance Sheet

The balance sheet is a financial statement that presents a company's assets, liabilities, and shareholders' equity at a specific point in time. It is part of the three statement model and is used to assess the company's financial position. In the script, the balance sheet is formatted and filled with historical and estimated figures, showing the company's financial snapshot at the end of each year.

💡Income Statement

The income statement, also known as the profit and loss statement, reports a company's financial performance over a specific accounting period, typically a fiscal quarter or year. It shows revenue minus expenses to arrive at the net income. In the video, the income statement is constructed using various assumptions, and calculations for revenues, costs, and profits are demonstrated.

💡Cash Flow Statement

The cash flow statement provides information about a company's cash inflows and outflows during a period. It is one of the three core financial statements and is essential for understanding the liquidity of a business. In the video script, the cash flow statement is created to reconcile the changes in cash and to ensure that the model's financial statements are consistent with each other.

💡Capital Expenditures (CapEx)

Capital expenditures refer to the funds a company uses to acquire or improve its fixed assets. These are significant investments that a company makes to maintain or increase the scope of its operations. In the script, CapEx is used to calculate depreciation schedules and is an essential part of the balance sheet and cash flow statement.

💡Depreciation

Depreciation is the systematic allocation of the cost of a tangible asset over its useful life. It is used to reflect the decline in value of assets over time. In the video, the process of calculating depreciation for various assets like a lemon crusher, ice machine, and refrigerator is explained, showing how it affects the income statement and balance sheet.

💡Assumptions

In the context of financial modeling, assumptions are the预估 or estimates made about future conditions that affect a company's financial performance. These might include sales forecasts, cost estimates, and market conditions. The script describes how a set of assumptions is used to project the income statement, balance sheet, and cash flow statement.

💡EBITDA

EBITDA stands for Earnings Before Interest, Taxes, Depreciation, and Amortization. It is a measure of a company's financial performance and is used to compare the profitability of companies. In the video, EBITDA is calculated as part of the income statement to provide an indicator of the company's operating performance without the impact of non-operational items.

💡Financial Analysis

Financial analysis is the process of evaluating a company's financial statements to determine its efficiency and effectiveness in managing funds to maximize value. In the video, the dynamic three statement model is highlighted as a tool for regular financial analysis, helping to understand the company's overall financial health.

💡Valuation

Valuation in a financial context refers to the process of determining the economic value of an asset or a company. The three statement model created in the video serves as a foundation for various types of valuation work, such as discounted cash flow analysis or leveraged buyout (LBO) modeling, which are essential for investment decisions and business strategy.

💡Excel Formatting

Excel formatting refers to the process of arranging data, applying styles, and adjusting the presentation of cells in a spreadsheet to improve readability and appearance. Throughout the script, various Excel formatting techniques are used, such as hiding gridlines, applying borders, and customizing date formats, to make the financial model more professional and easier to understand.

Highlights

Introduction to creating a dynamic three-statement financial model linking the balance sheet, income statement, and cash flow statement.

Availability of a downloadable file for the financial model used in the video.

The model's utility for regular financial analysis and as a foundation for valuation work such as discounted cash flow, LBO, or M&A models.

Step-by-step guide begins with organizing and building an income statement using a set of assumptions.

Explanation of using Excel functions like EDATE to project future dates for financial estimates.

Customization of date formats in Excel to represent fiscal years for income statement periods.

Formatting tips for enhancing the readability of financial statements in Excel.

Assumptions for a lemonade stand company, including revenue, discounts, raw materials, and operating expenses.

Demonstration of making assumptions dynamic by linking them to revenue percentages.

Building the balance sheet with historical figures and estimates for accounts receivable, fixed assets, and liabilities.

Explanation of the difference between historical actuals and forward-looking estimates in financial modeling.

Linking the income statement with the balance sheet to calculate key financial metrics.

Creating a depreciation schedule for fixed assets and understanding its impact on the income statement.

Calculating total depreciation and amortization and linking it back to the income statement.

Introduction of an Excel course for business and finance, aimed at enhancing practical Excel skills for industry professionals.

Finalizing the income statement with interest expense calculation linked to the balance sheet.

Building the cash flow statement from net income, adjusting for non-cash items, and changes in working capital.

Reconciliation of the balance sheet with the cash flow statement to ensure internal consistency.

Importance of the balance check in ensuring the accuracy of the financial model.

Conclusion emphasizing the model's role as a foundation for various types of financial valuation.

Transcripts

what's up everyone it's kenji here and in this  video we're going to be creating a dynamic  

three statement model where we'll be linking the  balance sheet the income statement and the cash  

flow statement and you can download the file i'll  be working with for free as i'll leave it in the  

description below so let's get into it and here's  the steps we'll take to make this financial model  

firstly we'll organize and build our  income statement using a set of assumptions  

secondly we'll create schedules to model out more  complex line items like capital expenditures and  

depreciation following that we'll build out our  balance sheet and lastly we'll build up the cash  

flow statement and link the three statements  together and in case you're wondering when a  

three statement model is useful on the one hand  it's just for regular financial analysis and on  

the other hand this is usually the foundation  for any sort of valuation work that you might do  

like for example a discounted cash flow an lbo  or an m a model as well so if we get into the  

excel file here we've got the income statement if  you go control page down you can find the balance  

sheet the statement of cash flows and the fixed  asset schedule so firstly let's start off with  

the income statement press the alt w vg which is  going to get get rid of those grid lines which are  

sometimes a bit annoying and this financial model  will be building is for a lemonade stand company  

in its early stages like say a startup let's get  started with the income statement first so over  

here you'll see that we have the year and let's  move that along to all of the other years as well  

so for that we can do a formula like the edate so  equals e8 press the tab key the start date is the  

previous years comma and then number of months  ahead so that's going to be in 12 months time  

so put a 12 there that's going to give you 2023  and we want to move that along all the way to  

these other years once we have that we want to  change the type of date here into something like  

the year and then the e letter for the estimate  in this case so press the ctrl one key for that  

and from there we're gonna go to custom and we're  going to customize the type of date here so what  

we're gonna do is put the y four times which  is gonna stand for the years then we'll put  

this sign over here which is like a dash and then  followed by that we'll we'll put a capital e which  

is going to be the estimate equivalent press  enter that should change everything into this  

the reason we just won the year instead of an  actual date is because the income statement is  

usually for a range of period like one entire year  while on the other hand the balance sheet is for a  

snapchat so a particular day this case is for a  whole year let's also start formatting the rest  

of the income statements so we'll put some indents  over here for the gross revenue and the discounts  

press the shift down arrow to select it from there  you'll go to alt h6 which is going to in them them  

for us and after that let's also add some borders  here on the net revenue so press the ctrl b  

first to bolt on it and then we'll go to alt  hb and let's say we want to add some borders  

just above it so for that we will just press  the p key like so same thing over here so for  

the cogs we would have to indent all of the  cogs here so alt h six put a bolt sign over  

here for the total coke so control b and same  thing alt h b p and you get the general idea  

so i'm just going to fast forward this whole  process so you don't have to keep looking at it

once we have things formatted the way we like  them in the income statement we can go to the  

very bottom by just pressing the ctrl and the down  arrow and over here you'll find a different set of  

assumptions so these are all the assumptions  that we've made about the lemonade stand and  

how we think it's going to perform so for example  for a revenue it would be the cup sold over here  

the average price of the cup and any discounts  that we might have then we have the different  

raw materials like what might be buying the  actual lemons the ice and things like that as  

well as other operating expenses so these are  things like these salaries that we might have  

any office or rent space that we might incur and  then lastly we have the tax rate which well it's  

just inevitable right and you'll probably notice  that all of these assumptions are in blue that's  

generally the convention you'll have them in this  color when they're hardcoded meaning that they're  

not an actual formula and you just type the number  in yourself let's also format this area a bit more  

just by putting a few indents so ctrl shift  down arrow to select all of that then alt h6  

same thing over here ctrl shift down arrow alt h6  and same thing over here alt h6 for some of these  

assumptions here you'll notice that there actually  as a percentage of revenue like the discounts here  

raw materials etc so for that we're actually going  to change it so it's dynamic and it looks like it  

so we'll go to equals and then control up all the  way till we reach b6 here press the ampersand and  

then we'll put a quotation first quotation here  put a space and as a percentage percentage sign of  

revenue of rev should be fine and then we'll go  close the quotations and just like so and then  

press enter and now you can see we actually forgot  to put the space here in between the discount and  

as the percentage of rev so press the f2 key  to get back inside the formula and let me just  

add the space over here generally when you put an  ampersand and any quotations you can put whatever  

you want within that sentence so in this case  we made it like this so now that we have that  

we can actually just drag it down so we'll go  ctrl c and drag it all the way all the way to  

this here so we've got raw materials fulfillment  and now because it's all dynamic it should be  

linked to the right one same thing down over here  we'll press the shift down arrow and then ctrl v  

you'll notice now this one's not quite right it's  not gross profit that should be in here so we'll  

press the f2 key just to see what's going on and  you can see that we actually have to drag them a  

bit lower down till the labor here press enter and  then control c again and control v and now it's  

looking the way it should now that the assumptions  are looking good let's actually fill in the income  

statement so go control up all the way to the  top and then for the gross revenue first that's  

just going to be the price times the quantity so  we'll go equals go all the way to the assumptions  

that's the cup sold times the average price per  cup press enter after at the discounts they're  

going to be negative as we're essentially giving  away money in a way right so for that we'll go to  

equals as we said it's a percentage of revenue so  we'll select the gross revenue and then multiply  

it by minus the five percent so let's go ahead and  select that press enter and then for net revenue  

we just consume these two so you can go the  shortcut for that is just the alt equals that's  

going to automatically fill that and give you the  formula press enter moving on to raw materials and  

here we're going to go equals and select the gross  revenue because it's a percentage of revenue again  

and now this time around we want to press the  f4 key and then press the f4 key again till you  

only have this dollar sign on this on the five if  you don't know these dollar signs essentially fix  

a cell so if you move a formula along that cell  is going to remain fixed so that's what we want  

for this one and then multiply it by the the raw  materials percentage which in this case is that 30  

press enter and this way we actually want to make  it negative because it's going to be a cost right  

so press the f2 key and at the very front of the  equal sign press the negative sign press enter  

and then we'll just drag this drag this down  a bit so ctrl c and then just paste it down  

over here like so and then for a total cogs  again same thing so alt equals press enter  

i just noticed here that the formatting should  be a bit different actually so this one here  

the total cogs should actually have that format  for the gross profit so for that we'll select  

this over here press the alt h fp and then  just the down arrow that's going to give you  

the same format for the one below and then this  one here we don't want this format we actually  

want our normal format like the transaction fees  here so we'll select that by shift right arrow  

and then alt hfp and then press the down arrow and  now that's looking better next up for the gross  

profit here we're actually going to be equals  and that's going to be the net revenue plus  

the total cogs here and you might be like well  isn't the cost a negative and it is but because  

it's already a negative over here if we do a  negative minus another negative it's going to  

be positive for us so instead we just sum on both  of them and that should be working for the gross  

profit margin just below we want to go equals and  that's going to be the gross profit divided by the  

revenue over here press enter now moving on  to the operating expenses and the first one  

we've got is the labor here and again we want  to make it negative as it's going to be a cost  

for us so equals negative sign control up all  the way to the gross revenue here and we want  

to lock this one again so press the f4 key f4  key again so it's just on the number over here  

and then multiply that by the percentage of labor  so control page down control down arrow sorry and  

let's select this one here labor as a percentage  of revenue and same thing we want to control c  

and just paste it down control v over here total  opex is going to be alt equal sign press enter  

and then for the ebitda we'll go equals that's  going to be the gross profit plus the total opex  

and in case you don't know what ebitda stands for  it's the earnings before the interest the tax the  

appreciation and amortization you're probably  wondering what that means or why it's useful  

and it's generally a measure of profitability and  the reason why you want something like this is to  

be able to compare with other companies better for  example this is a bit of an extreme example well  

let's say that one company pays zero and zero  dollars in tax and the r1 actually has a 30 tax  

rate so you pay a ton in tax now those two aren't  very easily comparable if you're comparing the net  

income of one versus the net income of the other  just because if that one's paying 30 tax rates  

then that net income figure is obviously going  to suffer that's why you actually omit the tax  

rates and you go to something like ebitda so you  can compare them more fairly next up here we've  

got depreciation amortization and for now we'll  omit this we'll get back to it later on for the  

ebit let's go ahead and set up the formula right  now so we'll go to equal sum press the tab key and  

just select the two from here for the interest  expense we'll also meet up for now and get back  

to it later but let's set up the formula already  so we'll go equal sum press the tab key and select  

these two over here for taxes we do know them  they're the 21 assumption that we have below  

so let's go ahead and do that so equals get the  ebt times the tax rate so ctrl down arrow all the  

way to the bottom here the 21 percent press  enter but it should be a negative right for  

attack so we'll go f2 key and put a negative sign  up front so press the f2 again sorry and just put  

a negative here press enter for net income it's  just going to be the sum of that so equal sum  

top key and select these two here lastly we have  the net income as a percentage of revenue so this  

is just another ratio that we have so we'll  go equals net income divided by the revenue  

so that's going to be the net revenue over here so  now that we've filled all the items for the income  

statement we should be able to drag them along so  first press the control up to go all the way to  

the top then the shift down arrow and just select  everything to the bottom here and then ctrl c  

and then shift right shift right all the way  to the end and ctrl v that should automatically  

populate for us and before we move on if you  like what you're seeing we do have an excel  

for business and finance course which i created  with my friend michael who helped me with this and  

he's a financial analyst at tesla in the course we  teach everything we know about excel specifically  

for people either looking to break into the  industry or those in it trying to level up  

their excel skills unlike most other theory-based  courses we try to make this one as practical as  

possible based on our real experiences working  at companies like tesla amazon or goldman sachs  

so aside from the typical lessons on formatting  formulas and charts we have case studies that  

replicate the type of work you might be assigned  in your day to day ranging from financial modeling  

to cleaning a data set and presenting some visual  insights related to this video we also have an  

in-depth three statement model which goes over  apple's real financial statements so if you're  

interested in checking it out we'll leave  the link in the description below alright  

so we finished the income statement but we do have  some line items that are still not done like the  

depreciation over here so for that let's go to the  very last tab which is the one on the fixed assets  

so go to control page down all the way to the  last one from here this is going to help us fill  

in the depreciation and amortization so firstly  let's clean up the formatting again over here a  

bit so select all of this area here so ctrl shift  right arrow from there go to control one let's go  

under custom again and we want to change this to  y y y y and then we'll put this reverse dash sign  

and press the e press enter and that's going to  change it for us we also want to change a bit over  

here put the indents and so on which i'm going  to fast forward so you don't have to see it all

so to explain this table a bit better over here  we've got the different types of investments that  

we expect to make so we're hoping to buy a lemon  crusher an ice machine and a refrigerator as well  

that's going to be in 2022 and over here the asset  life we've consulted with the different vendors  

and this is what they say we should estimate as  the life of the assets so basically how long till  

we have to throw it away because it's no longer  useful to us so we've got the lemon crusher at  

three years and then seven years for the other two  so we can go ahead and put the totals over here  

so press the alt and arrow the equal sign press  enter ctrl c and just drag that cross control v  

over here you'll notice that we actually buy  another lemon crusher in 2025 and that's because  

it's only got a three year life and so it's  we're probably going to dispose of it by by  

the end of this one so we buy a new one so  now that we know what we're buying we can go  

ahead and create a depreciation schedule which is  going to be just down below and if you don't know  

what depreciation is it's just a way to allocate  the cost of an asset over its useful life so for  

example if you bought say a new truck for the  lemonade stand that costs 50 000 then all of a  

sudden in the one year you would have a drop in 50  000 and you'd probably have a huge loss that year  

but the next year things would be pretty good and  so that doesn't make much sense and instead what  

you do is try to allocate it over its life let's  say the truck has a 10-year lifespan and so for  

that you're going to incur the cost on a relative  basis so everything at five five thousand per year  

as opposed to just having the 50 000 which looks  like something terrible happened one year and then  

things are back to normal which is just a bit odd  if we look into the depreciation table over here  

we can see that we have already some existing  equipment say from previous years for example  

and on top of that we have what we're going to buy  so for the lemon crusher we need to allocate it  

over his three-year life in this case so we'll  go to equals then we'll select the 5000 here  

which is the cost press the f4 key such that it's  only locking the d sign and then divide that by  

the number of years same thing we want to press  the f4 press it again and one last time press  

enter from there if you love if we've locked the  things correctly we should be able to just drag  

it down so ctrl c drag it down here and then ctrl  v let's just make sure by selecting this last one  

pressing the f2 key like so and you can see that  it's moved on accordingly to the where to the  

place that it should from there we'll select  all of them so firstly let's do the first one  

so ctrl c and then drag it across to control v  you'll notice that because we buy a new one here  

and it's only got a lifespan of three  years we actually need to renew it here  

so we'll go to equals and then select this  one again press the f4 key f4 key again and  

one last time and then divide that by by three  select here and then press the f4 key again  

like so and then for these ones we can just drag  them across so control c and just press ctrl v  

and we actually need to do this all the way  until the end so control c control v here and  

lastly we need to sum everything so for that  we'll just go to alt equals press enter and  

just drag that across press ctrl v so now that  we have the total depreciation amortization we  

can already link that back to the income statement  so let's go ahead to the income statement control  

page up all the way here and then for the first  one go to equals control page on to the last one  

and we'll select the 2022 one press enter  from there we should be able to drag it along  

but first we actually need to make it a negative  here so press the f2 key and let's put a minus  

sign right here up front press enter and  then ctrl c just drag it across ctrl v  

and in this case you might notice that we have a  depreciation and amortization if you don't know  

the difference basically depreciation has to  do with tangible assets so things that you can  

actually touch like say machinery a building  other things like that on the other hand for  

amortization it has to do with the intangibles so  things that you can't touch examples of that are  

usually like patents copyrights and other things  like that and the last line item that we have to  

fill here in the income statement is the interest  expense and for that we're actually gonna start  

getting into the balance sheet and then be able  to backtrack from there so go to control page down  

so first things first let's work on the formatting  here so for that you see that we have a historical  

number so that's going to be an actual as opposed  to an estimate like all of these here so to change  

that we'll go to control one and then we'll go  to custom here and now we don't actually want  

just the years but we also want the month because  it's going to be the balance sheet it's a snapshot  

of a specific time period so for that we'll go  something like mmm press the dash key and then yy  

which is going to be for the years and then we'll  put this sign over here and then put on a for the  

actual press enter now we've got the december 2021  actual and for all of these we want to change them  

to estimates same kind of concept control one key  we'll go to custom and we'll change those so 3ms  

press the dash and then we'll go to year year  

put the sign here and then we'll go for e for  estimate nice now we have to do all of the  

other formatting stuff which we've done before  so i'm just going to fast forward this as well

all right so now we have the formatting looking  the way that we like it let's go ahead and start  

looking at the historical figures so we can  see what's going on so we had cash of 5000  

next to that we have the accounts receivable of  150. if you don't know what accounts receivable  

is this is typically when they paid for something  but you still didn't get the cash for example a  

typical example here could be with the credit card  companies where there might be a bit of a delay  

between when the customer actually pays and when  you get the money in under the fixed assets we've  

got the 10 000 over here which as we mentioned is  for the existing equipment if we go control page  

down to the fixed assets here you'll find that  we've got all of this existing equipment that  

we mentioned earlier which is being depreciated so  control page up again to get over here this is the  

accumulated depreciation of it so we can start  putting the totals in for these ones so we'll  

go to alt equals here for the first ones and then  alt equals again for these press enter total long  

current assets is just the the net fixed assets  press enter and then for the total asset is the  

sum of the current plus the non-current so equals  this one the current assets plus the non-current  

assets press enter then on the liabilities side  we've got the accounts payable so for example  

maybe we bought some lemons from a vendor but we  still don't pay them in full that's why we might  

have something like that next to that we have  deferred revenue so this is when we've already  

received the revenue from a customer but we still  haven't delivered the product for example maybe  

they've they've already booked in advance for a  lemonade and they paid us money for it already  

that could be the case for the third revenue so  we'll go alt equals here and press enter for the  

other liabilities we have some debt over here  that's mainly because they were a startup and  

so we need to borrow some money from the bank to  get started so we'll go equals and then just link  

it from here and for the total liabilities it's  a sum of the current and the non-current so this  

one plus this one over here press enter next up  under equity we have the common stock which is 300  

this can get a bit complicated but for now just  see it as your assets minus your liabilities so  

whatever is left in your company after you sell  everything and you pay off for all your debts  

and then lastly we've got the retained earnings so  that's whatever you have at the end of the year at  

your company so let's get the sum of that too so  alt equals press enter and for total liabilities  

plus equity same thing so equals total equity  plus the total liabilities and press enter and  

the balance check here is basically going to check  if the assets equal the lap the liabilities plus  

the shareholders equity if that's not the case  then we probably made a mistake somewhere so this  

is some sort of a health check if you will you can  press the f2 key and that's how you see what there  

is currently there moving on from that and we've  got all of the different assumptions over here  

under down below here and so firstly we've  got the net revenue assumption as well as  

the cogs assumption which are going to help us  calculate all of this stuff here for the accounts  

receivable let's make this one dynamic again so  we'll go to equals and just select the accounts  

receivable here and put the ampersand quotations  and then we say as a space as a percentage of rev  

then close the quotations and then press enter  and we need to do the same thing here with the  

accounts payable so we'll go equals and firstly  we'll select the accounts payable here ampere sign  

and then we'll go quotations quotations as put the  space here as a percent of rev revenue and then  

we'll close the quotations here press enter and  lastly for the deferred revenue this is actually  

the same thing so we'll just copy this one and  paste it that should work for us one thing though  

for the accounts payable this is usually linked to  the cost of goods sold as opposed to the revenue  

so we'll have to change that so go inside it by  pressing the f2 key and we'll just change this to  

cogs there we go once this is set up here let's  go ahead and add the net revenue and the cogs  

which we can find from the income statement so  we'll go equals control page up the net revenue  

is going to be this one here press enter and  then for the cogs we'll go equals page up again  

and that's going to be the total cogs here you'll  notice that the formatting is all messed up so  

we'll go to alt h fp and then just drag it down  here once we have that ready ctrl shift down and  

then ctrl c so i ctrl c and just drag it across  control v one thing though for the cox here we  

don't really want it as a negative number just  because we're going to be doing percentages from  

there after so we'll go to f2 key and just put  a negative sign in front so it makes it positive  

press enter and then just drag it along ctrl  c ctrl v here so let's go ahead and do all of  

the different percentages that we need so for  the accounts receivable first we'll go equals  

control up all the way to the accounts receivable  and then we'll divide that one by the net revenue  

same thing here so accounts payable equals we'll  go select the accounts payable here it is and then  

we'll divide that by the cogs in this case and  then for our last one the free revenue equals  

here's the third revenue and we'll divide it  by the net revenue here press enter and once we  

have these historical figures we can actually go  ahead and forecast them for the future we're just  

going to link them to whatever they were in the  past for simplicity so we'll go equals and just  

select this one then just press ctrl c shift down  arrow then shift to right and press press ctrl v  

and based on these percentages over here you'll  notice that generally the accounts receivable  

is lower than the accounts payable now is that a  good thing yes because that basically means that  

you're taking a lot longer for you to pay  whatever you need to pay but customers are  

paying you fairly quickly and so you're actually  going to have a good cash flow in that sense  

and now that we have all of these assumptions we  can go ahead and put them in to the balance sheet  

here so for the accounts receivable we'll just go  equals we want to select the 1 and multiply by the  

net revenue press enter same thing goes with  the accounts payable so equals control down here  

accounts payable is this one and multiply that by  the cogs instead press enter and then same thing  

for the last one the deferred revenue control down  arrow select this multiply it by the net revenue  

and press enter now we can already get the totals  for these so let's just set up the formula you see  

that the formula is already in here so just ctrl  c and then ctrl v to paste it along same thing  

over here so ctrl c and then ctrl v to paste it  along and we can paste all these figures as well  

so just ctrl v you'll notice that all of these  are sort of unformatted we'll press the ctrl 1 key  

and from there we want to go under the numbers tab  and change the decimal places to to zero say press  

enter now that's looking a lot cleaner uh these  over here let's also drag these across so ctrl v  

now let's look into the non-current assets  that we have over here so for the fixed assets  

this is basically going to be whatever fixed  assets that we had in the previous year and add  

anything new to that so for that we'll go equals  select this one here and then plus control page  

down all the way to the fixed asset side here and  we want to select the copics over here which is  

our capital expenditures so whatever we've added  into the balance sheet in this case we press enter  

and we can also move that across and then ctrl  v for the accumulated depreciation the concept  

is pretty much the same except that it's going  to be negative right so we'll go equals select  

whatever we had previously that's been accumulated  and another minus sign control page down page down  

again and it's going to be total dna here press  enter then we can drag that across as well ctrl v  

then the net fixed assets is going to be just the  sum of those so ctrl c we'll just drag it across  

because we already have the formula and same thing  over here same thing with these two so ctrl c  

and then ctrl v because we just need to drag  them across and total long current assets here  

should have a bold so we'll press the shift  space key to select the row and then ctrl b  

there we go now moving on to that area that we  have over here and for this we actually have  

different assumptions so let's look at them down  below here here we've got all of the different  

information that we're going to need so right now  you can see that we're going to need to borrow  

some money in this particular year but anything  before that we're just going to be repaying  

whatever we had so remember we took out a loan  over here that you can find of say 10 000 and so  

that's basically paying that down and as you can  see once we incur a new loan then obviously the  

debt repayment is going to be higher usually the  debt is broken down into short-term and long-term  

debt but for this case just to simplify it we just  put it all into one so going back to the balance  

sheet item that's the debt here what we want to  do is first go to equals we're going to have the  

previous year's debt plus any new debt that we've  incurred which is going to be the net borrowing  

here minus any debt repayment or whatever we've  paid back of it so we'll select this one and  

then press enter then we can just drag it across  so ctrl c ctrl v here and for this we can just  

select these formulas ctrl c and then ctrl v then  for the equity side we've got the common stock  

which we'll just assume is the same so go equals  and select this one press enter and then we've  

also got the retained earnings so for the retained  earnings it's usually the previous year's balance  

plus whatever net income that you made this  current year say so go to page up under the  

income statement you'll find the net income over  here at the bottom and then press enter sometimes  

you will have dividends which are going to be a  negative right because the money is going to be  

going out to say the investors for example but in  our case we don't have that just for simplicity  

then for a total equity it's just going to be  the same formula so ctrl c and then ctrl v drag  

that across same thing here ctrl c and ctrl  v now moving on to the last assumption that  

we have here under the balance sheet which is  the interest payment this is going to be useful  

for us under the income statement to fill in this  last item which is the interest expense here so go  

to control page down again and what we're going  to do is go to equals it's just going to be the  

interest rate times whatever that amount that we  have which is this one over here press enter and  

then we'll just drag that across ctrl v then go  to control page up and for the interest expense  

we need to be negative so we'll go equals  minus control page down and we'll select it  

from down here as the interest payment press enter  ctrl c drag it across control v we finally have  

a complete income statement but now if you go to  control page down and check out the balance sheet  

you'll notice that it's actually not matching for  all of these years over here which is obviously  

not a good sign it should always match or else  we have a mistake but go to escape here and  

then you'll notice that we actually don't have  the cash over here so that's probably why it's  

currently not matching to find out the cash we're  actually going to create the cash flow statement  

which you'll find just going by going to control  page down again so first things first let's format  

this one quickly and i'm just going to fast  forward this so you don't have to see it again

all right so now that we have it formatted the  way we like it firstly let's go ahead and get the  

net income so for this we just got to go to the  income statement under the last line so equals  

control page down all the way to income statement  then control down arrow to get the bottom and  

we'll just select the net income here and  for the operating activities firstly we've  

got the depreciation so for this one we'll  go to equals under fixed assets in this case  

go over here and that's a total dna here press  enter and you might wonder why depreciation is  

actually being added back here that's because  there's not really a real cash outflow in the  

sense that whatever is a transaction you had with  the vendor let's say for a truck you already paid  

for on that day and so that's why there's not  really a cash outflow now next up we've got the  

change in accounts receivable so for that it goes  to equals we'll go to the balance sheet this is  

going to be the current year's accounts receivable  minus the last years so go to control page down  

start page up here and we'll select the  current years minus the previous years  

press enter and now we actually have to make  this negative so for that we'll just go in  

f2 and go to put a minus sign first and then  put it in brackets and get to the very end and  

close the brackets here press enter and you might  wonder why an increase in accounts receivable is  

actually something that should be negative that's  because when you think about it in terms of cash  

the cash inflows are actually going down in that  more and more people owe you money but they still  

haven't paid you that's essentially what accounts  receivables are and so for that it's actually a  

cash outflow for us so it's a negative and for  the change in accounts payable it's pretty much  

the same logic except that this time around  is flipped so we'll go equals control page up  

get the accounts payable from here minus the one  just prior because we want the change press enter  

and that should give you 20. and then we can just  drag this down ctrl c and ctrl v and to explain  

this for the accounts payable side this basically  means that it's taking us longer and longer  

to pay the cash say to a vendor for instance and  so we're keeping all that cash that's why it's a  

positive thing for us that's why we have a cash  inflow with all this we can sum the operating  

cash flows so alt equals press enter then we've  got the investing activities so these are usually  

things like property plant and equipment that  kind of stuff that you might invest in in our  

case we've got the capex so for that we'll go  to control page down to the fixed assets here  

and we want to select the total capex over here so  the control page up go to equals and we want it to  

be a negative essentially because we're spending  money to acquire say a specific machinery and  

so it's a negative it's a cash outflow for us so  minus control page down and we'll go to the total  

capex here press enter and then for the investing  cash flow it's just going to be a link to that so  

this is it and then for the financing activities  we've got the debt repayments and the borrowings  

both of these line items we can actually find  from the balance sheet so for the first one  

for that repayment we'll go equals control page  up and we should be able to find it over here

so here it is and in this case we it's actually  going to be a negative and when you think about  

it we're essentially using our cache to pay down  debt and so it's a cash outflow for us so we'll  

go to f2 key and put the negative sign in front  press enter and for the borrowings it's actually  

the opposite when we borrow money we essentially  get cash and so that's good for us it's a cash  

inflow so we'll go equals control page up and the  net borrowing should be right here press enter and  

now for the financing cash flow which is the sum  of these two so sum and select two press enter and  

the net cash flow is going to be the sum of all  of them so we'll go equals the net income plus  

the operating cash flow plus the investing cash  flow plus the financing cash flow press enter  

once we have that we want to select all of them  and then press ctrl c and just drag across by  

pressing the shift and right arrow ctrl v once we  have the net cash flow we can actually go ahead  

and fill in the balance sheet where we're missing  the cache if you remember over here we're missing  

it and so that's why it's currently giving us  a negative balance on the check here so let's  

go ahead and link it so we'll go to equals we  actually need to do the previous years so this  

cache over here and add whatever change there  was the cash flow which we figured out in the  

statement of cash flows here and then press enter  and from there we'll select all of them so ctrl c  

and then shift right arrow ctrl v now if we did  this correctly once we checked the balance in  

the balance check here it should be zero that  would mean that we've done everything right  

so we'll go ctrl down arrow all the way  here and you can see that they're all  

they're all zero sorry so basically  means that we did everything correctly  

so creating a dynamic three statement model  like the one we created here is going to be  

the foundation for any sort of valuation that you  might want to do on top of that for example it  

could be a discounted cash flow a leveraged buyout  a m a model other things like that so if you want  

to learn more about valuation you can check out  this video over here or if you're interested in  

knowing how to make this a bit more visually  pleasing with some cool excel charts check out  

this other video over here that's all for this  one hit the like hit that subscribe and comment  

down below it helps out the algorithm that's all  for this one and i'll catch you in the next one