Practical Financial Modeling Bootcamp - building a model of Google | CorpFin Pro | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Practical Financial Modeling Bootcamp - building a model of Google

teacher avatar CorpFin Pro

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

34 Lessons (12h 3m)
    • 1. Course preview

      1:17
    • 2. Part 1 - What is a financial model

      6:30
    • 3. Part 1 - Tips to make this course more efficient

      3:00
    • 4. Part 2 - Intro thoughts

      7:48
    • 5. Part 2 - Obtaining financials

      3:28
    • 6. Part 2 - OPTIONAL: Formatting

      35:50
    • 7. Part 2 - OPTIONAL: Useful shortcuts and tips in Microsoft Excel (Windows version)

      33:49
    • 8. Part 2 - OPTIONAL: Ways to structure a model

      22:40
    • 9. Part 2 - Understanding how companies operate

      8:48
    • 10. Part 2 - Revenue

      28:23
    • 11. Part 2 - Costs and expenses

      10:32
    • 12. Part 2 - Current assets

      25:31
    • 13. Part 2 - Non-current assets

      55:25
    • 14. Part 2 - Current liabilities

      35:57
    • 15. Part 2 - Non-current liabilities

      23:04
    • 16. Part 2 - Equity

      25:36
    • 17. Part 2 - Cash Flow From Operating Activities (CFO)

      18:34
    • 18. Part 2 - Cash Flow From Investing Activities (CFI)

      7:59
    • 19. Part 2 - Cash Flow From Financing Activities (CFF)

      5:42
    • 20. Part 2 - OPTIONAL: Mistakes

      20:10
    • 21. Part 2 - Finishing income statement

      8:22
    • 22. Part 2 - Switcher

      7:47
    • 23. Part 3 - Theoretical background and FCFF (1/2)

      27:29
    • 24. Part 3 - FCFF - continued (2/2)

      40:16
    • 25. Part 3 - FCFE

      11:02
    • 26. Part 4 - Revenue - detailed

      67:13
    • 27. Part 4 - Costs and expenses - detailed

      41:22
    • 28. Part 4 - Capex

      8:32
    • 29. Part 4 - Depreciation

      29:37
    • 30. Part 4 - Working capital

      6:49
    • 31. Part 4 - Income taxes

      14:22
    • 32. Part 4 - Debt and revolver

      46:21
    • 33. Part 4 - WACC

      31:36
    • 34. Next steps

      1:50
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

299

Students

--

Projects

About This Class

Course description

We believe that practice is the best way to learn. That is why we have created a practical course to help you to learn financial modelling from scratch (or improve your skills).

In this course, we are going to build a financial model of Alphabet, a parent company of Google. We aim to show how financial models are created at the leading financial institutions. That is why we have decided to pick up a real company and prepare a proper financial model, i.e. not the simplified version as most of the other courses do.

We understand the challenges of building full-blown models from scratch for people who have little experience in the field. That is why we have decided to split the course into three substantial parts:

  1. In the first stage, we are going to prepare a three statement model. We'll start by downloading the financials of Alphabet. Then step-by-step, we'll show how forecasts are built for every line in each financial statement. We'll use the format in which Alphabet reports, not a simplified version. In order not to confuse you at this stage, for some metrics (e.g. revenue), we are going to use broker consensus forecasts. We'll come back to such metrics later in the course
  2. In the second stage, we are going to build a DCF valuation using both FCFF and FCFE approaches. We'll base our DCF valuation on the three-statement model we have developed on the first stage
  3. After we finish DCF valuation, we'll enhance our forecasts. We are going to replace the metrics for which we used broker consensus with our estimates. This way, the course participants will be able to focus first on the three-statement model and then on building forecasts. We think that such streamlining will make the process much more efficient.

Along the way, we'll describe some corporate finance concepts as well as common practices used in the industry.

Files used in the course

You can find files used in the process (excel files and a file with links to download financials) in the project section.

IMPORTANT: Iterative calculations should be turned on. Instruction is available on the official Microsoft Office Help page (https://support.office.com/en-gb/article/remove-or-allow-a-circular-reference-8540bd0f-6e97-4483-bcf7-1b49cd50d123). Go to the "Learn about iterative calculation" section and follow the steps outlined there.

Prior knowledge required

We expect you to have:

  1. Knowledge of Microsoft Excel
  2. Basic understanding of corporate finance

You can still take the course without the skills above. We'll try to explain things on the go. However, in this case, be prepared to spend additional time educating yourself too.

Applications required for financial modelling

You can use any spreadsheet application you would like to. We highly recommend Microsoft Excel (2007 version onwards) on a Windows machine since it is the most common set-up used for financial modelling. However, the model should work the same way on most other applications. It even works on Google Sheets (which do not require you to install an app locally on your machine, you can use a browser instead). But please beware that (i) some of the functionality of Microsoft Excel could be unavailable on Google Sheets, Apple Numbers, LibreOffice Calc and other packages, and (ii) the files created in the packages might not be fully cross-compatible. Thus, we recommend using Microsoft Excel when building a model for external use or review.

We will be using Microsoft Excel on a Windows machine, and all shortcuts and tips will be relevant for this set-up only.

Who is this course for

A wide range of people might be interested in gaining financial modelling skills. Below are some possible scenarios:

  • To make investment decisions for example whether to buy or sell stocks of a particular company
  • To support your assumptions on a project or justify a business case
  • While starting a new business to estimate the financial results of a venture
  • In your existing startup - for fundraising objectives. Most investors will require a financial model as a part of your fundraising pack to better understand how you plan to make money, etc.

Knowledge of financial modelling is instrumental:

  • When pursuing a degree in business or finance
  • For your job interviews especially in the financial industry

"Disclaimers"

IMPORTANT: Please note that this course is not optimized/designed to be watched on small screens (e.g. mobile phones)

While this course covers all main financial modelling topics, it is not a comprehensive guide. We are going to cover most topics in detail. However, there are some things which will be modelled only high-level. The reason for this is that you need a lot of information (which is usually not available) to model such items. All such topics are in the vast majority of cases modelled high-level as we do in this course. We'll demonstrate that brokers are also using the same approach later in the course. These topics are taxes, stock-based compensation, goodwill, minority interest, associates, and some other. In some instances, people model these advanced topics in greater detail. Still, usually, such calculations are possible when you have access to non-public company information, e.g. you are working on a deal.

Like every human, we make mistakes when we speak or type. However, these are not critical things, they do not change the messages, and most people won't even notice them.

We followed the 80/20 rule when we were preparing this course (this is an excellent way to fight the natural desire to improve things continuously). We have not polished every single thing (e.g. change wording, take out every parasite words, clear sound like "aaa", "mmm"). Instead, we have left most of the course in the way we have recorded it the first time with some minor edits (e.g. taking out pauses). Polishing would take so much time that developing this course would become unreasonable for us. Moreover, it would not add any real value. Also, we think for most people, it would be easier to understand the content of the course in the shape and form we have recorded it initially. Usually, it is easier to digest unpolished (natural) content.

When you watch the videos, the screen might change rapidly since we move between the pages. If you are lost, it might make sense to have a look at the bottom of the workbook - the active worksheet is always highlighted. Please also note that you can change the speed of the video. We tried to speak slowly to make it easier to digest the information for some readers, but many people prefer more dynamic videos, in such cases, you can increase the playback speed to 1.25x or 1.5x. At higher speeds (1.75x+), the videos might sound a bit "robotic".

Meet Your Teacher

Teacher Profile Image

CorpFin Pro

Teacher

We are a team of finance professionals with each of us having over ten years of experience in the industry. We are passionate about sharing our knowledge and experience as we want people around to have a better understanding of corporate finance.

 

We aim to prepare practical courses as we believe that this is the best way to learn things. We hope that our efforts will contribute to your professional development.

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Course preview: Welcome to the course. Preview These course we're going to color financial model Use Alphabet, the parent company of Google, as an example. First stage. Don't want financials off alphabet from this issue. Upside Then, based on that financials, we prepare a detailed statement model. We're not going to take shortcuts, and we do not make a highly simplified version as most other course. Instead, build model off the country, not war than analysts. Investment banks make for wearables After lifting statement model we're going to prepare this year valuation model based on this is maybe the most used relation, which is a must know for any finance professional. We're going to build our disease for allusion, using both safe. And it's easy methodologies along the way with this crap. Corporate finance concepts as well, some common practices in financial model. We hope that you enjoy the court, and after taking it, you will be billed for models yourself. Also, this course will book your corporate finance knowledge. If you're interested, they details off the court in the description and wash sample. We just understand the content of the calls better again, thank you for your interest and hope to season 2. Part 1 - What is a financial model: before we start modeling, I said, just taking some steps to make sure that we are on the same page. What's first defying water financial model is I encourage you to think about the model as a system very provide specific date in. Then the model protest is these data and provides outputs. Your inputs are business rivals such as frequency of purchases, price of product and others. Your outputs are the metrics you want to see, depending on your goals. This can be your profit in five years, the valuation of the company and others. So when we build a model, we are acting as an architect off the system we designed How inputs are protests to get the required outfits. Now let's discuss the types off financial models. Technically, many things might be called financial model. Common ones are this year. Merger models, LBO models and others. In this course, we're going to build it this year. Violation model. We're using both FCF F and F C of E approaches. To build this, we're going to forecast financials a spot of this exercise. We're going to develop a three statement model, which are a balance sheet income statement and casual statement for our forecast horizon. Technically, we can prepare a DCF valuation without a three statement model. However, having four costs for these three statements enhances our model and provides better visibility over the company's operations. So it's a good practice to develop a three statement model. Another important thing we need to agree on is that there is no single correct approach to build a model as mentioned before. Were architects building a model as you understand it cost. Architects of the one who designs buildings can create a building in different ways. If you give specific directions to 100 architects of, then at the end of the day you are going to have 100 various designs off a building. Some of them will have a lot in common, while others will have significant differences. The same applies to financial modelling. Each person has his or her vision off the model. We're going to discuss this topic waiter in the course, but it's important to understand that I'm going to share my view here. He shouldn't be limited to it. Feel free to design your model the way it's convenient for you, given what I have just said, I think we should now agree that our goal is first to develop a conceptual understanding or how DCF models work. Don't get me wrong. This is not a highway or theoretical course. Actually, it's a very practical one. However, during the course we should focus on understanding concepts, understanding the world, you behind the calculations, understanding the mechanics off corporate finance. Our goal is not memorizing, precise. Foremost, there are some reasons I'm saying that conceptual understanding is way much more critical than memorization of formals. First financial models might seem to be a complicated thing, however. All models are the factor collection off small, simple calculations. So if you understand concepts, then you easily handle calculations again. Don't memorize how I calculate thinks. Instead, focus on the meaning of these calculations. Second, all valuation models have many commonalities and are based on some rules. There are usually multiple ways to forecast things. If you understand the logic, you will be able to apply the most relevant calculation logic for your particular case. Such calculation logic might be different from what I would be showing during this course, since every company has its specifics. Third, conceptual understanding would increase your chances off. Avoiding logical mistakes also is going to increase your confidence in your work, since you will be able to defend the A logic. Actually, financial modeling is not that much about excellent foremost, It's much more about your understanding off the company, its financials, its markets and its operations. I mean, if it was about Exelon foremost, then everyone would be using a single temperate. For example, you can the world financial from Bloomberg Terminal Onda automatically in sort them into a template for validation. However, nobody is doing it when they want to evaluate a company. Maybe at some point in time it would be a logical what? As off today, such an approach is not considered to be serious. You can do thinks this way only four quick and dirty calculations. It means that you need to use your know which to build a high quality model, and our goal here is to boost your knowledge. Also, be prepared to spring time educating yourself on the standing corporate finance takes time . I can tell you that it took me about 1/2 year after I started working in Emanate. I knew foremost perfectly, but I was missing understanding off the concept behind some of these formals. Once I understood the ideas again, the confidence required to become a professional. I hope in your case you need the rest time, and this course will help you. Finally, the last thing I wanted to discuss in this video is that at the end of the day, a model is just a tool for here. Analysis. Many people focus on the model when they start. However, it's not correct. As I mentioned before, you need to understand the company. First, it's markets. It's business drivers. Its growth prospects is financials. Usually you don't have time to read annual reporting details. I guess that in 90% of cases people review them very high level. It's perfectly fine, But don't start more doing without understanding the operations and markets of the company . You don't need to understand all the details, but you need to have some knowledge of the company's operations. It takes time. Be ready to spend it. Just to be clear. In this case, I'm speaking about riel modeling, no case studies where you have from 30 minutes to an hour and people expect to see correct for most in excel rather than judgment behind them. So before you start modelling, review the company it separations markets business drivers, then understand how the company is going to develop going forward and reflect it in your model. A financial model is just in America visualization off your view off the company separations in future I hope you're still with me and we're on the same page now in the next videos all touch base on some of the moments. For example, why I have decided to Big Alphabet as an example, and then we'll start building the model that's it with this video and see you in the next one. 3. Part 1 - Tips to make this course more efficient: in this video, I'll try to provide some tips on how to make your education more efficient. Some people taking the scores will be able to understand things easily from the first of them, others who have to review the course a couple of time before they start following it. It's fine since will have different backgrounds in terms off our previous knowledge. And, in fact, very syndication formats work differently for different people. Don't be upset if you don't get something immediately, be patient, take a short break and review the things again. Also, don't try to make the course in one goal. It's a quiet one course. I would recommend splitting it into several days to the just information more efficiently. Just a suggestion you might want to model imperil with me. Open the video on one side of the screen and excel on another one. Watch the video and then try to repeat what I've done after each video. If something goes wrong, which the video again and implement the changes in parallel. I think such educational logic should work well for most people, however, feel free to design the protests in the way it's most convenient for you. Also, don't his date US questions. Once you finish the course and gain knowledge, I, such as building a model on your own, pick up a public company and try to create a model for its from scratch. I know that it might sound challenging, but you should give it a try. Whenever you have a question, open these scores and review the Roman section again. The only thing tried to avoid conglomerates because, most likely you have to do a separate model. Forage reasons wine to prepare the more or less good model pickup the company operations off, which are straightforward. Also, financial institutions are model differently, so avoid them for this exercise, too. By the way off, the makers are also nor a good example because they usually have substantial financing. Arms and modeling is not that straightforward. Don't expect that you must have the skills just after a couple of hours Usually takes many hours to prepare a good model for most people being a model from scratch after this course can easily take or 10 hours since you first need to understand operations off the company, its financials, business drivers, growth prospects, its markets and take a lot of other things into account. It will require much reading, which takes time. After you finish your model, do another one. Practice is what makes your professional not only education, a senior, a lot of people coming to investment, banking from so called top universities, thinking that they know everything. While in fact most of what they learned was not that relevant In practice, they were usually trained on purified content, not always what quite often it was the case. I haven't seen anyone good at modeling straight from education institution Onley. People practice financial modelling were able to build high quality models. I hope the steps will be useful and see you soon. 4. Part 2 - Intro thoughts: in this video, I would like to share some introductory thoughts. First, let me explain why I have decided to pick up Alphabet as a company to show how financial models work. It's actually quite simple. There are three main reasons. First, most people know how Google works. I mean, they knew the company and face its products very often. I guess there will be a lot of surprises as we go deeper into the company separations. But it's still easier than trying to explain how Ellis known company works. Even though each company has its specificities In general, models are not that much different from each other. Second, Google is a large company, which is well covered by analysts, and there is a lot of information about its separations and its markets available free on while. Third, I'm not an expert when it comes to Google or alphabet, as it's called now. Yes, I have spent most of my career in TMT. While I was on the south side, I was working in M and A, and I don't think that investment bankers working in corporate finance often make models value in Google. I mean, some people build such models when they want to evaluate some ideas. But in general, it's not that common. But for me, it's the first time I touch it. What kids? It's actually the second time because before filming these ideally called Run. But my initial intention was to build a model for a company, which I'm also discovering. On the go, my dear, was that we should be on the same page when I build a model. Otherwise, I will be more willing to take shortcuts and skip some parts as obvious. While they are obvious only to me disclaimer, I will be usually saying Google instead of alphabet. So excuse me for this. No, what may explain how this course is designed. First, we're going to start by building a three statement model. We're going to download financials off the company, then step by step built forecasts for each wine. At this stage, we're going to use broker forecasts in many cases, just if, where for some of the people who are not aware off the dog, a lot of brokers issue research reports for their clients, analyzing the financial performance of the company and providing recommendations on the stock to perform this analysis. In many cases, they built financial models extracts off, which are often included in the research reports. In our model, for example, we're not going to forecast revenue at this stage. Instead, will use broker consensus numbers waiting the course. We're going to come back to this topic and build proper raving forecasts. However, at the first stage, I believe it's much more reasonable to stick with broker numbers. The reason I'm saying this is because such things as the tail really forecast take a lot of time and attention. And I'm afraid that by the time we finish reviewing forecasts, a lot of people will be lost and confused, making the Hill four costs and building a three statement models simultaneously. It's just too much information, too. They just for most people. That is why we're going to simplify forecasting at this stage and instead focus on a three statement model weight. During the course, we're going to come back to detailed forecasts. On the second stage, we're going to build a D C affiliation using both F safe of and F Cioffi approaches. We're going to take the numbers from our three statement, then, once we finish the This year section, we're going to switch to detail forecast, enhance our motto. I believe such an approach will make it easier for most people today. Just information and achievable go off understanding how financial models work throughout the Jordan. I will try to explain various concept and answer common questions as well as show things where people usually make mistakes. One of the question when you model things is how it depends on you go making. However. Model saves a lot of time, but it comes the expense issues that most likely you're not going to understand. The business will and you won't have. Flexible, However, to detailed models also have drawbacks. In most cases, you don't have enough information, or the quote of this information, especially forecast, is low. So we need to find the Golden Needle. As I said previously in the second stage of the courts were going to die relatively deep into the alphabet separations, I think our predictions are going to be more detailed than estimates off. Many research analyst having said that there is no such thing as correct granularity rebel . It's a function off the information to have your goals effort required on the value added. And I think I want to discuss is the following. We're going to cover most topics in details. However, there are some things which will be modelled on Lee Highway Able. The reason for this is because to model such items, you need a lot of information which is usually not available. All such topics are in the vast majority of cases model. However, as we do in this course, I will demonstrate that brokers are also using the same approach waiting in the course. These topics are stock based compensation, goodwill, minority interests, associate and some other. Potentially, we might create a separate course covering this advanced topics. But as of the date off recording, we do not intend to cover these topics year again. All these topics in most cases are modelled, however, also their particular for each company. No, then I think to discuss is that our goal is not to create an ideal model off Google Festival. There is no such thing as an ideal model, but more importantly, I will be using a lot of assumptions, even though I would try to make reasonable assumptions. My goal is not to be super precise. My goal is to show how models are created. Google is just an example. Having said that, I'm sure that at the end of the day we're going to get an excellent model, which will be better than what most brokers do. One more thing to note here. When you watch this video, you might have additional data better visibility than I do when I record this video. This simply because of the time difference. The assumptions. I will be making a reasonable right now, but they might be incorrect by the time you watch this video. So do not judge me for these. As I said, it's not about building a model off Google, but it's about learning how to build financial models. Google is just an example. Speaking of the time when you watch this video, I need to know the following recorded this video in summer off 2019. Even if you wash it in five years, it's likely that from the modeling perspective, nothing Has I first known how to model. Three statements would deceive more than 10 years ago. Nothing has changed things, so don't be afraid of the fact that the model has been built in 2019. Again, it's more about how to build a model rather than building most up to the version. Another topic I wanted to discuss here is how this course is going to look like I have been awarded the alphabets financials. Then I did a quick model making notes along the way. Now I'm going to replicate that model, enhancing it on the go and recording my screen in peril. I will be describing what I'm doing. I think this format will be the most productive to learn tomorrow. Obviously, practice is a better way, but it is something I do not control. And as the next step, I will recommend making a model on your own. But we'll come back to this topic waiter, as mentioned before, I have not published my videos because I think Polish videos are usually boring I to meet some minor edit, but they're really minor. I wasn't polishing every single detail. I think that polished videos make the learning process more difficult, even though the initial intention is the office. Well, that's it with this video. See you in the next one 5. Part 2 - Obtaining financials: let's start by obtaining financials or the company. Usually we use annual reports for this purpose, or we don't want financials from special systems such as Bloomberg. Fact said. I can Thompson and so on. In our case, we need something without paid access. Actually, there is an easy way to get financials off the U. S. Traded companies for free. So what I suggest is that it's open the browser. Let's start in our search engine. S E C. Edgar Company Search. No, what's for the fisting? So here in the books, we need to type off a bit, which is a parent company off Google. So we have different alphabet companies here, but the one we need is actually this one here, so it's click on it. As you can see, the company files a lot of things, but we need. What we need is 10-K I'm filming these in June 2019. That's why I can see here, for example, such filings as Thank You, which is a report for the first quarter. But that's actually not win it. So this one is thank you, but that's not winning what we need. Let's go down. Yep, here thinking, Basically, this is the one we need. So what's click on interactive data? And so here, This way, if we click view, Excel document will be able to export all data to excel. So it's do this. What's way to beat the computer protest? Is it now in this file? We have a lot of different types with tables used to prepare the annual report, but we don't need all of them. Let's take the ones we need. But first, words enable a living because this is sees related, we trusted source and risk of getting infected files of law. So what's hoping that balance sheet first and let's rename it to be s so well, let's go to the next one income statement and what's renaming que eyes. By the way, we have different balance sheets here. The one is for the conservative company, and the other one is for the parent company. So let's move the income statement colder to the bullshit. That's no going for in casual statements. So this is the one we need. Let's rename it to see. And now it's moving closer to other types. Know what select all these taps on and, um, let's go to move, perhaps, and what's moving to a separate Excel file, which I have previously created. So here we go, and now we have older steps we need in one Excel file in the next video, we're going to form it it, By the way, feel free to skip the next lecture. If you're not interested in formatting, you can take the file from that lecture where you have financials in a proper former. With that file, you can start watching revenue video straight away, which is actually the first video of the real model you some. 6. Part 2 - OPTIONAL: Formatting: No, we have all the financials in Excel file. So what we're going to the next is working to form of these worship. So if you know well about formatting in excel, feel free to skip this lecture. Having said that, I think it might be useful for some people to watch it just to see changes we're going to implement. But it's up to you eventually. So what's going to the intra page here? As you can see, we have just general information just stating that its financial more they'll have some origin discussed. Was this region in additional details. So whenever we have hard input numbers, we're going to cover them in blue. Uh, if you have four Moore's, they will be in black. You four have links to another work shades. They're going to be in green. And whenever we, um, have links to another spreadsheets that they're going to be in red, you can actually see examples in the example Comb. I can tell you that we're not going to use any links to external sources here because we don't have any other thing is that some people also use other cowards. For example, when you have dated from external provided they also use red cower. But it's dark red or, if it's, ah, linked to another cell within your work work ship than people might use orange. But again, we're not going to use this two variations. Personally, I think that having too many cowers might be it's too complicated, but we're going to use here only three, and I think what we have here is a switch. This one is used for iterated calculations, so I'm going to explain it away during the course how it works. But for now, it's Leavitt asses the value of it one, and we're going to touch base on this one away during the course. Also on this page would have the company name and disclaimer, but this is quite a common thing. So what? Go now to the financial statements and form of them? The first thing what I suggest is it's a very dangerous thing to do, but I'm going to risk. So it's, um, was hold shoot and would select old taps. So in action we take now is going to be applied to all these selected steps. But be careful with this console first We're going to change the form to Ariel and we're going. Teoh. What's for the front sides? Organ cues. Nine. Then what's havoc on a soap? Um, so that those who to going this year So we have a consistent former throughout the pages, so we'll have one empty corn and then after that, we'll have to comb before, Um, then we'll have going be names off the categories. Then we'll have, um, to empty calls. And then we'll have our years. Eso You'll see that they were going to use all this consulate during the course, and I will show you how their use. So for now, now we have a point. All this forward to old taps. I don't know what. So I have the selected the taps and let's now go and, um, former each in the videotape. So we don't need 2016 years. So was deleted. So but were first note ability, we first need toe a nerve cells. Um, so it's just the emerging all unnecessary thing. So what's moved 2017 to the Komen E. On what do throughout all pages, we have it in the balance sheet now done correctly. Now let's go to the income statement and also move it there and they would do the same in the cash. Full statement. Now what's put years in the first roll, So we'll start with 2017. It's added here four more and then now it's extended to our forecast horizon, which will be to 2028 and also justify just to see how many cones you have selected you can see in the west Side, just from the way from the four more. But just here you can see 11. Andi means that we have a selected 11 calls here, and that's because we're also included 2018. So in total will have, ah, 10 years for forecast forecasts. And just ifwe Prescott control are just to extend my for more. Now it's form of the year. So what we'll do is we need just to say that these are estimates and just type contra one. Go to the dialogue green you and at, um there, ah e symbol and it's going to be is going to work Fine, because we have the names off the taps year, so we don't need actually a reminder saying that this is a consolidated cash flow statement . That's why what I suggest is it's just put here your end 31st off December and then also it stayed that everything is in mewing dollars unless otherwise stated. So just type it and it's we don't need it to be bold years. I think it's a good idea to make them both. Um, no, it's continue for mating, and they just said these port make it italic. And then, um, did you think what will be good things to the next? I think we can deliver this to And then we can just the week off various cones to make them consistent throughout the whole orange. I think it can. You can change it a bit further. So, um, we don't need these two calls at this stage to be so white Soldiers decreased there with on that. It's a game just the week off the corn for our forecast horizon, so we can also will make sure that everything fits on the screen. Um, so we're just copy this one on and we just copied the wine. We have just formative, too old pages. It's also paste the week, so we will make sure that we have the same for month and were consistent throughout the whole model. So what do the same for their brown shit Now we need also to paste comb with so we can will have the same former throat or pages. It's very important is going to save us. Award off a lot of time I going forward and it's going potentially help us to avoid some mistakes, but you'll see we'll have a word of another. Vacations will deal with their consistent will. See how important it is to have consistent former throughout the model. So we have every time in comedy, for example, 2019 year and so on. So, um, just one thing to note here because brown shit is eventually a snapshot in time, we will still can use the same caption because when we say year, enter the first of December, it still works because income statement and casual statement there interval statements while balance. It is a snapshot in time, but we can still use the same optional Furin. Now what did it Now it is I freeze panes, So whenever we move across the pages, we can always see the name off the, um, the name of the category and we can always see years. So it's also free. Spain's here in the sea to Andi. Sit your cell and we'll do the same for the cash flow statement. Now we have no pages we have. The pains are the pains of freeze on what I suggested. Let's take out greedy lines. We do them through unthinking these in page ol. But some people, what they do is they just go to uncover these dis pages in white so they just make the fuel of the cells white. And that's how they eliminate the grid one. But it's wrong. Please don't do this because it's a very unprofessional. And if you want to take out the grid ones don't cover the cells. Just take out the grid lines. That's Ah, that's the correct approach. She just takes 11 action to antique the books. You can actually do this. Also on the view Tap. Now, what's that? Some Some space here to make sure that our pages hooks Well, what's add here? Say that these are assets because we'll have all liabilities and equities further in the section. Now let's have a look at categories. So what? We're having the reporting of Google. They have cash and marketable securities together. They just quit these two wise and bill them. They have total cash and cash equivalents and marketable securities. And as you can see, third line is basically the some off the two previous lines on de so we can replace it with former. By the way, good. Think about reporting in Go go is that whenever you have summaries, very there are no rounding mistakes. Object it previously eso we can we can feel we can easily replace these hard coded numbers with foremost because that that that will be a better approach. So what I suggest doing here is it's type here that this one is some off the previous two analysts and now copy the four more. And, um, what we can also do is we can right now, extend this four more through the whole range. So we'll make sure that everything is consistent and nothing is that just move it up and what's in then this to cashing marketable securities. So it will be clear that this you together make up the makeup of the wine evolve, and that line is going to fall through the model. Now. What's supply Consistent Former through the whole arrange off numbers. So we just select the range where we have numbers. What it suggests is that supply coma style and but we'll have coma style without without any decimals. So I think this way it's going to quite nice. Now what separate, um, types off assets. So we'll have total current assets and it just add a border here. So we will clearly see where we have. Ah, which numbers And, um, as you can see, the some of these four is exactly what we have there in the wind Number 13. But what I suggested would reports everything with foremost. So we'll just some older numbers. And obviously we don't some wines number seven and eight and now let's extend the formal through the whole range. Now this part is going to be known current assets. Now let's make it also bold and what's at some lines, just summarize what this support Oh, non current assets. So it just used former painter and, uh, you know, they're not Teoh, you know, to have the same formal and in order not to do it manually so we can just go pay the formal . And here we can, just some all the numbers, and let's extend to four more. And let's see. Um, let me just double check one thing. Yes, something. Yeah, I think I know what's wrong. I accidentally, um I have accidentally ah, put a former in other non current assets. So it's just my small type of my mistake. So we just add another wine. So it's not. It's not other current assets, So let's make it bold here with you, the same form of that we have four total current assets. Think you should work now with just some old the number here we can, something we're using, the sound for more, but that's a more dangerous approach. It might lead to additional mistake. That's why what I suggest is in such cases, it's better to use manual manually, some all the numbers you need. Now, let's see if some of these two numbers is equal to total assets. It looks like it does now with some of these two lines and let's go back for more. And what actually happened now is that I had the technical error and my excel crashed. That's why you see that I have restarted it and to call the grid lines from before tap. And I'm sorry for these, but nothing, really. You haven't missed anything. Maybe, really? So what it just did now is that our have applied the former for total assets, which is wine. One wine above and two lines go or actually get bolted, actually lying bill. But anyway, what's used this four month for for our grand total for total assets and just I read it now , some lines to make sure that, uh, our assets section is is properly formatted, and it's visually separate from other sections. And now it said another one with do the same for the right side of the balance sheet, which is the liabilities and shareholder secretary. So it's add a caption saying I built shareholders equity with other wind, the same as we need for assets and let's start form of thes section, the first of its separate our current liabilities. So we have currently ability, so it's add a line the border, and now it's replaced the four more for current liabilities with some off items because it just the better approach. It's more professional. No, I actually let me demonstrate you with. If I replaced this with the some form of what's going to happen, is working still to get the same result Mathematically, it works perfectly fine, but issues that when you have some four more, it's easy to type it. But the issue that once you have someone injured some number here I don't like percentage growth and so on, and it might impact your some results. And I think that this might lead to mistakes every said previously. So it's better rather, to use some for more is better. Teoh do manual some some and, um, it just it might save you from Eros. So what? I strongly suggest using this approach. Now let's extend to four more to the whole range. And let's call it no current. Let's go the next section, no current liabilities. In fact, some people call with long term assets. Maybe that that's a even better name. But just to be consistent for now, let's just call it no current liabilities and, uh, let's add additional lines and with other summary for no current liabilities. So it will be total just like total on current my beauties and its ad. Now it's form of these. Make it bolt. And at the top border, um, we have the border having bolt. I'm just used the shortcut control be. And now it's some older numbers in this section of the balance ship. And for our total liabilities, it will be some off total current liabilities and total known current liabilities. And you can see numbers hasn't changed, so it means it means that we most likely I'm sure we did everything correctly here. We don't have any mistakes. So for total liabilities, which use slightly different former So what? I said, yes, it's a double wine, but no, not not there. No, not bold wine, but just to double lines. Because it's not. It's not a grand total, as we have for total assets. Just one off the, um, one of the summaries over these right side of the balance ship. Now we have the export is stockholders equity, and it's also form of the sport and just have a some for total stock holders equity. It's going to be this some off these sections will have only two border and I would just sound all the numbers in this subsection. And now it's also extend the four more through the whole range. Now what's form of the next one, which is a grand total for thes subjection, liabilities and equity. So we're going to replace it with the four more we should the some off total liabilities and totals to colder Secretary. Now, what's the point here? The form of the same as we use for assets. So it will be Topa and double bottom border because it's ah grand total of these subsection actually, just notice that we have no, uh, extended for more for total non Cardiss, it's ah so I think we are more was good here it looks like, at least for basic formatting. We're done now. What did the four we have here? Unnecessary additions. They're relevant for historical financials for 2017 and 2080 But they're not really one for all for custody periods. So what I suggest is I would just take them out and put them as footnotes. So let's go to the bottom of the page now. It's basted here, so no, it will be for Donald number number one, and we'll just based it. And it's going to be 2018 and 2017 respectively. Because as you remember, first we had before we move the cones for your first column was for 2018 and only the 2nd 1 was for 2070. Let's do the same for this convertible preferred stock, so it will be our footnote number two. Just copy. Paste it here and the same one will do for these long course A. Because being Corsi, just call it ABC stock because it will be just easier on and paid in capital, and the rest actually don't need the risk. The rest go to signaled, and it will be off. No Number three. Just paste it here. Um, now I think we can rock sells here. So the organizer and, um, I think we're done now. What we need to do is we need basically to insert footnotes. Let's add them to the comb, see? So it's going to be footnote number two. Joey, let's put it with the with this sign because otherwise Excel treated as minus two, but with the signing treats everything, as as it takes everything after that sex symbol and also for countries Elbows. It should be number one. So good. I think we're good here. Now. Thes numbers should be in blue because they are hard coded as you remember. So let's put them in blue Then the whole thesis section should be also in blue. I just pressed before which repeats my previous action for forming and was do the same year . It's also, um, percent for to appoint the same former and the same here, and we're going to have the same for equity. Okay, I think we are almost done with the balance Shit, because it looks like everything is, um his moral is correctly formatted. I'm just checking if everything if I have me something or not. Um, I think we're good here. But before what you moved to other pages, what may also show we one more thing. In some cases, you have a very long lists on. And for example, if you decide to model everything not in separate taps, but have all financial statements and actually everything else in a one in one worship, though, started in one Cool. Yeah, in my work shit in these cases, what some people do like just on the website in the cone, a near the key lines they put exists. So this way, using control arrows, they will be they. They easily navigate through different sections off the different sections of the book. So thesis justify I we're not going to use it in our model, but just a four year. Some people do this way. So, you know, to navigate through different parts of the model they put exists near key parts and using control a rodeo easily, never g through, um, various parts off their models. So what is it just now is? Let's go to the income statement and former So we have, um, it'll be easier to form a brother. Do busy it informative than to form a battleship. Let's equipment here model was the same things as we did in the balance ship, So let's start with the costs and expenses, because this one, this one is effectively a some off the lines about what it suggests is to have consistent form. So it's moving about these ones and what's replace it with a four more, which will be some off the five Alliance Bill Warwick, Um So it's now extend the former throughout the whole orange. Um, what I think we can do here is, um, with so former everything in a coma style without decimals. So we don't need doors because we mentioned it in their column. B one. We say that it's in years. Dole's now what's so please also, um, other? Yeah, I think I think I have. Yeah, I have I. I had a small mismatch when I was forwarding this one year, which is corrected. No, it's, um, change our expenses. I would delete this one and then at intimidation to the five lines, which are billable costs and expenses. All right, so let's make them italic. And then what's replace for more for income from operations, which will be the revenues minus costs and expenses. And it's also, um, extend this four more. Andi then was do the same from income before, uh, income in Texas. But before we do that Oh, it's also other line for income from operations, so because it's effectively s up summary Onda using um here is trying to find this one. I'm trying Teoh Teoh Group two groups certain I don't recall anyway, the shortcut for these all shift and right arrow. And in this case, in order not to hide things. It's better Teoh. Use these short coat and group them and the in this case you see, like when you press one, it's going to be going to show you better format. And when you pressed two, it's going to show you all the lines. So don't hide things. Don't hydro's or cones instead use thes the separation? So which is a grouping of cells? And because it's easier, it's more visual and just the better approach toward mistakes and that. Sure, I think that was indeed a tap you had that that function. But anyway, it's ah applied the same former for income before taxes and net income. And and then let's replace our are coded number with four Moore's. Now it's extend the before more for incumbent for in convective. But before we do so, just one thing to note is usually these line other income and expense it is we have to deduct the result of this crime from income from operations. But given that Google says directly that its income not expense, if it was minus that would be expense. And otherwise it's income in this case will have to sum this with the income from operations for a lot of competency sexual the other way, because they have that they pay interest, and usually they have more expensive than thinking. But for Google is the other way around. So just be careful with this one to you have to count this with the right sign now for net income is going to be nearing before income. Texas miners providing for income taxes. So it's quite straightforward for revenue because it's a hard coded number. What's make it blue? The same applies for our expenses, for other income and for provision for income. Texas. Everything here should be in blue except for four more hours, and it also like we have everything fine. But you don't need this to line. I think maybe we'll calculate this waiter in the courts. But for now, we we don't really need this tour. And so what? I suggest it was, delete them and it's no moved to the casual statement. Well, you know, one of cases people just to decreed these casual statement from scratch, but we're not going to do we're going to use more professional approach. The we're going to, um, to use the four month which Google uses. So the first thing we're it's just a boy common style without decimals, because we don't need to mention that exit door in $1,000,000 because we have the stated in the Selby one. Now let's separate different parts off the casual statements that we have first cash provided by operating activities, and this one basically consists of three parts, so we'll have net income. Then we have adjustments and changes in that's not liabilities. So for net income, as you can see, this one is equal to the one we have in the in the income statement. So it just link it straight away on and let's extend the four more. And by the way, this should be in green. So it's just, um, our code eat properly, then for adjustments will have the summary. So we're just intend this and changes or just and then these two sections and which some? All the numbers here and then we were reflected in the net cash provided by operating activities. So, um, just some the numbers on E. I think of included everything. Um, no, just, uh, quickly Check. Yes, it seems that everything matches Soak. We can, um, just take this formal in order to save some time for typing. Now let's extend the four more to the horns. Let's make it a bolt and will do the same for adjustments and, um, their income. What's add one more long? Let's make it also bowled on dumb, which is reported the former Ford net cash provided by operating activities. This is going to be some off netting comports adjustments was changes in asset and liabilities and we can extend the form or to the right with the using control are um yeah, I think we're fine here. This number should be in blue and I think this way we are done with operating activity. Let's move to the next one investing activities. So here we're going to the same thing. So it's logically separate summary off the section which is a net cash used investing activities. So it's used a former painter from our operating activity section, and now it's manually, some all the numbers in this section so we'll have ah grown in, not granted but total off thes subsection and we can extend the four more to the right. Yeah, I think, Yeah, it seems that everything is correct. Let's make this number blue. And, uh, let's move to the next one. And, um, let's at some longing So yet who have net cash used of four financing impurities? Um, again, with some put it four more summing all the numbers in this section and, um, and think we can now extend the four more to the right? No, just like the range. And, um, what's copy? The four month from other subsections and we just avoid here. We can do it actually, through a special to not not necessarily through formal painter. So using this one is just another way. Um, sometimes it's even quicker to do it this way. Anyway, let's continue. And we have, uh oh grand total for cash. Full statement, which is basically your cash for in that particular year. And that is a net increase decrease in cash and cash equivalents. It's actually you selected different formats. So with the double border but bottom border, and that's going to be a some off our parenting activities course investing activities was financing activities cause effect off exchange rates. So that's the fourth standalone element. And, let's know, extend the form or to the right. Good. Actually, these numbers to be in blue and the Q numbers should be able to because they are heart input. And now, um, what's also deal with these two lines? So the 1st 1 with cash the beginning of the period, they should be a hard coded number on four cash at the end of the period, we can replace it with the with the form also cash beginning of the period ports on the changes in cash on what? Just 16 This four more to the whole range and for the protection. Make this involved because this is important for our balance sheet. And for the beginning of the period, it's equal to the end of the previous period, so we can extend the four more here, too. Um, now, with these supplemental disclosures casual information, it might be actually a useful information. And because, as you can see here, it's it relates to cash paid for taxes, but not off responsible. Not necessarily when we when we have taxes and interest, you know, income statement It's not necessarily the amount of cash removed with this one's. It just gives us some additional information. But we will touch base on this one. Wait during the course. Now explain off what is the difference in one of the future lectures? But for now, in fact, I don't really think that we need these numbers, so I think we can eventually delete them. They might be useful for for someone else's. But what? We're not going to use them? So what? So it's believed them because, um, well, touch baby. So it in the courts. So I think we are done with the cash full former link. And actually, we're done with the formatting off these workbook. For now, we're going to form a things, a waiter once we build our model. But with the main with the perform itching to start the model, we were done for now. One thing which I wanted him not this year for meeting is very important thing because it makes things much easier to read. It makes things structured and it potentially wants you to avoid mistakes, and also it makes it much easier for people to for external readers to read your models. Eso pay a lot of attention to formally. But having said that, if there a small mismatches and I'm sure that during this course I'll have certain smallness measures in the former, just don't just take them easy. It happens and it's no end of the world, but it's still still try to do best with formally Andi, That's it, basically, for now, and see you in the next video. 7. Part 2 - OPTIONAL: Useful shortcuts and tips in Microsoft Excel (Windows version): in this horse, and I'll like to touch base on one important thing. Shortcuts and also tried to provide some tips on using Excel in real life. Most of the time, instead of using a mouse, people use shortcuts. It saves a lot of time. However, it think things much more complicated for them forever. I'll try to use my most most of the time while I'll be recording the schools. But sometimes I might forget that I need to use my mouth, and in these cases I will be using shortcuts. I'll try to voice over them. But excuse me if I don't use onshore shortcuts is eventually a matter of personal preference. There a pretty common combinations. I'll show some of them in this video. I suggest you start using them. However, there are many other shortcuts which won't cover in this video. I suggest learning them at the next stage. So for now, try start using eight to 10 combinations once they're part off your daily routine at new shortcuts to yours. I do not recommend learning all shortcuts on day one. So what? Start the 1st 1? I'll just go actually, through this least very quickly, because we have a lot of things to cover. And what I suggest is that you go to these days and try using the shortcuts yourself because I'll be doing very high level overview off how they work. So the 1st 1 is entered itself. So we have four more here, As you can see, YouTube waas age do So what if you want to edit this cell, So in this case, we cannot just type it or we can re type this one. But instead of doing the super C F two and now you're inside the cell, you can select, things will shift, you can change things you can edit, you can add whatever you want. But, um, here, if you want to add, for example, um, reference to another self, you just boys press poor sign, and then what have you but one tip here, if you press f two again while you inside the cell, you will be able to navigate within your workbook so you press it Once you'll be you're navigating inside yourself, you press it twice, then you're navigating within your workbook. If you want to return back to navigating within the cell you press again after you, so we would have to You're just switching modes. Either you navigate through the cell or you navigate through your workbook. So this is I hope it's clear with a few words, Go to the next one. If for a four distributive US action usually issues for some complex things you put here this car you want to avoid the same color for I don't for these sell you just press a four here or you want a quiet for the whole orange you present for, so just repeat your action. But it doesn't repeat the watts for more which you and which you've entered. But to beat some other actions, for example, for mating. It works me very well with forwarding. So it's now clear on these, uh, range no fue another thing with. Therefore, if you're inside the cell, for example, this one references I do and you want to make this reference absolute meaning that you add door signs. So whenever you move the so it's going to be or you if you copy, so it's going to be referenced on Lee to lie to In this case, you can manually go here and put doors. Im or alternatively, once you're inside the cell, you can put, you can press. Uh ah, wait where you have, um, you have a reference you can present for in this case, it puts absolute reference for both roles and combs. If you press a four twice, it's going to fix your role. Then it's going to fix your comb. And if you press it for old inferences, so it works this way. First, it fixes our rules and Kohl's. Then it fixes rolls, man. It fixes stones, and then it removed off your or fixing. So I hope it's clear with this one. To let's move to the next one on the next one is recalculate workbook. So he in Excel you have an option which is used for poor, quite large models off manual calculations, because it might be a nightmare if, after every change off your model, Excel has to recalculate all the numbers inside your work. If you work, it is quite. If your workbook is quite heavy in this case, it will take a lot of computer resources and everything my soul down very significantly. In these cases, you put either after Matty. Except data, tables or manual. So what? Me? Just demonstrate how would work. So imagine that we're now in automatic mode. I press nine here. So you see this number is Siri, calculated automatically. This one is recalculated again. No, I would say that I put a manual so I moved here. Eight. You see, nothing happens. Nothing is recalculated automatically. So if I go to the cell, I go inside it on. I press enter then this cell is recalculated. But only this self. For example. This one is referencing this cell and nothing happens. It still retains old value of this cell. So in order to change it, you need to go manually to this. So in Presenter, if you want. Do what? Say which it again to them. Now, if you want to re calculate this one, what you're doing is that you You can use these two buttons. Calculate now or calculated. The difference is that calculate now killed recalculate the whole workbook and Calcavecchia Recalculates Only the ship Andi kill. Great. Now the shortcut for this is if night So what? I perceive night and you see these two numbers regulated. And if I want to recalculate Onley. These particular tap, Then I press shift F nine. So this is how it works. But usually it's used only for complex models. Another option here is actually automatic. Except for data table. This is much more common use option. So imagine that I changed numbers here. Everything is you calculate the only thing which is not recalculated using deception, our data tables as its state in its name. So this is because in data tables, imagine that you have a data table 5 to 5. In this case, it means that for every cell in your data table is going to some numbers and recalculate the whole world book. So in this case is for every time you change something your, um, your excel is going to recalculate everything 25 times. Additionally, in order to calculate these data table so they just tables can also significantly sold down the performance off your machine. Usually you need them when you need, when you already have final results, and because they're just showed you sensitivities. And in these cases, people just put except for data table, then everything works perfectly. But once you need to have a look at what's going on with your data table just percent night , and everything is recalculated perfectly in this case and its way much more convenient. Okay, let's move now to the next one. Continue to the right. So imagine that we need I have prepared a table here. Imagine that we need to kill creativity. Margin. So it's b d, divided by revenue on what's appointed former toe touch, based on how to buy this for moderator on. So now what you can do You can just copy the cell and siddiq the range where we want to put it on. Base it with control C control V or the resection. An easier way to do you. Just like the whole orange and the first cell which is selected. The one which is here in white is going to be once you press control, are the the former in this? I was going to be replicated through the hole in So what I did now instead of and in control, see then selecting the whole injured. Just select the range and impressed control and keep it pressed. And then press are on Ben in this case in this case, the former is extended to the whole ridge. By the way, if you don't, if you have instead of form you have ah hard input number than you again. You select this range press control are and everything in this this heart called The number is going to be extended through the whole ridge. So I hope it's clear with this one can continue down is exactly the same thing. But it just makes extend the form or the value vertical instead of horizontal. Then the next one is for president. So we're having four more here. And if we want to see what are they control with square bracket and you see that it's a mathematical selects to other cells which on which the value of these so depends. So we just show you the president's and actually can do this Using these, they still hear trace presidents and trace dependence. It's a very useful thing. You, when you check your model, you usually use this one. And if you want to remove these arrows, you just put a move. Are also basically I think I have it went wrong, but I can cover it now so dress presidents. So it just shows you which sells contributed to this particular one. And if we, for example, press here praised, betrays dependence, so showed away. This so is referencing to this is a very useful thing when you're checking the model, but another way to check without arrow, but just to navigate through the model and to see the sea which sells contributed to your the calculation off your particulars off the cell you want is using control and the worth square bracket. That's what I did know it shows me exactly these, uh, Mr Cells now, one important thing which I wanted to note here, imagine that you have, um you have numbers in different types, in this case of I've created the stable. So company company be units sold, price and revenues. Obviously the multiplication of this too. But I've spirited through to table, so I have didn't boots on different tap. So what say that in sold multiplied by here, Price and the other way around for company B multiplied by. So we have the foremost correct. But now, if I What's going to happen if I press control square bracket. So here you see I'm a reference to defense. So but their cell history let me just show it again. So if I press control square bracket so I'm reference to the first. So here but here the price is not selected. So what happens is that once you have references, you are the taps. It just they're just sends you to the first value. So here it just shows me only the first value. It doesn't show me it doesn't select me. The price here have just selected it manually. No, But what happens on the next one? Because we switch the warders here. So I'm just pressing control of square bracket and I'm reference to the other tap and on the step is the price is not even selected. That's because again, it just sends you to the first to the first value in your for more. And this is actually more, um, this is a better approach for writing a formal. If your former was it, he usually put on then inputs from other taps first, because then it will be easier to trace them because more or less, you can guess what's inside the cell by pressing F two than it selects you, um, the numbers on this particular time. But it doesn't citic the numbers on the other type. And in this case, it's easier to trace it using control of square bracket. When you have a number, uh, when you have in your four more reference to the other, that is going to make life a bit easier. And by the way, imagine that. Here we begin. We press control of school bracket. We are on the other type and the food to return back to our original cell press five and then enter immediately. It just refer. You just returns you back to the cell from where you came here. So you you press here, and if I've you see it works perfectly. So a five and then enter in this cage, you're going back to your original so control square bracket. You go to their cell on which you presidential. And if I enter, you come back to yourself through this way, you'll be able to track your motto easily. You'll be able to see how it works. And if everything is incorrectly So now let's go with covered these two, let's go to the next one in sort Aztecs and that you have a phone number, right? So it's I don't for the gift was for 44 then 85 90. I'm just making up the numbers. This is the phone number. I first enter and you see like it puts it into different four months. It remove the poor side initially. So if you want. So if you want the numbers starting with mathematical operators if you want. So if you want to put it takes starting with them mathematical operators as a porcupine. Before doing this, you just put this on the symbol on BC like a single quotation mark. And then you put paws 44 again. I don't remember a war number I have there. Is she in this case, It just to go these quotation mark, It just doesn't show it in the cell, but it shows everything after that. So basically, these quotation marks is an indicator in your excel telling the program that all right, whatever you have after these quotation mark and if it is the first sign in yourself, whatever you have in after the Kurdish market should be treated as a text. So this one is a useful one, too. So let's go to the next one. This is an easy once electoral, so shift space selected call control space. So what's go to the next one? Magic l bottom. So basically, when you need to use something, you just you can use your mouth and go to your menu, select sound things and so on, or alternatively, what you can do, you press out and you see, um, you have here some other references. So you have aged for home and for dessert toe. If you breast age, then you're now in the home menu. And then if you want to make it both, for example, you see this one is one, or I know it's not a good example, actually. But anyway, if you want to make it bolt you Chris one and you see it just repeats. This just makes the actions little going with your mouth here, making the changes with their mouth. You can use out bottom and then navigate using these things. Numbers using these are road map, which shows you when you press the butter. People slow now just because I was showing it, but usually for example, if you want Teoh touch base in this one withdrawn. But if you want to compete as a value So it I did it within a second. So it just wants this shortcut become familiar to you. You just you can do this within like it split off a second because you just type it immediately there in your muscle memory. And they're very easy and very useful. It was the lead, this one here and with. So how sure Other shortcuts with bucking and the difference with their control Bat bottom is that you would control. You need to keep control pressed. You're going to hold it with all button. You just press it the reason only to hold it and then press other keyboard other combinations which are required for your for the action to take boys. So it is a very useful thing. Use it very often on with whenever we want to do. If you don't know if you don't know your shortcut, excel gives you tips. So, for example, if I want to go to data tap, I press a and then it tells me what to do next. But with the most common operations. You just millwright them very easily on, for example, as I did a while ago. If I want to press it to import it as a value, it's HVB. So we just very easy. You don't need any any tips from Excel. We just memorized this one, and you can do it easily. So it is a very useful thing. So let's go to the next one. This one is injured there. Insert a row. So the way it works is that it's like I and you see, I wasn't nation by Excel initially, but because I think it's from old version off Excel. But the retained this functionality and then you press are in this easy. The role is inserted automatically. And then if you want to delete this whole, it's right e d. And then you served in try role. So you have this functionality with from all that, So it has been retained and so it's out. I our genes are terrible and then to delete it, it's i e. D are selected. Actually, the whole this is kind of an indication for except that I want to delete this rope. But to be fair, I I'm not really using the shortcuts on doing it slightly different. So what I do that I select the arrow and then I press contra pause. He just gives me more flexibility, and I think that just more convenient for me. And if I want to the literal I, um, instead of using these i e d. What I do just again. I select the the rosebush I want to delete and press control minus contra pulls at the rope and control minus delete them. But the only the only thing is that it's good practice to select your also your indicating that you don't want to add or delete SL. You want to just add and delete arrows. So if pressed contra pause without selecting euro, we just going to give you these dialog window and in order to avoid it, just select arrow straight away or select a cone straightaway on in these cases is not going to give you any dialogue option for then contra pause at zero and control minus the the next one is some up. So, for example, if you want to some of the oldest numbers, you press out and yet you need to hold it and then and equal sign and then enter. Or what you can do is I'd enter Enter. So in this case is going to some all these numbers here. So it's hold out and then ecological sign again then, in this case is going to some all the numbers in this in the zoological group in this call it works. I think it was presented to us easy. It works horizontal itude, so it's ah equal equal. In this case, it's going to add it if America or echoing that enter side. It's up to you. Whenever you use something, hope you want to use it. So what's going on to the next one? And I'm sorry for moving force. We should have a lot of material to cover in any way. You What I recommend is just going through this least on using the shortcut yourself. It will be very useful, so it go to the next one. So Tab obviously moves you to the next to the so right. But if you want to move to the cell on your just press shift, that it just another way to move between the cells and switch between workbooks, it's contracted, but we don't have, in other words, broke up. So what we just created with the contra oil a press control and I created a new a new workbook And if I want to switch between them, I just using control tap. So, as you see here, I hold control. And then I put a step and I'm moving between different workbooks. I can create another one. So book one short gods. So let's go. Go back. So shortcuts, then I press control tab. I mean, book one, I press it again. I mean book to oppressed again. I come back to shortcut sole Control tab is used for this one. Then the next one enter without moving and this one is very useful thing. I'm not actually using this very often, but sometimes it would. It would be very useful, just not in my daily routine. So how it works is that Imagine that we can put something here with, say, 50 right, and we can put it for more this way. So once we press enter, it's good. It's changed the former, but then I'm the selection went to the sell down through this case we need to go back to the soul. So I pressed. Enter that. I need to go back to the cell and select the former and so on. There is an easier way to do this, so imagine that we should believe formal from here. Imagine that if I don't want to go down, I'm just doing this to and then I press contraband. In this case, you see that the selection stayed in the same cell, so I don't need to repeat inactions. Ovitz's a convenient A way to navigate through your work, so I hope it's clear with this one, then a number equivalent off equal sign. So basically what I want to say humans that if you have ah, keyboard with the lump that in this case instead off typing equal sign, you just type poor sign, and then it's going you. Then you put in four more. So in this case, um, nothing really changed because pause doesn't change the value. So we just add a pause here. But it's way much easier because poor sign and poor plus key on your keyboard is bigger than the equal sign. So it's very common to see the formal, starting with WAAS, just because it's just easier to type pause. And instead of equal sign, you can actually start the form with mine assigned to. But then, in this case, is going to change the value of that. So well, Post signed doesn't change the value of the soul. That's why if people usually start for most with the process, so it's very much the same thing as equal, with just more convenient when you type two years poor sign instead of the equal sign. But let's go to the next one. Former painter. So, um, we have we have a point former here so we can do what we can do. The point. This informant to be so just pressing controls that you don't do the action on. And there is a shortcut for these using art a magic button. So it out H b o. Sorry, my I pressed if or accidentally so we wouldn't try it again. So I age FB and then you just select in yourself. You can do to using your mouth, or you can do it using your keyboard. I'm just pressing right arrow and you see that America avoid the same Formoso Foreman painter is Hft. Then if you want to clear all four months, it's basically here clear formals. And in this case, they're short got for this is old age um, e f So you see the reason for mating in this house with the general form. And when I'm saying that in general four months, you just press contra one and you can change four months here you can select whatever you want. So we from pressing control one. Here. You see, we have accounting former with two decimals with this with specific form. But here we have a general former, just because we have just cleared or formless than, um was due to gain. Let's avoid this former then. As you can see, we have accounting now and then. Once we clear for months, then it's generals, so no special property is attached to the sound. That's why it's in general format. Let's move to the next one. Pay special. This is a very useful thing. So whenever you wanna pay special either for more war values, you go to pay special and select different things you can do with the cold with you can do what I think will be using this quite often. But the shortcut for this is it age he and then you select your options off pace values. It's again via game for four more seats f And then if you want to do something which is not listed here, you just press s and select and select from this from these thief's. So whatever you have here highlighting with, um with the wine under the weather. So basically, that's what you need for values its V here for foremost it's f for a cone with its double you as you C W has a wine below it. And if I put double you and then I pressed enter, for example, then it's going to put the same with to this particular this particular cone. We had this language for these cones. That's why nothing has changed. What's now moved to the next one. And the next one is insert the work shit. So shift everywhere and so impressing shift if you will and you see new work on you work shit is inserted. So shift dealer and again. So we're inserting this way. Um, now move between worksheets. You hold control. You go with the page up and page down. That's how you navigate through your workbook. Next one. Making things bought its control. Be making things italic control I under I and control you an open dialogue for making down . That's what I showed its control want. And here you select different options. Fuel, border and someone's own, um then additional number style. So it's not additional after former. So basically, what's going on here is that if you won't two foreign things in a specific way, you can use this panel. Or alternatively, you can do like, ah, hold control shift and then one for number for more than 34 data for month and 4% for. And by the way, when it shows when I pressed control ship three, its data former So, Aziz, you can see here just actually at years. Also, here it's 1909 That's because the excel, I think it calculates from the 1st January off 19 0 but you can check online, but something going vessel. Um, if we put, um, the number I'd in a week 13 May 2019 in this case and the poor general forward So just says me for 43,650 Dean s O. It just says how many dates must from the reference deed, which is as if I'm not mistaking. It's the first of January off 1901 or 19 0 so just says how many days parts and because we had 3000 500 years old just reference how many days passed from our reference date. That's why when once we're buoy data form on date former here we can see that it shows that that it's for 31st of July 1909 Eso was go to the next one moved to the beginning to the end. So it's control home brings me to the first sell off the worked up and controlling to their lost edit itself. So are what's so in industry. If we assume that off the youthful space off you're worked up so control home to the first cell and control and they're also off the huge space off these particular depth. So quick moves. So you hold control and then you go using your arrows. You can never get easily through your three a table to your workbooks or control. You see, This way I can easily navigate to the edges off off the value range. So if you I go with control more up or control down. So I'm going to the edges off this particular local or into to the edges off this particular a role. Um, let me just go here. You see, like I'm never getting through the the first and the worst value off the continues French. So I hope it's clear again. Let's go to the next one. So this one actually covered this topic here. So as you remember, four more straits presidents and trace dependence. So we've covered this one than page layout for printing. So what I mean here is that you can do go to this view, Onda. Just your worked up. So whenever you send pages for bringing, which is a very common thing to do when you check your model, um, some people prefer this way some people don't, but it's a good practice. Once you finish a model, prepare your your model the way that so that people can easily print outputs and you're doing this using these step on. And also another thing is using this page oil. You can use print titles you can select print area. There are a lot of options, so you can do feed to one page. It's very useful thing. Just practice this one or another thing is Rose to repeat on top. If you have a long tables, they're useful to just experiment with these with these settings and you'll be able to make it models quite beautiful this way. Way um, read. Read the communication on. I think you make your pages beautiful and so And when you bring them, they work. They work nice. Well, that's it with the main shortcuts. What I suggest is that once you implement them, you were a daily routine. What you can do is just selecting your search engine type, financial modelling, short cards or best Excel short cards and see there is something useful for you just the move through different, different pages, very stricken nations and see if something might be additionally useful for your daily routine. One more thing, which I wanted to touch base on in this video, is that there are plug ins which changed the behavior off your Excel, which adds additional functionalities which, as additional shortcuts for these functionalities, which might be is for you can actually find some of them for free, um, on the way. But be careful installing them in order to avoid infecting your PC. How if I have time by the end of this course, I might also add, um, a video on how to create the Sporkin's yourself so you can actually do that. It's quite it's relatively easy. So in this this way, you'll be able to, after mate some of the functions and make them easier. So basically, you might add additional tap here. Or you might add additional shortcuts using various macro. You can move this one thing, this panel to the bottom or it can enlarge it. There are a lot of options, and I might at some point in time I might add additional video on how to do this. But I guess for this particular lesson we are done and see you in the next one 8. Part 2 - OPTIONAL: Ways to structure a model: in this video director touch base on some ways you can build your model. It's not a best practice lesson, since I think that learning best practices requires much more time and usually comes actual with practice. Also packing bits practices in one lesson might be not the brightest idea. What I recommend is once you finish the course new search on financial modelling biz practices and try to implement various practices one by one. But I hope that our touch base on most of best practices while we go through the model. So okay, let's start. What you see here is, in fact, the version of the model in resources section for this particular isn't. But are we just showing how it works? But this is one of the files in one of the final essence off this course, and I'll be showing everything's based on this file. In general, there are very supporters on how to build a model. All of them have advantages and disadvantages. It's eventually the matter off your preferences preferences off other people who are going to use your model and also the standard off the organization you work for or the standards off if you're behind, it depends on your circumstances, but in general it's the matter. Off preferences. So let's just start with the first question, which is what's better, one page model or multiple pages model? Personally, I prefer multiple pages model. But don't get me wrong Ive era, for example, over 10 pages in the key part of the model that becomes very challenging to navigate through the model. Some people prefer one page models, and in fact, the Russell advantages were going page models. But I personally think that the model group Biological Works is a better option. As you can see here, I prefer having a dedicated work shit in Excel for each statement. So this one for balance shit income statement, cash for statement, then adjudicate it, um, worksheets for valuation. So this one and then here we have dedicated worksheets for a logical groups off calculations, for example. We have over him for operating model. We have a financing tap taxes, other calculations. Consensus is something what you won't have in your models it just for but in particular for these scores. But you you won't have this one unless you want to do consensus manually. yourself and do it on a separate app. It's up to you. You can do it this way to But again, there is no wrong or right approach. Choose the one with you to best approach. I have just described works best for me, and I actually based on my practice. Most people prefer the same approach. However. I've seen people who hate multiple pages, and they have their point. It's it's their preference. They prefer doing everything on one page. And as I said, I prefer having multiple pages, but no, too many pages, and I prefer pages oak grouped by so I don't watch it. Also, I think is really coming. Adding dividers for sections, for example, you might have a lot of data from declined in different source files, and you might wish to keep all them in your model. In such case, you can create a section divider and put all sources afterwards, for example. Um, just have a look. You you might have. I don't know 50 different pages with different information here, but what would you want to go not to make? It will be very bad practice if you group everything in your main part of the model. So what I would recommend in such cases just do dio section divider and call it. I don't like your main part of the model and then you'll have another divider and call it Support where I have it. I've got it here. So, for example, you can do it this way. Support, and then after these support step, you'll have a lot of different taps with with the information from your client. And I think this way you can logically group your model and have multiple pages. I think that it's just a better approach and in certain ways, am having linked to it's total files is very might be very complicated. So if you bring your files to your model, which is not a bad idea, just put them after these support page. Or just you can say name it information from the coin. It's up to you. But this way you won't have too many pages, and it will be easy to navigate through the model because you have the main part of the model with the home in of 812345678 nine toe have more wise than 10 pages that in your main part of the modeling will be easy to navigate between them because they're so are they. They have relevant calculations by the logic of that calculations, and you'll have all the source files after the support tab, and then your mother will be very user friendly. Having said that, I like the models where you have multiple pages. I do not like the modern way people create a tap for every calculation. If you have two minute haps, it's really your model becomes mess. For example, in this part of the model you if you have more than 10. As I said, perhaps it might be not the brightest idea, because it will be really challenging to navigate through the sections off the model. I know that some people, what they do is they create a separate app for group of calculation. For example, they might have attacked for working capital, a tap for KPIX worksheet, for course. Just one thing to note. I sometimes say tap. Sometimes I say worshipped. But these are synonyms, so coming back to the point, they they might have worshipped for cause virtual for raving and so on. If your calculations for each of these categories is over 1000 twice as long, then it might be not such a bad idea to have a separate tap for each of these groups. Otherwise, I think it's more convenient to model things in taps grouped by logic. Eso. As I said, you won't have a separate tapped for working capital KPIX Cost and revenue. You will put all these calculations into these operating model, as we did here in our model. And then we also have other calculations which are not. We do not belong to a pretty model, for example, like dip financing, equity financing, all the things they're model in financing tap old taxes are modeled in Taxi Step and so on , so on. So this way we have things modeled in theological groups, and we don't have too many tabs. And it's easy to navigate between between taps and whenever we want to find something, we can more of us guess where that but the cure calculation will be in our model. I guess that's it with the question off having one page vs multiple page, the only thing which actually forgot to notice. I do not think that it's a good idea to cover cold your taps. It might be better to call cold your dividers because it will be easy to navigate. But having said that, if you like color coded taps, then feel free to implement these. For example, you might cover called your statements. You might want them in one code. To you, it's It's your logic, you went whatever works best for you. But personally, I prefer are not cover tops on but coward section dividers Point I wanted to discuss here, and that is which sign you use for expenses. Some people prefer showing negative sign for all close and positive sign for all incomes. Some people prefer to model everything with a positive sign, then reflect a negative effect off expenses in four months. Again, no right or wrong, approach my preferences, modeling everything with the positive sign, except for cash for statement and what we just show you, for example, hearing the balance, she or in income statement, we have expenses with positive sign. That's my preference. And then, in foremost, I, as you can see here, I have a negative sign just reflecting the nature off expenses, and in fact, this is the way off a bit reports their financials to. And they're not only alphabet and I can say that I would have all the companies prefer the same logic. The only thing is in cash for statement on, Actually, in other types with which relate to cash cash balls, we have, ah, things model. The way they affect you can increase your cash levels. Then they're modelled with positive sign. If they decrease your cash levels, then they are modeled with negative sign. And as I said, this is actually also the way alphabet chose their financials. So they have, OH expenses, for example, with point of sign in their reports. But when it comes to the casual statement, they put negative sign for things which decreases cash on. They put point of sign for things which increase cash. I think it's easier to avoid mistakes when you build models this week, however, people who prefer our modeling expenses with negative sign usually have the same argument. Let me repeat. There is no right or wrong approach. Just always be careful with signs. It is one off the most common mistakes, which might lead to dramatic effects. I've seen a lot of smart people committing such mistakes. Sometimes these mistakes ruin deals and as a side note, also be careful with currencies, especially when you build merger models off. Seen very smart people making terrible mistakes with currencies. Just double check your numbers. It's better to spend more time modeling on checking things while we go through your model. Rather than making really silly mistake, which in fact ruins all your efforts, you can build a brilliant model and make it one silly mistake, which will ruin everything. And also, it's not all, actually only casual statement when we have ah, valuation pages. I also prefer showing here the way they effect your cash levels because, in fact, that your valuation big is very much your is kind of an A work off your cash for page. So here it's more or less a logical you will have the same approach. So if something decreases second Schwebel, then you put it with a negative sign and then you sum everything up. But if something increases your cash, whoever's name deported with a positive sign. But we'll be discussing this topic. Waiter once we go through the model as we build our be commending on this topic again, and I will be providing some other tips as we go to a couple of other things to note. Most importantly, trying not to write complicated for Morse rule of thumb here Is that not more than one calculation for life chance per wine, but only if these calculations are apparent. So if an external viewer after two seconds looking at this for more can understand what's going on, then it's fine. But if it takes more than I know 23 seconds, just try to split it. My wines. And it's a very much wiser to have calculations in multiple lines rather than putting like 2030 lines into one line, you know, and make the for more super complicated and not readable. I actually acknowledged that I behaved not as the best person when when I was an analyst, because I liked complex formals. But then, after a while I realized that it just increases the chances off making a mistake. We were humans, after all, and we all make mistakes. Trust me, everyone makes mistakes. You just need to do one minor tiny error in the financial model, and it's my drawing the whole year model. Just always remember this trying not to put a water off calculations into one wine because it increases your chances off making a mistake also respect their colleagues. It would take much more time for them to understand your calculations. If you put a lot of different things into one while, just break them into several lines and it will be easier for them to read it, and it will also decrease a chance off. You're making a mistake in your model, By the way, since we touch based on foremost on, never, ever use hard coded numbers in your models. Never, ever. It's a scene. The only exception is you can put constant numbers, such as the number of months per year or number of hours per day. Otherwise, in all of the cases, there is no excuse for using car coat number thing calculations, and I beg you, do not ever put hard coded numbers. NGO four Moore's. If you want to make certain assumption, just put it in a separate line and make it obvious this is the worst possible thing. You can do. Do not put our coated numbers in your foremost to note. Here is, whenever you have a change in for more within narrow, it might be a wise idea to indicate it. Somehow, I think it's quite common practice to put a border around the cell, where you have changing for more. It's not very critical, which four months you years, but just be consistent throughout your model. You can, for example, put yo few in the cell where you you have the four more change but just be consistent and make it obvious for people that there is a change in for more and the logic of calculation changes. But we'll touch base on this one as we go through the model and you'll see that I will be using these quite often. And I'll be actually giving other tips as we go through the model. So I hope that they will be useful now. Another major think to discuss is hope to do with assumptions. Some people prefer having a dedicated page for all assumptions and glynco assumptions from there to your calculations. Personally, I do not like this approach. I prefer having assumptions way they are over for calculations. I think constant switching between tabs is a terrible idea, which takes a lot of attention and might lead to mistakes. In fact, the main argument off people preferring a dedicated assumption speech is that it reduces in mistakes. And to be fair, they have a point. I agree that it decreases the chance off making a logical mistakes, but I find such an approach extremely cumbersome. So I prefer having assumptions exactly where they are used. By the way to mitigate potential mistakes. It might make sense to have an assumption page separate Assumption page and put their links to assumptions used in calculation pages. You can add such links once you finish your particular calculation section. For example, Once you finish your revenue calculation, you can put all assumptions. You can link assumptions to that separate assumption page. And actually, the reason behind having a several assumption page is that in certain cases you might have same assumptions to be used for different wines off calculations, For example, I don't inflation rate and C p I, and it's a terrible idea to have one input enter twice as a hard coded number to reiterations you might end up having different numbers for the same thing. Let's go to enhancement. I think here we had a lot of different assumptions. So let's go here and we'll have your market share off Google properties. Right? So we might want my do is create a separate tablets called assumptions. Um Oh, as always, we actually need to copy this one here. So, um, we might have a section raving you assumptions, and then we can go here and put Google properties appropriate. Is raving you brokerages intern at market share. And in this game, in this situation, we can go back here and link the numbers form here. We just put it them as values on and, um, they should be in green because they come from the other page. So, this way, once you do this, you'll have always functions on one page, and if something needs to be updated, you can link the numbers to here. So this is this is this might help you to to me, to give ish where you have 11 set off assumptions to be used in different calculations and you update one assumptions and you don't have the other one, So it might all you to mitigate a logical mistakes. And once you being the speech, it will help you to link things correctly. For example, with the city, I just so it's imagine that we have here, CP. I read and it goes I don't just create here. Oh, actual with severe. It might be a good idea to put all city I sanctions here because have other things which we should be reused for other calculations. I don't, for example, these go go properties at market share. I should be used for other calculations. You might use them, for example, Here, you might drink here numbers from this assumption speech. And this way, once you updated them here, there will be updated automatically below two because they will be updated and on assumptions Page, and then they will come here and will be updated to I mean, you can you can put clings directly from here. You can put them to the cell if you need, but it's up to you. But this is just the way for you to double check. Thinks you can create another assumption, step and put always sumption there and also people will be able to compare assumption straight away and just impaired numbers and CEO of the neurological mistakes in the numbers . For example, some things might be linked, and once you see them on one page, you'll be able to compare one things to other things, and you'll be able immediately to see that they might. For example, they might be certain mistakes in your assumptions where you assume things which go about 100% and so on. It might be more difficult to see these things when they are in different places throughout the model. But once you put them, once you bring everything together in one place, you'll be immediately ill. Be able immediately. See if there a certain mismatches on auto. I hope that the water here is clear, but it's up to you feel free to model things the way it's more convenient for you. Just one of the ideas to avoid mistakes is to prepare this assumptions page when you finished certain logical works or otherwise. I mean, feel free to have a one assumption page and put all assumptions hard import assumptions here, and from here they're going to fall for the model just implement the one which is more convenient for you with one more suitable in your particular circumstances. Okay, Before we end this video with me, just note a couple of other mining things. I do not recommend having empty cones between year calls. For example. Here, as you see, we have the numbers will have years straight, one after another one. Some people would they do is that that they create an intercom here. Andi, they make it small, so they have visually, um, they have visually breaks between years. I do not recommend doing this because it will make much more difficult for you to update your for most. If you want this way, feel free to do this, but I just don't recommend doing it this way. I think it's no so convenient thing, but it's up to you. It's your model. Also. It's a wise idea to have the same structure throughout the model. For example, here as you can see, what you just delete this too. For example, here, as you can see, our 2020 is in the comb age, and what I mean is that throughout your model, every time you have numbers in 2020. They should be in the corn age. For example, let's go to income stable two into 20 corn age here the same Texas the same. So you have this way. Your model consistent on it, will make it much more easier for you to check the numbers. And if you also make it easier for you, just put a mistake or misting starting cases. And I highly recommend, following this rule having the structure of the model consistent through all taps. But if you if you do everything in 11 tap in one work shit, then you won't have this problem. So it's one of the advantages of that approach, but it will be much more difficult to navigate through the model. So again it's up to you. There is no right or wrong approach. I strongly suggests having the same structure throughout the pages, so all years are always in the same cones. It's just way much more easier. Teoh check the model this way, and it just increases your chances off building a model without mistakes. That's it. And as I said previously, well, as we go through the model, I'll be giving some other tips, and I hope that they will be useful. But for now, with model structuring, I think we covered the main topics and see you soon. 9. Part 2 - Understanding how companies operate: in this video, we're going to discuss how companies operate from the corporate finance standpoint, I think that understanding concepts off corporate finance is essential for anyone interested in financial modelling, as it will allow you to understand key building books associated with financial modelling. This understanding Help me what? And I think this knowledge is much more important rather than memorizing Foremost. If you know corporate finance, well, feel free to skip this lecture. What start? We're going to show it simplified picture here because it's a conceptual lecture. See, this course is around Google or alphabet, as it's called. Now let's continue with it as an example off a for profit company. We're going to take a helicopter of you here. Google, as a company, has different stakeholders up. We don't confuse it with shareholders. Shareholders are just one off the stakeholders, but it's more complicated. Notion, actually, the big picture for you to understand any company is eventually a framework for interaction between stakeholders. Now what's happened high level look at who are their stakeholders at Google or any other for profit company again, it's a very, however, view from the angle of the financial modelling. First clients of Google. Since we're working a late from the financial modeling perspective by coins, I mean companies or individuals who pay monitor Google, they're being money either for ads or for quality services or for devices or for something else to be fair. Google. Maybe not the best example here because most people are using the services of Google for free. These services are search Gmail drive and so on. But for the purposes off the financial modelling, we're focusing on the clients who paid money. These are advertisers, buyers of phones produced by Google and actually even buyers off paid version off Google Drive and so on. So on. Let me give you another example, which, which is much easier to understand. Apple clients off Apple are the ones who buy iPhones, iPads Macs, its cultural isn't and so on, and they're one of this stakeholders edible. No, let's have a look at the second building book to serve its clients, and money go has to do a lot of things in the background. For example, they need to create these services and goods they need to run servers they need to pay to the sales force to promote the services. To perform all these Google interacts with either its suppliers or its employees, which are the stakeholders in the second book, Let's move to the next building book. Here we have the capital or, as some people say, finance providers, Visa shareholders in very shape and form. And these are deaf providers again in different ways. Okay, And there was. Building Book is an environment in which we operate. I'll simplified here and call this building board government. It might be more complicated than simply government, but big picture for financial model. We're going to focus on government as they stakeholder because we pay taxes, which are reflected in all model. Before we dive deeper into each of these books, I just want to notice that our first and second books together make up the operational model. We're going to model these two books in conjunction with each other, just for your information. When people say in the Price Valley or foreign value, they mean the value which is generated here, which is calculated using operational model and from this enterprise, barely. We get to equity value off to considering elements in our third building walk, which is kept a providers. Now let's dive a bit deeper. Let's start with the first book. I guess it's the easiest. One will have clients who pay us for services or route. The rising, the model related to this book, our revenue, of course accounts, receivables deferred, driven you and so on. We're going to discuss each of this wine waiter in the course morning to the second book. I have to notice that it's slightly more complicated. As I said before, we have to significant companies here, some wires and employees. You can think about them the following way. If we cure the third party expenses, then we pay to suppliers. And if we cure internal expenses, then we're paging. Please. Supporters can provide goods or services, and we pay for them. It might be Liquori City raw material services like and sampling your phone. It might be even employees rent and so on. So on. The company also obviously have to pay for it to its employees. Andi. It has paid for their patients for the social benefits and so on. So please know that they might be other expenses. For example, Danny, but daring, sudden way a result off. Work off either your employees or your supplies. Good. I hope that it's clear. With the 1st 2 building books. Now it's switched to the 3rd 1 Capital providers. For a company to operate it needs certain funds. Big picture. These funds are provided either by equity investors or depth investors. In the case of equity investment, you take the main risk. In general, we control operations of the company. If you perform well, you're well rewarded. If if performances, so so, then you're not well paid or even with money. In the case of them, you're better secure. Usually you receive a fixed amount every year irrespective, or the performance of the company, meaning that if the company is what's making you're getting your money at the expense of equity capital providers. However, your upside is limited. If the company performs fantastically well, then what you get is still the same around as if the company performance was average and you receive your money back after a while. I think Guru is an excellent example off a company in which you would better be an early stage investor. What say that you had a chance to buy shares or bones off Google in 2000 and nine. You're definitely better buy stocks because the value of these shares increased dramatically. While if you have bought point Manuel bombs, you would get a fixed interest and that's it. Please know that they might be instruments which are somewhere in between depth and equity , such as prefer shares. They should be viewed case by case. Okay, there is one more thing I want to discuss in this book, which is not necessarily related to kept a provision but usually appears when we speak about capital providers. This is your relationship with the companies you do not control. We'll have to keep things to consider here. Minority interest and associates. When your key injuries is a portion off your operations, which shareholders do not hold. Let's say you're 90% off a company. You consolidate its operations, meaning that you put 100% of its revenue 100% of its assets and so on. On your accounts, however, there is a 10% stake which doesn't belong to you. You have to remember about this 10% when you build your model, we're going to review this topic in slightly more details. When we were going to the evaluation and I think associates here, we're kind off in the opposite situation. What say we have a 30% stake in the company? We do not control it, but we have significant influence on in this case. We cannot consolidate it, but we still have to show on our statement effect of these 30% stake. We're going to stop. Think about this wine waiter in the course, but I want you to remember about both minority and associates when you build a model. Okay, now we have reached our book Relationships with the government. This one is simple to understand won't start short. We pay Texas, and sometimes fines would be a lot of different Texas income tax, property tax and so on. So on, even though it's easy to understand. Conceptually, it's a complicated topic over which you usually have minimal visibility when you build a model. In some instances, union model tax lines in the tails, but the vast majority off models have very high level tax calculations. That's it. With this video, I hope I explained the key concepts. If something was not clear, who tried to catch up as we build the model and see you soon 10. Part 2 - Revenue: Let's start building our operating model. We're going to forecast raving if s. And then we're going to switch to course forecasts. So first, let's create in New Tap and Koide om for a grating model. What we need to the first is a copy, the first role from one off other spreadsheets. So it's good for pace and then basically based everything. This way, we'll make sure that we have consistent structure across the pages. What I mean is that in this way, for example, will be able to make sure that on every type in corn, G will have to 90 numbers in Konkey, for example, have 2023 numbers. We should also based come with here just to make sure that were consistent across the pages . I mean, it's not just makes things look better. Next thing we need to do is what's at a section divider. And for these, I suggest using this car. It's called this one, leaving you. Let's make it boat with control me. Then, um, other thing we can do is let's go to view, um, then let's take out here greedy wines and then was free Spain, so it will be easier for us to navigate across the page as mentioned before. At this stage of the course, we're going to use broken 10 cents with numbers for revenue. Waiting in the course will prepare revenue forecasts ourselves. The direction behind taking brokering consensus now, instead of building forecasts ourselves, is that revenue forecasts are very company specific, and to perform this exercise will have to spend a lot of time. I'm afraid that by the time when we finish reading forecast, some people taking the schools might be worst, and it would be difficult for them to focus on Building three. Statement or the witches are will go at this stage of the course to simplify. It will use consensus numbers waiting the course. We're going to come back to this topic in order to get this consensus numbers or actually to get brokered numbers from which we're going to get consensus numbers we need to use specific systems, which provides such numbers, for example, might be Bloomberg or I can or fax it and so on. Or we can build this working give these numbers manually from broken report. I've decided to get the numbers manually from broker reports just to show how it's done. To do so will take three broker report and extract the data we need for the avoidance of doubt. There is no rule saying that we need to take three brokers. It can be to brokers for brokers 5 10 and so on. So on the same time, I have decided to go with three random brokers to which I have access. But before we dive deeper, what may 1st show what is a broken report? Since some off the course participants might be unaware how they look like what takes a random broker and random company to see research reports. Let's open the browser and type. For example, Apple, Morgan Stanley and pdf. I'm just I just made these before. Actually, I talked PFG instead of PdF. But anyway, not a big deal. Eso, if we take this thing's combination, will be given with certain links by search engine on Justin old here. Usually broken notes are not publicly available. They are distributed to quienes off brokers for their consideration, but some of them sometimes are available through search engines for our suits situation. We just need any report off any company just to discuss the structure off this reports and how they were quite so. It's fine if we take, if you just click the first name. So that's why I decided, just as one of the competitors to Google just took Apple, one of the workers Morgan Stanley type PDF and I received the link to the report we need. So let's go for was for the first thing. However, before we click it no one thing. Legislation in some countries might impose certain limitations on the usual brokerage. In most cases, this limitations are designed to make sure that nonprofessionals don't base their decisions . Your investment decisions on opinions off other people are there not manipulated by the people. This an education program. And I believe that there are no issues by browsing thesis reports, however, since I don't know your geography and I'm not aware in that geography. So since it's beyond my control, I did this matter to you. If you can access this, reports are not on my side. Given my qualifications and my current location, there are no issues. No, If everything is fine on your side with just click the first think of all right, actually did, Um, and then you go to a report like this. So it's just have What is this report? The separate are effectively in compression off view off an amorous or a group of analysts on whether the stock is fairly valued or or it's not. Or is there an irrational in purchasing this talk? Or it makes sense to you, so it and so on. As you can see in this report, there is a section for stock rating. Usually it's buy, hold or sell, but some brokers might use other names in particular. We can see that Morgan Stanley uses overweight, underway core quantification, but eventually it's more or less the same thing. I think even in this quaint villages provide Ah, that overweight is basically by an underweight is cell and so on. Then, in this report, we can see Target Price, which, according to their and our sees, this talk might reach at some point in time. In usually it's within the next 12 months. Also, one important thing to know here is the date off. Their report, in particular the stripper, has been issued on August 1st 2018. Usually, such reports are issued off the quarterly reporting, all off the specific events. For example, Apple holds worldwide developer conference every June, where they announce some developments and or obviously, afternoon phone release. Brokers use your issue. New Reppert issue updates to the report. So let's just brought the report and quickly see what's inside. So on the first page, usually you can find summary off the report with the key ideas presented here. Key investment. Um, this is also our described here. In short, then let's go to other pages. So after the summary, usually you can find more details on the key ideas. Mohsen Calculation Some additional description. Were you in some reports as being particularly this one, you can find additional valuation details. Or, as you can see, Morgan Stanley Ron's here scenario analysis. We're going to touch base on stinar the analysis later in the course, but scenario announces not always presented anyway. If you are see very scenarios, just usually what happens is that the however overview off optimistic and in this case is Boston area. The Oran pessimistic scenarios, and but the rest of the report it describes base case scenario so, But as I said, we're going quickly Touch base on this one in the in one of the next parts off the course. So let's just go to other pages. You can see here they have risk. But anyway, it depends on on edge particular broker. They all have their former four report. They'll have their the structure. But usually you have somebody on the first page is then additional details on the next pages. Some additional takes additional information on the next pages. They just basically, they elaborate on what they've shown in the summary digits provide additional details on then its full buy some financials. So basically they have here Incan statement. They have So the operating metrics. So you split by divisions and so on, so on. And they have, um also, you hear additional statements. You see the calculate growth rates, they have balance sheet. They have cash full statement on Not always. Research reports contain financials. They not always they contain extracts from the model, but usually they do Onda. That's basically what we need for our situation are exactly these financials. Well, that's it. With with this report, I think we have reached Yes, we have reached the end of the report, even eight pages here. We think I think we have four pages of the screamer. There are some interesting details there, but that's not what we need. As I said, what we need to take from here or on the financial statements, But obviously we don't need to take it for Apple. Apple was just as an example. I just want to demonstrate how research ship or so quick. So it's which back to alphabet And what I did is I have the wounded, random reports and copy pasted financial statements to Excel are you would go back to excel and here you can find these financial statements for alphabet, not for Apple. As I said, Apple was just just just an illustrative example. We need no to analyze alphabet. And if you go here and click on height, go to consensus step. Um, you'll see. You'll see basically the statements, the information we need for our analysis. So this stop is split into two logical groups On top of the spreadsheet, you can see the numbers were going to use. So here, basically these are already the numbers ready for copy pasting. Um, and these are the numbers we're going to use in our model and thes numbers are basically linked to detailed financial statements from broker reports, which are actually presented right below them. The numbers from broken directly, as it would say, What kind of time Buying another game. Think with the report note on any what it just copy, pasted their information directly to accept. And just for your information, we have three brokers, so we have watched broker. Then, um, we go down, we have medium broker. And then, um, we have small broker. Thesis is I've just probably to name the broken this way because it's more with shows the size of the company could have named them broker one broker Teoh Broker three. While I just decided to for the categorization watch, medium and small as mentioned before I have taken three random broker ripples, I wasn't sure picking them since I wanted to show your challenges, which will cure along the way. Please know that I have copy pasted little financial statements as they were presented in the reports without changing them. That's why they all have different formats. They were also certain mismatches in those reports. However, I with them without changes because this Miss mash is were not material. And I wanted to show you the challenges which might happen along the way, since my goes to show how models are created in practice and not in ideal situation, not knowing Polish environment. Onda these mistakes which you face You face this mistakes every day and this is part off your, ah daily routine. So just to give you an example, sometimes the numbers do not match and clearly theories one. They should be one more lying, which is not presented here. But anyway, as I said, these mistakes on it, not material, and I've just copy basic the numbers as you could have seen them in the report. A couple things to note first. By the time you watch this video, the statements will be outdated, since alphabet would have issued new financials. But our goal is to learn how to build financial models, not to build the most up to date version, since these numbers in the report are purely for illustrative purposes. The reason. No difference. When the reports have been issued, we could have taken the reports from 2015 and the process would have no change significantly. Also, you might remember my point regarding limitations in legislation in some countries. So it might potentially help some of you if you have access only two numbers without other information contained in that. But I leave this matter to you, since you know institution better. No, let's go to go back to O attempt up. And it's basically start, um, putting everything here fist. But anyone quote or even used in the model, So just type it here. We'll meet this line since at a later stage, we're going to prepare revenue forecast ourselves, and we'll have a switcher to select an option. But to make sure that we do need to change foremost every time we select between auctions, we need basically this one, which is going to fall through the model. Then the next thing we need to do is what's out here lying cold approach number one, broker consensus and be worried to its other wine leaving you, um, school. Since the girls, uh, it's, um, let's make it it a week, and it's at some intonation, and right below it would say, um, let's put here names for brokers. So it will be broker orange. You have broker, medium and broker. Small. It's F here. Dates when they were issued, I think Washington was issued in, um, May 2019 and the other to worry should in April. I'm not mistaken, some that important, but it's good to have. So it's just changed a bit. The four month and I think I had Yeah, the so dishonest is correct. Um, no. What's that? This special form of four. Our, um, broker numbers. We're going to use it, Throw the model. I think we can do this way. You can call the style without decimals, and the number should be in blue because we're going to copy, Paste them from consensus Step. Now let's go to consensus. Step on. But just take grab these numbers, go back to a lamb. We'll hear a special based values, and here we go. We have the numbers then, um, let's make consensus from these numbers. And when I say consensus it it's that you're very simple thing. We're going just to average the numbers who was? Think average and let's go. So that's That's basically how we get consensus. But just for your information, There is no rule that we have to take an average. Some people take medium. It's perfectly fine. It's absolutely okay. You can excludes even some brokers if you think that they're out wires or you think that they should not be excluded. The only thing just make sure that you somewhere known that this book broker is not included in your consensus or is excluded from your average calculation. Just make sure that the reader can understand that which, um, which brokers are included in which Arnold. So now it's just copy this for more and extended to throw the whole INGE, and we can immediately issue with the numbers in 2026 20 we're going to do with these just in a minute. But before we do this, let me go to the income statement and, um, take the historical numbers from here and put them as links. Here we go to a special basting, um, they should be almost Oh, they being green. Think, um, it was straight away copy than here, too. This saying for months it's extend these Let's now put the foremost for growth rate. It's a classic formal for girl threats or you do. You divide numbers from current period you your previous period and deduct one from There was a boy a percentage for a month here, and it's extend it through the whole orange. And as you can see, everything works fine to 2026 we're going to do with it. But one thing which I wanted to touch base is the weird dynamics off growth rates. So as you can see here, we have 15% 12% 13% 10% 12% 3 women. The numbers are just not consistent, and they do for any specific trend. Um, there is no real rational behind such numbers. And the reason for having this weird trend in growth rates is because, as you can see in 2022 there are no numbers from medium broker, and this immediately distorts the average number firming to such things. When I told you that I was no cherry picking reports when people are so you know what of cases people just selecting our research triples with longer horizon for with longer forecasts, just to avoid specifically these issues with broken trends. But I wasn't cherry picking it because eventually it's not a mistake, just very logical. You need waning. So be careful with these and eventually it's up to you if you want to use collect reports with the longer forecast arising. But just be prepared to on sort of the questions if you're right. Okay, let's no go back and told the eventually No one has any idea what's going to happen in 2026 . We're going to get growth rates here, and it will be precise enough. So let's put a border your own these two cells just to show the readers that there is changing off for more. And what we're going to do here is we're going just to for our growth rates were going to put her coded numbers. So let's say I know for 2026. So it's use 9% then 8% in 2027 7% in 2028 and then here in revenue. We need to change the four muscle. It will be previous period multiplied by one pause. The growth read. Then let's copy this one. Let's go to based anything the space for more so basically now, which have the numbers for on. What we need to do is effectively just totaling in the cell number five to the sold number 10. So just extend these one. And then what's copy this one? Because this is vessel Y. Number five is going to fall through the model. So that's what we need to copy these numbers in this wine number five, not the numbers in the wind number 10. So we have copied them and let's go to the income statement. Onda was Go pay special based link on this one should be in green because it's linked to another tap and we have the numbers correctly basted. Here I was. Go back, Teoh em on. Basically, that's it. We're done with driven your forecasts. For now, as mentioned before we're doing simplified for cost at the stage and the waiter in the courts were going to come back to this stop. The last thing which I wanted to discuss invisibility is how to find the numbers in broken nose, since it's not always straightforward. So let's go back to Allah. Consensus step and here for revenue, you can see that we have links you financial statements and what I suggest is just let's go one by one. And, um, trying the taken 74. So let's go to the wind number 74. As you can see, um, for this broker, we are taking growth riven you. But here, in addition to gross revenue, will have naturally on my father's desire would be accused, actually net revenue numbers. But if we go deeper bit, we'll see that in fact, the numbers we need our growth raving your numbers. These equate to find the right metric is to compare historical numbers. And you know what keys Revenue in the income statement is the same as what these particular broker calls gross revenue. This is actually very common mistake when people are taking wrong alliance in broken notes in certain cases because naming from broker to broker my differ also, sometimes you might need to make certain adjustments. So be be ready for these. The difference in for this particular broker it's caused by the fact that broken treats traffic acquisition costs as passed through revenue and makes relevant adjustment. It's the right off the broker, eventually to avoid such adjustment. But for our model and in our case it is better to take unadjusted numbers since they are comparable with other brokers as well as with the numbers we the with the numbers which represent in our model to demonstrate it. So that's actually have a look at the definition off raving off the medium broker. So let's go down two medium broker and here we have income statement. And also, as you can see, we have different periods. But I've already mentioned this one. So for medium broken, we have only forecast only 2 2021 while for awards broker, we have the numbers to 2025. But let's just go back to the raving aboard. And by the way, here with this color, I've highlighted their lines, which I'm using for making consensus. So as you can see, a medium broker also has the line called me driving you. But the definition off nurturing here is actually slightly different. In addition to taking out traffic acquisition costs, a medium broker also takes out other costs off revenue, so saving your numbers off, large broker and medium broken cannot be compared to each other because they just of different metrics, they just calculated differently. Andi. Eventually, if we g o even ah, more down to and have a look at what small broker uses. They should have only the wine called revenue. And if we compare the numbers in historic periods, we can see this is the metric which in our model called revenue on in the model off medium and wash broker through the sign is called Gross reading and exactly the wine, which we need to take in order to, um, you prepare our working census estimates. As you can see, it's not always very straightforward and requires certain efforts. Um, one more thing to you when I was transferring the numbers from consensus step. So let me just go to the top of the page. So when I was transferring these numbers 20 end up here, I've just copy pasted them as values if you put instead of values. If you put links to consensus step. So basically, if you do it this way, be very careful by not putting a link to an empty soul. So what, We just demonstrate what I mean? So if you extend this one to the whole orange, you can see that the numbers change and this happens because when you put a link to an empty cell. Eventually the value of this whole become zero on. Then it is included in average calculation. So basically, it averages the way X awards is that it produced these numbers these numbers and zero. And these basically might cause, um, a mistake. This is a very common mistake, which people make. So be careful with this one. Do not putting stem cells or modify or for more just but it's buried safer just to copy, paste the numbers as values with me. Just undo the changes. Well, that's it. With this video in the next one, we're going to discuss cost and then working t to balance sheets or comforting, and we'll come back to you can speak for us with and see you in the next video. 11. Part 2 - Costs and expenses: let's continue our journey. And in this video we're going Teoh Focus Own Coast in expenses. So let's go to income statement, and basically what we need to do is to prepare this line here. But we're not going to four cost these individual lines at this stage of the course, so instead we're going to get the total costs and expenses numbers. And in the second part of the course, we're going to individually go through each of these lines and prepare detailed forecasts for each of them. However, at this stage, as I said, in order to make the process smoother and in order to make the ports more efficient, we're going to focus on Lee on total costs and expenses and in order to calculate these costs and expenses. What we're going to do is we're going to have oak in brokered notes for income from operations and deduct this income from operations from revenues in order to get to costs and expenses. And the reason we're doing this is because I'm not. Every broker eventually reports, um, costs and expenses in the shape of and for which we need. So in order to get the right numbers. It would be more efficient if we, uh, if we extract in, confront operations numbers from broker reports and deducted from raving in order to get to Khowst and expenses numbers. So let's continue and let's go t or m tap. And first, let's create a section divider. Spoon it here on a cool operating income. Some reason for uh, it's not correct. But you I think now we're fine will have only approach number one here just because, um, just because eventually in the second part approach Number two will be to forecast numbers ourselves without broker consensus, and they're one of forecast numbers. We're going to forecast Causton expenses wine directly, and from there we're going to get operating in himself for operating income. We won't have operating income used in the model way. At this stage, we needed only the one from Burger Consensus, and then it will be the calculated wife. That's why we're not putting here a wine called operating income used in the model. We're just saying that it's only a approach number one broker consensus. So what? I suggest we can just copy this whole thing and copy and paste it and this one obviously is going to be operating income just present as values here, instead of think, would be more relevant. Easter put margin, and we will keep this one as it is now. But just delete the numbers. Um, but for these two lines, what we at the end of doing it is let's go to the income statement and put the links here. It's put the four more four margins. So this one divided by actually, we need to divide it to this one because it's a broker consensus. That's why it's body this way. So we're good here or touch base on this one with the room, and now it's go to consensus step. So what we need to do is basically copy paste these numbers, and I'm going. I'll HV raise them as values. And in 2026 what we need to put here is, let's just assume I think it's fair to assume that it's going to be 30% going forward and let's use it for all periods. The only thing we obviously need to change the formal here because currently shows US growth instead. What we need today is this one divided mortified by a margin. And then what's extended foremost with it. The shortcut is old h f. So you know, we have operating income four old periods. But before we moved to cause the calculation of Causton expenses, we just have a quick cook how we get the numbers from broker forecasts. So for the Watch broker, um, let's just go to the wine number 83. So we're taking this one. It's actually quite straightforward. They have operating income, and what they do is basically, um, they get from grocery vin unit driving and then from naturally knew they doctor. So the party thing expensive and get to operating income. So for large broke, I guess it's quite straightforward for medium worker. I think it's not. So let's go to 214. Yes. So here we go. We're effectively taking these lines, and in order to understand what what we're taking, we just have a look at historical numbers. So just remember, memorize them. So it's in 2018. 26,000,000,321 million, and I would go back to consensus. So we're taking this number. But if you have a cool rukh, they have also operating income a bit, excluding one off items. So what they're doing here is on. They are not including this European Commission fire. So, as you can see, they have to operating incomes one excluding one off items, which is in this case, you don't wanna fight him to be only European Commission point. And they have also reported one and basic joining this one loaded tank. Consistent numbers. Um, also just f y i. When people say it, they usually mean actual operating income. It's the this is these are almost synonyms, but it depends on broker. It depends on the person using this. They're not always the same. But big picture there. Synonyms, Ondo. It's gold into the small broker. I think it's also straightforward with these guys. So is 332 I'm just pressing control, um, with a square bracket. So I'm getting to the sell directly. No. And as you can see, the also have the wine called operating income. So in this case, it's also quite straightforward. So it was straightforward to get this one from a large and small broker. It was a bit more complicated to get this number from medium broker. But event of the day, if we just compare historical numbers, will be able to get the number three the way. And by the way, that the reason I was suggesting to get the numbers from operating income and not calculating costs ourselves is, for example, karaoke at small broker. They just do not show you any cause breakdown. They don't show you cost. So the only way you can understand Coast is throw operating income. So in order to you to get the correct numbers, I suggested to go through operating income. So I hope it's clear with consensus and with move forward, let's go back to yo end up. So basically what we need here is we just copied these, um, whole section and with Koide Coast and expenses. Um, I just in the more was number one broker going. And here we don't need these lines so we can just delete them because eventually, the way we calculated he's by simply, um, I'm just the pointless for the former. Maybe that's not very efficient. But anyway, the way we kill commit costs and expenses is we're taking revenue in approach number one and deduct operating income from this number. And that's how we eventually get to, um, to costs and expenses. So we just extend this one. So, girls, I think it's fine. Andi, I even think that we can basically extend this four more because we don't need any hard coded numbers here. Um, the only thing is, it's also extend this four more to the whole period. So, you know, the to cover historical periods to and this way we basically have, um we need. And, uh, I was just, um, copy these numbers and transfer them to the income statement. Let's go here and paste this everything Were you during the course? We might change for more here, because what will do well for? As I said, we're going to forecast each of these lines individually. And but for now, we just paste this with a willing to, um, to the operating model, and he will have all the numbers and basically that's eat with this video in the next one. We're going to start for costing our balance shit and see there 12. Part 2 - Current assets: we're done with forecasting revenues and costs and expenses. So now we're going to switch to balance sheet for custom. We're going to come back to income statement weight. During the course, we're going to basically finished these lines. But in order to do so, we need to finish something in the balance Shit. And we're going to switch que the cash full statement. And after that, we're going to come back to the income statement. But let's now go to the balance shit and what we're going to do now ease to four cost Current assets was held at the first lines. They are, in fact, balancing wines. Uh oh. This one won't be really balanced, you know, model. But we'll touch base on this later in the course. But just remember that cash wine is the balancing line off off your melon shit and is finished as the wa step. So, in order to in order to finish it, you need to feel in the cash flow statement. So let's leave these two wines for now. Ah, and go to the next one, which is account receivable. The country syllables is part off the thing called working capital. and working capital in most classic definition includes a country syllables, inventory and accounts payables. I Usually there are other items which also should be included into working capital, but it depends from company to company. In most cases, what people mean by by working capital is are these three items. But, as I said, you need for every company need to consider each particular wine, but it should be included in to work Captain note, for example, For alphabet. I would include otherwise, but I just got situated in the course. So for now it's focus on on the country syllables. So basically country seal both in its essence, show you how much cash your doctors all use. For example, you recognize certain portion off revenue, but you haven't received cash for it, and that is exactly country syllables. Imagine that you sell I, you know, liking alphabet. They so would say that they're pixel devices on, but they haven't increases cash for it. And the the buyer off that picture devices there was a wholesaler and then that wholesalers has, for example, 30 days to pay the invoice and within that 30 days off, obviously in most cases, people wait till the was they to pay their invoices. So Google recognizes revenue on bond, but it puts craving in que its income statement, but they have not received it. 106 cash it. That's why they have also put the amount they hasn't received yet into this wine called a country syllables. I just made up the numbers unknown that I just made up. The situation may be, I know the they might different m everything. Your recognition policy. There might be nuances, but in general, just for you to understand this part off the Reagan, you have already recognized what you haven't but for which you have not received cash on. In certain cases, it might be a very sizable amount. And actually in the keys off alphabet, it is a quite say sizeable amount. You can see that it's a 20 billion to almost 21 billion in 2018. We should quite a lot of money. So, um, this is one of the more one of the most important items in for almost for every company. But it also depends on the big reasons model. For some companies, the country syllables might be zero or cause to zero. It's Ah, it's very company specific thing, but it's an item which you need to pay a lot of attention. So now, Um, what I suggest to do is to go to ROM tap and basically prepare for costs for a country syllables. So let's just copy this line for our new section, which will be called Working Capital. It's put it here. Oh, it's cool. Morgan Capital and, um, the next line of its Koide accounts receivables already suitable. But then we will correct. Well, correct names. Waiter on our clear type is waiting in the course. Once we finalize. How do I'm sure that there will be a lot of tables in my books, but I'll correct them as I spoke them. But at the end of the course, I'll I'll go through the file and correct some of the type of. So do more able, this one. So it's called a Are It's making boat and where I suggested it easy. Let's go to raving you. It's basically a copy. This whole walk, Um, and what's basted here? I just did in order to save time for four morning so it could a are used in the model. Ah, it will be a are yes. So it cooks. Okay, Actress sported one. You stay here. Well, this one shouldn't be Revenue should, because expenses. I just forgot to baby's name. And, um, let's now go to link the numbers for historical periods. So going to go this way and here instead of growth or I suggest is we just put as percentage off. It's just calculated and we need to leave because its approach one would think it to broker Consumes whose? Well, it's former painter so And let's extend this one. Do the whole range. Yes, it's great circle references, but let's just delete these numbers. Well, it's going to change anyway. So for these numbers, we're going to take a broker, um, broker forecasts for now and waiting the course. We're going to forecast a country syllables through through the thing called the sales outstanding. It's very much the same thing as percentage approving about will touch basing this later. So for now it's go to our working since a step, and we have here a country syllables. We're going to discuss the just in a minute, but for now, it's just copy and paste the numbers. So we have them seer. And, um, I think just good. Sorry. I just put this one here. And, um, it was just for custom off towards, um well for custom as a percentage of revenue, but the number will take or be 15%. Well, justice, human that it's 15% for a period. I just pasted the numbers as values. And now let's go this way. And when you can multiply too raving you for brokers just paste the foremost HP. If so, we have the forecasts. We have the were broker numbers to 2023 then starting from 2024 which is assumed this to be as a we just calculated as a 15% or for our revenue. And, um, what we can do now is call police one, go to the balance sheet and paste it as a link. And obviously they should be in green, and I would go back. And what I suggest is which is discussed the numbers from broken reports because they are we have fissile have different periods now. Also, we don't have numbers from small broker. Let's go to consensus step and let's have oak. So why no number 94 for hours? Broker? And, as you can see here, the wars broker for balance sheet items, they have four costs only 2 2023 That is why we have four costs for a country syllables, um, here only to 2023. And that's way afterwards. We need to use our own assumptions. So, um, but it's actually quite straightforward. They have the line called called the Country Syllables, and we just take the numbers from here. It's moral is the same thing with actually medium broker. So in the balance ship, they have a only 2 2021 But they have their own code. The country syllables the original problem by taking it. And what if you have oak at historical periods? You can see that you know the numbers match. So it's It's quite straightforward exercise, so in the same here. Actually, if you see the numbers in historical periods, they match, so it's a straightforward thing. But if we go to the small broker, we can see that the way they present their balance. It is a bit weird, so they have a very short version off the balance ship and from these bound ship baby physically cannot get the numbers for a country syllables. That's why we're not taking it. That's why, um, here we have no numbers for for for the small broker. And by the way, the numbers for won't basically well when we are going to model other categories in a lot of cases won't use numbers from small cracker just because they just have very sure. Also, they have actually very weird bullshit. For example, Just have a look here and here. The numbers do not match for assets and liabilities. It shouldn't be this way. Maybe the this is not material mistake. Maybe, but it also like in Ste. But anyway, it's It is in the state of the total assets and total liability should match. And you see that the match in historical period. They might be different definition, but that's how they present the numbers in their report. That's what I meant when I said that I had not modified the numbers, but anyway, that's how they present it. I cannot say anything more why this number don't match without we can't their model, but anyway, it's not important for us, and they also don't have any numbers. But just bear in mind that such things happen in in very censorship boards. But for now, we're not taking anything for small broker. And, um, I guess that's eat with the account receivables, and I would go to the balance ship and four cost the next line, and actually over the next one, is income tax receivable. Um, to be fair, Texas usually or note model in in financial models just because you have very little visibility on how to model them. You had very little information which doesnt have hole you more going Texas. And it's a very common in financial models, no to Model X. It just seemed that at certain level there are various options. You can assume that as an average off previous period, you can see them equal to the last period. We just can't assume they left zero or people just for different, different ways. Even for, she says, taxes are not really model, because in order to model them correctly, you need to have access to companies information and, um, that's producing not the case. And even if you have access to company information. It's It's usually very complex modelling because it involves a water things a lot of things have to be taking into account, and especially for such complex company as alphabet, which has operations in a world of different countries. And anyway, this is a topic for advanced modelling, and I can see that. Sure, I can even demonstrate it if we go to, um to broker notes. Let's just go to, um, broker by broker. So if we go to the balance sheet off the large broker, you can see in context receivables, you see, like this more. I think this is exactly ah line yet you see, like the historical numbers match and here they have foot number for opiates. And I can tell you that this is the number off in this category, as in the balance ship as off you want because this report has been issued in May 2019. And that's why they have updated the numbers with you want release. But they have not, um they just human get foot going forward. So, as you see, they do not model this one on. If we have a look at ah, the other broker and this one, you see, they just assume it at 20 who have also they're not making any changes. But anyway, this line is not material. So what I suggest to do is let's just assume, you know, model that, uh, this is equal to 2018 level. But what? I think we should do it. Just create a new tab. What's called taxes and, as always, we need to for consistency. We need Teoh based this line. And then I was going to be a special A school with on and then let's go to view, um, for his pains. And let's take o degree the lines Onda we need now to create in you subsection. Here we go. There will be other Texas, so it just could be pasted. Heroes go to the balance ship. It's called previous one and face it as values and I wont be rolled the same. So it was for historical periods. But just know a link the numbers to the Valen ship I show you what we can do is that's a boy called gusto. Without decimals, this to number, they should be in green. And here Oh, it's Putin important there. It's not necessary. But just to show you that it's not regular, um, it's not regular for more. I just copy and paste the formal with each year. We just You just put a lien previous periods in all years, so we're not effectively. We're not forecasting anything here. What we're doing is we're just assuming that taxes were singing the decks at the same level as in 2018. So no, not that that taxes it so. But their wine called in contacts receivables. It's put an old, um, qualifying this one. Just say no model is human. 2018 Well, and war is also suggests was putting Italian. It's make it blue. This will be the four month for for our good commence for our notes in the and then I would go to the next item in the township, which is invented. We've just invented off working capital. Aziz. You can see it's not very material. I'm for Google. However, it's still over a 1,000,000,000 in 2018 and that's because eventually a lot off cos even the one which have mostly, which moved to provide services they still have serving inventory levels in a lot of cases . They just hit him within other current assets. But go stays them explicitly here. And, um no, just model them as part with the working capital. Having said these, I just noticed that we forgot to transfer. Basically, copy Paste. The numbers could face Link of four. Texas, which is now based drink green. So, yeah. Now, let's go back to inventory and let's go to the operating model. Just copy this one. And this one actually is not going to be as a percentage of revenue is going to be as a percentage off course and think we're thing most that'll yes, I think we're good. Oh, I just noticed a mistake which ive you see off length the balance sheet to, um to the numbers in approach number one, while I should have link them here. So Okay. Can we just call it this one? It's very easy to do. Mistake. You always have to double check. And whenever you go through your model, just it's a good habit. Que double check the numbers as ego through your Muggle. So we just go back. No, I think we have correct links, and we have correct ings here, too. This won't obviously should be as a person. Did you off Kristen expenses And because we're using it in approach number one should be doing to Causton expense in their perros number 12 Just extend the four more. And, um, we need to put other numbers here. So what's as the as the case with as it caused the case with the country symbols? Let's go to concerns to step on. Basically, we need take the numbers from here. As you can see, we just have only one broker. So, um, just beast as values and then human it to reach change. Actually, the formal it's actually wine 37. Cool. Just hold. It seems to be correct. Each we have and obviously not 15%. But rather I don't with use 0.5%. And that's eat with inventing. We just quickly go to our consensus step, and it's very straightforward with them. Invent area level for watch broker so they have it in their balance shit, and we just take the number from here. As you know, small rocker doesn't provide that details. And if I'm not mistaken, your theories you can find anything. He waited two elementary in the report off the medium worker. So that's why we just have Onley one broker forecasting inventory and we're using on their numbers here. So would you go to RM top? Let's go, Pete, this one to the balance sheet and based it as we should be in green and we're fine with inventor. You know what's going on to the last item off current acids, and this one is called other current assets. The thing is, we have very little visibility on what is inside this wine and war, how this one is sleep. That is why we have to do certain assumption. And we just assumed that these wine is as more always more was correlated with the size of the company on. In such, Keith's people usually usually model these wines as a percentage of revenue. There are actually different. You can assume, as a percentage off your total assets, creating so recordings you can you can use various methods. You consume it as a percentage of some other items, but anyway, it's a common practice to assume it as a percentage off revenue, and that's what we're going to be here, But what can you just demonstrated? So it's Gil back to Om Tep. So it's copy off a section divider. Um, you say some typing. Let's go through this one under current asset. So it's also body here, and, um, think we can just go pay as a percentage you're framing here. So no, it's That's good historical numbers. This should be common style. No decimals in green on as a percentage off raving. You're just calculated. I think we can. You would have to put it by the way, to the line number five. Note to the one number 10 because that wine doesn't depend on the approaches. So just Fikse on birth life. Let's put percentages. I think going forward, just copy this former on your toe. What do you want? Number five, which is Oh, or even you and, um, think we can basically here and extended and then wore the percentages. I think 3% is a fair assumption. Just put it as values and see forward is calculated correctly. I think everything is fine, but just no, um, basted as the link. It should be in green, and basically that's eat with current assets I hope that everything is clear so far. If not, feel free to ask questions on DA See you in the next video where we're going to, uh, Tribune four. Cause for non current assets. 13. Part 2 - Non-current assets: know what Switch to non current assets. We'll start with the first line on marketable investments. Well, think into financial reports off the company to see what is inside this line. So it's open period off 10-K and here on the page 56 off the report, we can find the definition. Basically, it'll marketable. Investments are the investments in the companies in which off doesn't have control. What have a significant influence? Such companies are usually called associates. Strictly speaking, what I'm going to say now is no corrected. Technically, no, it doesn't work this week, but the rule of thumb is that if you have from 20 to 50% in the company, it's kind off your associate. I mean, there are a lot of complication, more troll, but with the U do you have, but just the however understanding it's from 20 to 50%. It's associative ball, 50% you control the There are a lot of exceptions, but however it works this way, So under these line in no market for investments, Google basically says, What's the value off their investments in their associates unless you have good visibility on what is inside this line, you do not model it. For some companies at some stages off their development, it might be a very significant amount just to give you an example. Yahoo had the stake in Alibaba, which was worth more than all corporations off. Yeah, also, another example is nah, Sports had a stake in Vincent and that the value of that state was greater than the value off or other operations you know, case significant amount and don't get me wrong. 14 billion in 2018 is a lot of money. It's more than what about the companies are worth. But given the sheer size of Google, it's not a game changer. Andi, with the information we have, we really cannot model it a precise. So we're going to assume for the forecast period that is going to stay at the same amount off around 14 billion. And actually, if you have a look at what brokers do for their reports, I will see that there more was supporting the same logic. So what's going to consensus? And here for large broker? Let's go and try to find the wine in, um in their balance shit. And as you can see they have Visa this match with our historic ALS, and after that they have a change in 2019. But going forward, you see there are no changes. And as I mentioned in one of the previous videos, the world Broker, they just updated the result with their 2090 numbers with the Cuban results. So it just opened. Um, you want report and you'll see these so no marketable investments. So as you can see as off Q one, this is exactly that amount. And let's have a look wort meeting worker does. We won't see anything in school broker as you remember. They just do. You know that the schools details, but that's why I would just have for medium broker just find the wine. So it's not in constraint mint. It should be here. As you can see, they also do not know it just because you don't have good visibility of what is inside the soil. Information on this and that's why you just assume that is going to stay more of us at the same level. And basically that's what we're going to do in our model to So what's create in UTEP, our Cooley stub six for other calculations. And as always, when we creating you tap for consistency with Skopje pieced going number one. So we've tasted the wine with control v. He then that's based the with off the corns. And, um, also traditionally was go to view for his pains and think about the greed ones. Um, then the next thing we need to do is let's copy section dividers so it could be just this one. Let's go to the balance ship. Copy this and on each Reavy woodholme wind for me for this self A teach TV they're not. Go here and do the same thing. Um, then it's avoid the right four months common style without decimals. And what's bookings to the bone Shit on this, you obviously should be in green and a hero. It's put border and what's put a link to this one, and I Hve if for pasting four Morse and what's at an old just say no looked modelled, I assumed at 2018 level Um, if you model these Warren Sorry, please consider other financial statements, and in brackets were just clarify what we mean. For example, bashful state make it italic can apply blue car. So on day there was thing we need to do is basically cookie, this one to our balance. Shit. And we can use this one. Hes links employ green, former, and we're done with no marketable investments. So what's moved to the next line, which is deferred income Texas. Um, as with previous off taxes, we touch these with in contact receivables because we just don't have enough information to model these properly at this stage. What we're going to do is basically assume that this coin doesn't change, so we'll just fix it at 2018 level. I just have a What do brokers do with this? Fine. Just quickly. Um, let's have a look for medium broker. So marching. You see, I think they've the really don't, um do you know, model this one? So they just don't have any details for it. And I was having for awards broker. Yeah, I think. Oh, no, it's liabilities. Um, before income. Texas on. And I think this is a line, so yep, it matches in historical periods. And here I bet that they're taking Q one numbers, So yep. As you see there taking Q one numbers, So they're not modeling this going to. So let's go back. Ah, here. And actually, no, you should go. Not here but to the Texas step. I think we can just copy paste this thing. You. The only thing go have to change is deferred income taxes, and it's actually in 17. Let's remember these. You put it here, then we put it here. No, they think is correct. And then we hear just getting to the line number selling team. I'm sorry, and we'll just copy this one and put it back to the brown shit So you can do devise either this way or if or if you're in Excel 2016 or just by pressing this one and put it in green . That's how we model this one. Now let's move to the next line property and equipment. And that's actually where the fun starts, which is first, try to understand what is inside this one. As you can see, it's a very significant amount, and inside this line we have the value off the equipment on the balance sheet off the company and the value of the properties in the balance sheet of the company. Forget, for example, I think Google has a lot of value in there serious in their data centers. And that is what they would they do when they build that data centers. They put a lot of tangible assets on their balance sheet, and that is what you can see here. Please note, by the way, that there are also all down shit items, but we're not going to cover them. I don't think that actually Google has, ah, significant off balance sheet items, but just always buring mind that for some companies it might be a significant amount and have a look, um, for the wine in the annual report. But anyway, this is more a topic for another core. So we're not going to cover here and let's just move forward and what we do now he's will tried you model these line and what? Just go to the operating model and with that, um, a section divider. So I put it here. Um, I just could be, and based on the name. So basically, how these things are modelled is a very common thing. So what you do, what you have is you have the assets And as of the beginning of period, then you have something which increases this assets. You have something which decreases this assets, and then you have end of beautiful balance. So basically, your, um, end of period balance is what is shown in the brown shit. So just link these numbers there. What's applied? The right former Onda you're beginning. Period is basically equal. Do the end off the worst period so we can extend the four more. And when we speak about property and equipment, the thing which increases the balance value off the property and equipment is skeptics and the thing which decrease the valley off these assets is depreciation. And just if way, I, um people say depreciation amortization, the depreciation is for tangible assets, and organization is for intangible essence. We'll touch base on this one just in a couple of minutes. So before you just put it for more here, So basically, your end of period is equal to beginning of period. For skeptics minus depreciation. What's extend the four more? Let me just now, spend a couple of minutes on explaining how in general in principle brought with this forecast for profit and equipment works. I'll just have the theoretical example just for some of you who will aware how it it works . Just feel free to skip the next couple of minutes, but for the rest, let me just quickly go through the numbers. So imagine that we're buying a server. Let's see Sarah buying for I know, let's say for 10,000 dollars, then what happens next is, uh let's say that we're doing it in 2019 so we're buying it. That's why we have the cash out for off $10,000 just quite the form of the game. And obviously, as of the beginning off period, we had in this line for these political service the value of zero because we didn't have anything. Then we spend $10,000 let's just assume that we we bought this servers on the last the off the period on 31st off December 2019. So we had the cash old fool in amount off $10,000. Then depreciation is going to be zero because, as I said, we bought it in the end of the event off the period, and that's why we haven't appreciated anything yet on then the end of the period. Value will be basically beginning of period. Post KPIX on. Let's put a four more for beginning off period, and the beginning of period will be equal to the end off the worst period. And we can extend the former for until the period, too. Uh, no. What happens next is that these amount goes to our casual statement 10,000. Then this amount for the whole period goes to our balance ship, the property and equipment A while. Um, but then, for and for all periods off there, we have acquired it because we're not having any any cash out force. We already paid for the service. That's why we can put zeros here. Catholics. We just in cured the Catholics only during the first period. Then what happened next is that but say that we're using these. Their useful life off the server is five years. It means that we are going to depreciate in order to calculate the annual depreciation. We need to divide this 10,000 by five, and it's going to be 2000. Um, and basically, with here and these 2000 we're going to depreciate this amount in the next five years and after that, the value. We're looking to appreciate anything because the value of the asset will be zero. And we just assume that there is no salvage value for these servers. Teacher It's a very simplified example. So it means that in the next five years we're going to charge 2000 in order to show this expense, you know, income statement. So just let's go quickly over this again. So what happens in the England statement in the period? There are no changes in the casual statement theories for $10,000 in the income statement, as they said, there was no depreciation because we seem that the purchase happens in the end of the event of the period and in the balance ship, their value of the property and equipment wine increases by 10,000. Then in the period Number two, we don't have any changes in the The effect on the control student is zero. Then what we do is that we show the charge over the useful life of the told the income state 2000 dollars in expenses line in the income statement, and these 2000 decreases out the value off off the abrupt and equipment. And it means that the value or for the server on our balance sheet decreases by 2000. And now it's 8000 and it happens in the next five years. And by the end off the period Number six we have there said, Fully depreciated, the value on the township goes to zero, and we have all lost, um, depreciation charge in the income statement off 2000. I hope it's clear with this theoretical example. It's a very simplified example. We we just assume that it's we don't have any salvage value. It's a straight line depreciation and so on, so on. But I hope that it's clear. So let's just go back to Google and what's now for that forecast for Catholics and appreciation? Its first copy this section divider and put a hero. It's called, um, kpix On what I said, just it's copied the format we used in, um, you're waving your line. Please go. This year, we don't need this line. And, um, let's go, b. Many skeptics. I was put here and kept pigs. We think we can call people s percentage off raving you for historical periods. I just put your mind is because be very careful with science when you take the numbers from the casual statement, as mentioned before, we are modeling everything with positive signs and we make we just for the watchdog in our foremost. But when we model cash for state and when we have the numbers in the casual statement, there shone with the ethic they have on cash balance. That's why. Be careful with this one. So when we take the numbers for KPIX because they are decreasing cash levels, they are cash outfalls there showed negative here, and when we put them two other parts off the model, we have to put them on the minus side. And here KPIX is basically the same thing as purchases off property and equipment. They might be different names, something it's called brother purchases off one property and equipment. Sometimes it's called capital expenditures, but here it is called purchases rather than equipment, and we're taking this one. So just this one and we can cook the four more one bullet here on when we say as person to shoot believing you, we divided too, the one from brokers and I think we can extend this range. Obviously, it's a circle reference, but she deleted for now. Um Then what we need to do is with delete this one, and words go to consensus step and we have healed gap X tangible acid. I'll just copy and paste it and we'll come back to this one just in a second to have hope Brokers do this. And so we have the numbers calculated here correctly and for, um, basically for the purists after 2026 Um, we're just put it this way. It's pretty border here. And what? Just assume that it's 10% going forward. Actually, 10% is very common assumption for KPIX. Ah, in a lot of interesting I don't know why, but you know what of interested? 10% is kind of a magic number for Catholics. Assumption in the water from here. So basically, my number 10 multiplied by my number 102. It's extended for more, So we have no KPIX numbers and what we need to do next is copy them as links here. And no, we just have a little called brokers. Um, get for past this cap it's how we go in their statements. So we just go, 01 by one. What's first go to watch broker. It's why Number 161. So it is in in the casual statement off the So it's a large a broker official statement, investing activities. It's a purchases or property and equipment, so the numbers in historical periods match Just double check. They do. So we're taking this align and then let's go to medium broker. So you're Riggio cash from investments. And the way they reported is that they have core cap. It's they have other bed skeptics and think they have reconciling item. And some of these three items goes here. And this is the number which is the same as, you know, historical period. And basically, we're taking this for three numbers in the periods 2019 2020 and 2021 and on, and you see that they call it instead of purchases off property and equipmen