Build Excel Models using Advanced Formulas and Functions, VBA, Power Query | Prashant Panchal | Skillshare

Playback Speed

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

Build Excel Models using Advanced Formulas and Functions, VBA, Power Query

teacher avatar Prashant Panchal, Excel Expert and Data Transformation

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

69 Lessons (5h 53m)
    • 1. 1. Introduction to the Course Build Advanced Excel Models

    • 2. 2. Minimum Requirements for the Course

    • 3. 3. Prepayments In a Business - An Introduction

    • 4. 4. Prepaid Expenses (Prepayments) Accounting Definition

    • 5. 5. Prepaid Expense Example (How Accounting works for Prepayments)

    • 6. 6. Advantages and Disadvantages of Prepaid Expenses

    • 7. 7. An Intro to PRO Excel Models and Formulas

    • 8. 8. Basics of Date Function

    • 9. 9. Basics of EOMONTH Function

    • 10. 10. Basics of DATEVALUE function

    • 11. 11. Basics of IF Function

    • 12. 12. Basics of IFS Function (Excel 2019 and Office 365 Only)

    • 13. 13. Basics of VLOOKUP Function

    • 14. 14. Basics of MATCH Function

    • 15. 15. Basics of INDIRECT Function

    • 16. 16. Basics of NAMED Ranges (Name Manager)

    • 17. 17. Advanced Version of VLOOKUP Function (All other formulas combined)

    • 18. 18. Introduction to Model and Control Panel Tab (Important Sheet Tab)

    • 19. 19. Formula Based Prepaid Expenses Model Deep Dive (Part 1)

    • 20. 20. Formula Based Prepaid Expenses Model Deep Dive (Part 2)

    • 21. 21. Formula Based Prepaid Expenses Model Deep Dive (Part 3)

    • 22. 22. IFS Function Month End date Prepayment calculation

    • 23. 23. Prepaid Expenses Closing Balance Summary Tab (Formula Based Summary)

    • 24. 24. Protecting Formulas Cells and Fields in the Model

    • 25. 25. Exact Date Prepaid Amorisation calculation Intro

    • 26. 26. Formulas update for Exact Prepaid Exps Calculation

    • 27. 27. Formulas Update for Exact Date Amortisation 1

    • 28. 28. Formulas Update for Exact Date Amortisation 2

    • 29. 29. Formulas Update for Exact Date Amortisation 3

    • 30. 30. Formulas Update for Exact Date Amortisation 4

    • 31. 31. IFS Function Exact Date Prepayments Amortisation

    • 32. 32. Data Validation Controls

    • 33. 33. Bonus Prepayment Model Opening Balances1

    • 34. 34. Bonus Prepayment Model Opening Balances2

    • 35. 35. Power Query and Pivot Table Prepayment Summary Table Intro

    • 36. 36. Power Query and Pivot Table Summary Deep Dive (Part 1)

    • 37. 37. Power Query and Pivot Table Summary Deep Dive (Part 2)

    • 38. 38. Power Query and Pivot Table Summary Deep Dive (Part 3)

    • 39. 39. Power Query and Pivot Table Summary Deep Dive (Part 4)

    • 40. 40. Using Array Formulas to Add Formula Protection

    • 41. 41. Bonus Allocation Intro 1

    • 42. 42. Bonus Allocation Walk 2

    • 43. 43. New Version Power Query and PT Model

    • 44. 44. Complete Walkthrough Advanced VBA Prepaid Expenses Amortisation Model

    • 45. 45. Bonus New Version Excel VBA Model

    • 46. 46. Dynamic Dashboard Overview

    • 47. 47. Importing Profit and Loss Statements

    • 48. 48. Creating Dynamic Data Validation

    • 49. 49. Creating Named Ranges for Dynamic Table

    • 50. 50. Dynamic Date Column Headings

    • 51. 51. Dynamic Month and YTD Dashboard tables headings (PRO TIP)

    • 52. 52. Dynamic VLOOKUP Formula First section of the Dashboard

    • 53. 53. Creating Rolling Dashboard with Dynamic VLOOKUP Function

    • 54. 54. IMPORTANT Error Checking (PRO TIP)

    • 55. 55. Data Prep for AREA Charts (#NA Function)

    • 56. 56. Visualization AREA Charts for Month

    • 57. 57. Visualization DONUT Charts Revenue, GP, NP 1

    • 58. 58. Visualization DONUT Charts Revenue, GP, NP 2

    • 59. 59. Introduction Formulaless Dashboard

    • 60. 60. Understanding the data files

    • 61. 61. Consolidation with Power Query

    • 62. 62. Dynamic File Path Trick in Power Query

    • 63. 63. Conditional Cumulative totals

    • 64. 64. Conditional Cumulative totals with M Code

    • 65. 65. Dashboard Creation Pivot Table

    • 66. 66. Dashboard Creation Donuts Charts

    • 67. 67. Dashboard Creation Line Charts

    • 68. 68. Update Dashboard with New Data

    • 69. 69. Thank you for going through this Journey

  • --
  • 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.





About This Class

If you’re an accountant, analyst or business professional looking to take your Excel skills to the next level, this is the course you’ve been looking for!

This course will teach you how to prepare efficient Excel model with advanced (powerful) formulas and function, Power Query /Pivot Tables and Excel VBA (all templates are available for download).

In addition to that, I’ll cover how to create fully dynamic Excel dashboards with just Excel Formulas or if you want to get more advanced, we’ll cover next level Dashboard with Power Query and Pivot tables (no formulas used, refresh the dashboard with new data with just two clicks)

Here’s a sneak peek into some of the things we’ll cover:

  1. I’ll teach you advanced formulas and functions, and how to work with mega formulas

  2. Using Power Query (Get and Transform) to automate multiple sheets into an insightful summary report (Pivot Tables Report) using Excel VBA to control input and reduce errors, automate entire workflow saving precious time.

  3. In the course, I go through specific examples of Prepaid Expenditures like this one below:

       Mobile Phone prepaid vouchers/data plans and Insurances.

While this course will be utmost helpful to Accounting professionals, FP&A Professionals, Auditors, business professionals, anyone can enrol in the course

Accounting for prepaid expenses is easier than you think if you have the right tools at your disposal. But to do it… you need to accurately maintain the record of all prepaid expenses on your computer for an accurate profit or loss each period end (monthly/quarterly and yearly)

And Therefore it is quite crucial to have a Robust Prepaid Expenses Schedule (for an Accountant/Analyst/Auditor) and  Microsoft Excel is a great tool for this task

As we go through the example, I’ll teach you hands-on techniques to maintain a robust prepaid expenses schedule that will help you accurately determine:

  • The portion of prepaid expenses to be charged to the income statement/profit and loss statement each month

  • Prepaid expenditure balances for monthly balance sheet reviews

  • Forecast or budget prepaid expenses for future periods

  • Impact on cashflow of business due to prepaid expenses spending (high or low)

  • How to allocate prepaid expenditures each month to various divisions or cost centres accurately (using Power Query and Pivot tables) with just a few clicks (fully automated approach)

  • How to make sure that prepaid expenditure GL (balance sheet) is accurately maintained (cross-checking integrity of GL entries to detect and prevent errors and frauds)

And I teach you all of this using Microsoft Excel!

Not only will I give you the models I’ve built, but also help you improve your ability to use the various functions of Excel:

  • IF Function (nested IF)

  • Date Functions

  • Lookup and Match Function

  • Named Ranges

  • Data validation

  • Array Formulas

  • And much more

Once you have a solid grasp of those, I’ll show you how to combine the above functions to create powerful formulas!

It doesn’t stop there…

We’ll use Power Query (Get and Transform) and Pivot Tables as well!

And we’ll even cover some Excel VBA.

At end of the course, you will have a clear and practical understanding of how prepaid expenses accounting works and how you can use Microsoft Excel efficiently to calculate accurate prepaid expenses charge, prepaid expenses balance and forecast prepaid expenses for future periods.

Your co-workers and your boss will be very impressed when you show up with these new skills! For the price, I have it at… it’s worth every penny!

Don’t wait to improve your understanding of prepaid expenses and become an Excel guru! Enrol today to get started!

Who this course is for:

  • Anyone who wants to learn Advanced modelling techniques and Tricks in Microsoft Excel
  • Accountants who want to efficiently manage their Prepaid Expenses and Balance Sheet Reconciliations
  • Non-Accounting professional/Entrepreneurs who want to estimate the impact of Prepaid Expenses on their investment and Profit and Loss over a fixed term
  • Analysts /Auditors who want to audit effectively for Prepaid Expenses
  • Business Budgets and Forecast Activities where prepaid expenses are a crucial part of Balance Sheet and Income Statements

Meet Your Teacher

Teacher Profile Image

Prashant Panchal

Excel Expert and Data Transformation


Hey, I am Prashant Panchal. I like helping fellow Accountants, Auditors and Analysts to become PRO at using Microsoft Excel.


I have been using Excel for mainly Accounting and Analytics for more than 13 years and I quite enthusiastic about sharing my knowledge with fellow Accountants, Analysts, or any Excel user


I have come across many challenging reporting and analytics challenges during my career, and I was able to resolve the same using good old Microsoft Excel Spreadsheet application.


With me, you will find Basic to Advanced Excel formulas and Functions, Tips and Tricks, Excel VBA ready to use codes.


Th... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
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.


1. 1. Introduction to the Course Build Advanced Excel Models: Hello and welcome toward once except techniques with everyone's formulas and functions. Spark wary people tables and axel baby. Let's see what we learn in the course. We'll start with basics or prepare expenses. I will explain you what other people expenses in general Example of paper expenses are mobile phone or Internet people insurance or house or property, which is people health insurance of vehicle insurances, which are always big. I will explain you accounting for people. Expenses in detail with practical examples, will also explain you major at one days in the seventies, off people expenses. But if you're already familiar with this topic, that is paper expenses. Please give this section and jumped the next one because this course is not only about people excesses about it wants axel techniques, power query, people table and data modelling. 10 Will motor basics or formulas and functions like date functions if functions dysfunction , which is new to offer state 6 to 5 way look or function match function in direct function, name ranges and reforming US dysfunction are quite powerful and quite useful in creating dynamic morals and accent. I will give you a detailed demonstration of each of this function with religion examples before we use them in Model Alexei's, we will be doing a step by step exercise off preparing, please. Three axle models for people Expenses Amortization formula based Excel Model, where only formulas are used in creating model and summarized data in fully dynamic manner . In the second, original model will be using power, credit and people tables to create a dynamic somebody off all paper expensive sheets. A powerful we operate our transformation and the tourmalet would be absolutely immortal, which would be dynamic and expandable in user friendly way. It last. We'll go through some exciting exercises, like creating dashboard. We'll go through Bash Corporation exercise where we will be creating a to kind of random attachments, one with using excel formulas and function that we have learned in the course. The second dashboard with help off at once, tools like power Kredi, a k get transformed and see what it was in this park ready mades, dashboard exercise. I will show you how easy and efficient it is to transform. Have pain. Similarly structured it are we just flew kicks. It's more for the revolution. It end of the course will be ableto understand and apply it once formulas and function richer. Learning the scores excell Marilyn techniques you learnt and applied the same other Mourners like amortization or even though no mortgage amortization models manager More reticent should deal with Excel for any business accurate forecasting off more taxes and expenditure that is, people expenses and other similar business expenditure apply. Park radiant people table techniques taught amid reports that will change your life forever if I must admit, Create a power visitation with full leader Mimic dashboard using formula, Spark ready and pure Tibbles World Idea students for this course, any accelerated past permission to exist. Who wants to learn and apply at once? Techniques thrown in the course cauldrons. Who wants to efficiently manage their people expenses, other mortgages and should use and melon Cedric cancellations. Not accounting professional entrepreneurs who wants to estimate impact of people, actors on their investment and profit and loss, or a fixed up analysts and auditors who wants toward it efficiently. Opie Protectiveness is any democratization. I am prashant child. How many years of experience in finance and accounting already business operation and what's that recounted by qualification I love Excellent is traditional cruise like formulas and functions and once charting in this allegation, which I have been using since many years. At my work, however, I'm excited with Microsoft's and excess new Ranger power tools like Power Credit, which is the best data cleaning Intar Commission tool for Excel users. It are modeling with power. People are awesome for creating at once data models with large data sets that tradition excel shit cannot handle. I've been using except Libya to automate tasks, formulas and an interactivity to excel applications. So what are you waiting for, Dr. Three people videos, and I will sue inside the course. Thank you very much. 2. 2. Minimum Requirements for the Course: minimum requirement before taking the course basic to human knowledge. Off accounting in finance You should have basic knowledge of rubble and accounting system that is, you're familiar with terms like debit, credit expenditure, income, profit and loss and balance shit Basic to anyone your knowledge of Microsoft Excel. I'll be preparing a predicament and partisanship deal from the scratch. I'll be providing you complete template as well as blank template for your practice and fall along with me in discourse. It is available in the resources section. I'll be mainly using date functions. Nash Eric Function Look off lunch, especially. We look up and match indirect functions data evaluation for the temple preparation. I will also be using different formatting techniques as well, which I will share with you. Lastly, I will use Excel baby and Park ready to your professional filled drop repairman template. But this is completely optional. If you choose not to use Revere, Template will work just fine and will serve this purpose 3. 3. Prepayments In a Business - An Introduction: prepayment as well. So that is something your pain unit ones. Let's look at some examples when you pay your property. Leasing at once usually start off the month. You will still have to live on spend time in the property, apartment or house, and I really services there to pay them for their services. In that one's example to when you take a car insurance or health insurance, you must pay a premium for the pier off for unusually urinate ones. The insurer or insurance company will take care of your car and help during the same period , and you won't be paying them each month since you already painting or the ones at the start of the policy time. Well, while front problem, it is a very common example. When you buy a mobile phone watcher for a certain Peter or amount, you'll be using this balance for a minutes or money your board for after your paid the minute ones. Unlike postpaid plans in mobile, when you will be pliable to pay after a certain period of time and after sudden surges or consume one of the most important German finance and accounting, this term is quite important finance accounting work on account on analyst deals with this term Daily ist part of his routine exercise, accounting and bookkeeping, also for financial analysis, will be exploring finance and accounting expects or pre payment in later section of discourse. 4. 4. Prepaid Expenses (Prepayments) Accounting Definition: Let's somethinto counting terminologies for prepayments, people even expenses. A current asset in balance, she I will give you a brief definitional for this current assets. Current assets Our balance sheet accounts started presents value of four letters that can be reasonably expected to be converted into cash within a year. Current assets include cash and cash equivalents. Account. Sensible, involuntary marketable securities repair expenses, another liquidated that can be readily converted to cash. Now let's come back to pre payment. Ah, people expenses type off, I said. That arises on a balance sheet. It is a sult off business making people were not goods and services that will be recieved in near future, while people expenses are initially recorded. As I said, their values expense for a time as the benefit is received onto the income statement because, unlike conventional expenses, the business will receive something of value in the near future. You do the nature absurd in goods and services that must be paper expenses. For example, insurance is a paper expects, because the purpose of purchasing insurance to buy a proactive protection in case something unfortunate happens. Clearly, no insurance company would sell insurance that covers occurrence open, unfortunately, went after the fact. So insurance expense must be people benefits this you later companies make pre payment for goods or services such as equipment or insurance cover is that Freud continued benefits all the time. Good. So services for thes nature cannot be expensed immediately or entirely as a course to revenue on income straight, winning one single accounting period and must be recorded that says, in state on the balance sheet at the time of their partisan. Before being used Rock in June to record three women, your toad averaged equipment or insurance policy account and carried cash account. Assuming there no carrot is used this effectively. Kids is the amount of people expenses in the balance sheet as an asset account and reduces the cash balance in the balance sheet. Matching principal off accounting. The match in principle of account requires expressed to be matching their renews. The time expense makes six contribution to its revenue. In other words, expense are recognized not necessarily when the related payments are made, but when the benefits from the expense can be tied to the expected revenues. Let's take a different example. A piece off equipment appreciates after being deployed in use period appreciation. Costas recognizes expense for each accounting period when the user off equipment is actually contributing toe each periods. Revenue generation, the approach to matching expenses, letting the experts follow the revenue. This will apply same toe repayment accounting concepts. We will explore more about this magic principle in the next lights. 5. 5. Prepaid Expense Example (How Accounting works for Prepayments): Let's take an example for accounting treatment. Suppose you're prayed internist. Premium off $60,000 on 4th January and 70. What would be accounting entry in the balance sheet. You'd be debating people expenditure and the current asset with $60,000 suppose you're paid . This amount with cash will be carried in cash, a bank account and according entry each month and in the income statement or profit and loss statement would be repayment expenditure account debited, which is people exponential in income. Statement on revenue account and you'll be committing people Men Exponential Accounting The balance sheet, which is your debit. Eight Cigarettes. $60,000 on first Johny Toan, 17. Now let's allow bread. This particular treatment with profit and laws and balances view in the next late since we're made payment up to $2000 on 1st January 2017 the balance sheet account of people men expenditure, Undercurrent said. We'll have a balance off $60,000 at beginning of January. Children 17. Now each month, including January, will be a motor rising $5000. Since this is exponential, ridges equally sped toward here, so $5000 will be directed from the people and expenditure current estara counters again see and remaining balance off. 55,000 will be there in January 1 17. At the same time, you can see in the income statement below we have debated $5000 now in February will be carry forwarding $55,000 as opening balance in the people exponential calendars that account. And again we'll be charging fighters and dollars to profit and loss statement from the same amortization account people in expenditure and remaining balance That end of February would be $50,000. Similarly, in March, we level pointing balance off $50,000 again will be advertising $5000 charging into profit and loss statement as you can see below and we love the remaining balance off $45,000. This will continue till December 2 10 17. Well, prepayment explanation account in current asset will be zero and hold 50,000 will be debated topi in l as it was exponential for Children 17. But since it was painted once believed in charge it in one short in January, we had to immortalize it equally into in 17 since it was benefit for 2017. This is how matching conserve works with the prepayments 6. 6. Advantages and Disadvantages of Prepaid Expenses: at one day Descended said Wanted Yusof prepayments we saw mind for the cover its period. Since prepayments are done in our runs for the period, you don't have to remember each month or a period that you have obligation to pay particular expert nature in this case, insurance continuing with the insurance. Example. If you have paid your current years health insurance for yourself, you don't have to worry about it for the period of one year. Whenever you need to realize the insurance, you can just use it without worrying about its payment. Prepayment generates cost semi. Example. Many or lesser Clinton Services offers up to 10% discount and in some cases, even more. If you're willing to pay entire year subscription off friend, then monthly basis. So this results in savings you fear so. Subscription in your businesses. Tax deductions. The tax law allows most people expenses that spent 12 months or less to be directed when paid, even if the usual accounting is a cruel business, if the certain conditions are met, of course. Although this is considered a change in accounting method, an automatic method changes allowed to Reducto paper expense on the cash business for income tax purposes. What other they said wanted you so prepare mints. Cash flow. While making advance payments may seem to be good idea for many businesses, they do more harm than good. Ah, healthy cash flow must be able to sustain monthly expenses and in very purchases or any increase in pre payments minutely decreases the cash flow in working capital. What businesses with marginal cash flow repayments can mean less cash to pay for immediate expenses and revenue generating investment. A decreasing people expenses Results in inclusion, cash flow or operating expenses are typically paid on monthly basis, which is why any reduction in people expenses will immediately benefit cash flow for the current month. As an example, reducing stalk off people supplies for three months to one month immediately makes the available cash he cruel into two months were tops of place contract locking, Pity X. Many others Excuse me. Charities have loopholes in the Thompson condition. If you all look them by getting into the contracts, you will end up in paying penalties if you reach to cancer subscription before the period. Also in case of long term land or property lease agreements, if you want to cancel the agreement. There are some penalties, usually few months cental to be paid to the lesser or property owner, with results in unexpected explanation and cash our crossfire. 7. 7. An Intro to PRO Excel Models and Formulas: except Lieberman. Model robots. Pepperman Mourner will be preparing robots. Pepperman Modeling Micros Fraxel from scratch Whatever repack civil and we'll have other proofing everywhere. What I will be expanded. Well, it can be used for many other purpose besides Justin Accountants. Montane Voluntary cancellation. What I mean is that this model can be easily used in annual quarterly but getting off forecasting exercises as well more. There will be easy to understand and follow along for any reader, but the formulas used in the model can be overwhelming for non Excel users. Simple but powerful formulas and functions. I'll be using simple yet powerful formulas and function by combining them together in our moral preparation. Don't worry. I'll be expanding you each and every formula to be used in the moral preparation in the next section of this course, three regions off the modern. Will you be betting three words in of the moral In this exercise, First model would be simple, formula based, model, simple, formula based model is nothing but people. Men model prepared in excellent only formulas and function. But al highly recommend to use to macros, which are very simple. First Mecca would be give sheet name based on cell values, and the second maker would be creating high public, off index or table of contents. We should make her life very easy and your model very intuitive. But if you really want to keep away from me being micro's, it is fine. You can award this to make replication completely, and you can always give your sheet name a type name manually. Second region of the model Report Aquarian People Table based This mortally same is the first formula with model. The only difference year would be I'll be using Power query and people Table to create a really dynamic repayment somebody sheet that I will explain you more when we get on to this model. In this version of the model will be using macros, and we be extensively but not for calculation. So don't worry, it won't be asking is in difficult as you think BB will be used in adding new sheet taps dynamically, giving shit time names, automatically protecting the step with the click of a button. The most amazing part of this particular baby, a best morning would be to change your periodic view. According to the year selection For example, if you choose to see only to to a 17 year data, you can just select the same year. That is Student 17 from the drop down menu, and you will see only rows and columns for that total in 17 year period when other yes, that I will be hidden. Let's go through formula functional Excel tools that will be using in preparing this three virgins off the Modern. The first version of the model is only formula based. The most important function in model would be a function which is most popular function in Excel. It allows you to make logical comparison between a value and what to expect. We'll be using necessary function virgin. In our peppermint calculations, we'll be using various date function as well. That is late function, yeoman function date really function. Who will be using? We look a function match function in direct function. To prepare her prepayment somebody sheet, we were using name menus to create dynamic table areas or look up tables along with indirect function while working with Videocon function headed formulas. This is completely absurd to use every formula, but I will explain you. What are the main benefits and drawbacks are very formulas in general, while in our model scenario, certainly the other formulas will have extra level of accuracy. Insecurity it of elevation will be using simple listed of elevation. A drop down list. I will explain this topic when we're already inside The model Billy exercise, Acceptable since acceptable. Have a feature off water expansion. They work best with the devil addition list. She'd protection should. Protection is crucial for Arab proofing your water actually in any model, so we'll be locking cell contains. Whenever there is a formula in Excel model, we'll be using simple way of your court or break. She turns. Bears don't sell value. This will. We used to give accurate prepayment gen lame as a sheet name. Don't worry, this is optional. If you choose not to use Libya, you can always give me a new limb to your sheet tap and people in jail, we'll be using another simple but time saving. Begin micro to create sheet index with hyperlink sheets. They should be great to navigate shit around through the model. This is also optional. You can always create hyperlinks manually within your model. The second version of the Monetary power query Pure Table Region. In this version will be a playing all tools, formulas and techniques, as in the simple formula based model. What will be using power query as a tool to create dynamic data set in Excel Table, from which will be creating people table somebody, people meant somebody sheet. Prepare it power query table and paper table is the best among all the three waters off this morning as it is flak, civil and other proof. Also, any new Shi te por Prepayment Jelimo Any new kind of prepayment would be added to summary sheet automatically medically call for data refresh button. While there are few limitation in this version as well, it cannot work with Custom Year period and also unusual peppermint shit. Time cannot be locked, but I will show you work around for that as well. The third original peppermint model would be at once we be a modern. I will usually be according in these Virgin for falling additional features, adding you shoot their video click of a button. The new sheet name Toby at it has travel prefix up prepaid word. If you don't have that work, you cannot be a new shit. I will add that can affectionately, with the help of ubi according herring shit protection to form less is automatically. When you create a new shit, I will explain you more about model functionalities benefit elimination when we will be using the model India time in next few lectures. 8. 8. Basics of Date Function: Now let's dive into the formulas and functions and excel tools that will be using in preparing the prepayment models. This formulas and functions are critical to our model preparation, and I will not be going in very much detail of this formulas. Function expansion. Think this is not a excel formula and function course, but I will give you, ah, brief introduction off this formulas, and you will understand how I'm gonna be using the same in preparing a prepayment. So the first category of functions are date function, so first function is dead function itself. Eight Function returns the situation serial number that represents particular day. The same text for date function is year month and be all three parameters that compulsory your argument is required. The value of your argument can include one or four digits. Excellent dip. Register your argument according to the day sister off your computer. But before Excel for Windows uses 1900 exist, which means the first lady's 1st January 1900 second argument is required, which is month. It can be a positive or negative in teacher number. That means you cannot put decimals in that it represents a month of the year, which is from one put well so January to December. The argument it is required. It's a positive or negative in teacher number, so it cannot be a decimal. It represents day of the month from first to 31st. No, left to some practical example and understand this date function in action in Microsoft Excel. So let's go through the practical example for the date function. As I mentioned before. Date function requires three argument, which all our complicity. So you're a month and be so here in this example how three arguments year, month and day already put in in the cells. Now let's start with the practical example for the day function. As I mentioned before. Date function requests the arguments year, month and day. All these three arguments on compulsory Now How mentioned, for example, year, month and the arguments in column B, C and D. Now let's put this formula in column F is equal to death on tap. I will select year. I will select month Andy. I would close the parentheses and hit. As you can see, the date is tactical January 2010. You can always format this cell and display up the rate according to your wish. I mentioned 30 for Jane 2010 which is the way I want it. You can always support this, the arguments manually, like this 2010 one and tactical us. It will give you some result. So depends how you want to use the formula, and everybody has his own needs. But if you are a pro Excel user, you'll always use some cells as a reference because you want to use this formalized endemically. But in some instances you might have to use a pneumatic feeding as arguments, and it depends on how you want to use the former. So now let's go through a second example, which is 10 52 which is years. 10 months is five and days to. So since there is no year prevention here it is 1910 or 2010. We don't know. So let's see how that function will work in this scenario. So I will give formula in after l again dead hit enter. I will select the year s tent. One test five and A is two. I will hit and there again, and I'll get secondly 19 days. So in this case, Excel assumes that the your argument was for 1910 as it starts from 1900 as I mentioned before. So if you want this particular date for the 21st century, which is 2000 boyo to specifically mentioned the year with four digit letters. So in this case, we should have mentioned 2010 as we didn't grow 11. But the rest Excel will assume that it is for 1900. Similarly, if I just copy and paste formula down, it will assume that's true is for 1900 to not to does'nt now. You can always use other formulas inside this particular date formula. So for the year you can use other formulas as well. So there is also for Malakal year. You can use that while neither formula to derive this particular number dynamically, we'll be using similar kind of formula in our paper man model preparation, and I will show you how to use that when we prepare the modern, So the source of riff introduction for the date function it uses three arguments year, month and day. It has to be in teacher numbers and it can be used animate Kelly with other functions as well. So let's see another functions called end of the month in the next section. Thank you for watching. 9. 9. Basics of EOMONTH Function: inundate function categories. Yeoman function. This function is very powerful, and I use it all the time in my very US mortals preparation. So what is your man function? It returns the cell number for the last year of the month. That is indicated a number of months before off. Rather started use end up down function took acrid match originates or due dates that fall on the last day of the month. So the syntax off end of the month is physical to EU. Month start dead and months start is required arguing so our date that the president's starting date it should be entered by using date function as we've seen before was the result of a lot of formula or function. For example, used a 2008 year five as a month and 23 years a day, so for that it will be 23rd tomatoes. Any problems can occur if the dates are interes texts, so is usually actually not recognized as number it will generate. Another month is a required argument. Number of months before after the started a part of the Mellie off the Mondays, Sir future date a negative value results apostate will see this in action. Not acceptable. Now let's see some practical example for the human function as mentioned revolt. Your month function has targets started and months. So here in Selby, so hearing column B and C I have mentioned few days and months as example. So let's give formula human so physical to e o month, I will hit tap. I will select the start date as mentioned before you can put hard coded date or any other date function in this argument as well. So we'll be using cell beaten as our first argument and we'll choose Month has a zero, or you can heart court zero as mentioned before, but in this case will be using 10. As argument will close the Prentice's and are hit, enter has expected and not the man formation results, the end of the mandate off that particular date. So it was a June 1984 and if you put zero argument, it will give you end of the month off 30 June 1984. Now similarly, really use one in this particular date as a month argument. So end of the month started 12 May 1985 and month argument will be one and without touching a June 25. So try to May was the main month, and we added one more month index end of the month for 12 days. Talk. It was me and we added one more month in that it will be June, so we'll have a 30 June 1985. Let's take another example by every two months, so e o month select 5th June 1986. We're selecting two as a month argument and we got 31st August. So 5th June someone 10 for June will be 30 June 1986 and year two months to that. So it will be July and August will get 31st August, which is the last day off August. Similarly, if I just copy and paste this formula down for 5th June 1988 will open September 1988 5th June's a month off. June will be the Montana 30 June and idiot, and you had three months in that. You'll get 30 September 1988. Now let's see how this function works when have a month argument as a negative number, so we'll give formula E or month. We'll select the day will select negative argument for the month and for $9. 90. 63 we got 30 July 1960. Immediate montane date for the August month is July, so we got Carnival July 1963. Similarly, if you put minus two, we'll get June. So August is our month. Late in the reached, the 9th August recites, and then we have immediately minus two. So it will be July and 30 June will be are intended. Send up. The one function is very useful. Invaders financially occlusion report preparation. We'll be using the same enough people man model, and you have more idea when we use this function in mortal preparation? No, let's see one more function in that category, which is date value. 10. 10. Basics of DATEVALUE function: No. Let's say one more date function, which is date value function, which will be using in our model preparation to date value function can work sedate that is stored as a tax to a serial number that Excel recognizes as the date. For example, formula is equal to date value. 11 2008 returns 394 40 Which is the serial number off the date. 112 dozen e Remember, this result might be very depending upon your computers. These settings The formal argument for date value is did well you and date s tax latex is a required argument, which is the only argument here. So it is a text that the presence sedating accelerate for Mitt or a first to a cell that contains attacks that represent the dating accelerate format. For example, in order, comma 1 30 resonate or 30 a gentle, doesn't it? Our tax change within quotation mark that represents states using that the four day system in Microsoft excel for Windows date underscore Tax argument must represents a day between 1st January 1900 tactical. December 9999 The date really function returns the value other if the date no school argument falls outside this particular date range. If the portion of the date on the Scotus argument is omitted, the date really function uses the current year from computers built in clock. The time information in data underscore text argument is ignored. Let's see date value function in action with some Excel demo. Now let's work with some example for the date value function. So I have few dates mentioned here in the column B, which is trip to May 2015 12 to May 1985 and 5th June 1986. As you can see, I have put this particular dates as attacks, as are put in ordered comma. I start off this particular dates, so if I sum this three, I will not get any number at all. I will get counted, as you can see here. So this are tax entered as date. Now let's apply date really function to this particular Backstreets. So in cell detain, I will enter the value function. I will say, like this particular cell which is beaten, which is taxed as date and a headhunter, I get 42 1 39 which is nothing but a number a serial number for a date. So I would have to former this particular number for a date. So I will go to control one. Number four Matic and I will choose the date. Former I like. So this is nothing but filth. Tomato 1015. Let's applied a value Formula 12 Men and Unity five, which isn't Cell B 11. So I will apply date value function Select Be 11 and hit. Enter. So, as expected, I got 12 minute Indy five, which is now actual date you can use in a formal or function. Why neither calculations which you cannot use this particular former, which is in tax. Former. Similarly, I would just copy the formula in cell D trial for the value in B 12. So I get 5th June 96. Now, if I total this three values, I get calculations, as you can see here. So we'll be using this data. You function in combination with other functional Excel model preparation. I will show you very practical and effective use off this data. You function in our mortal preparation. Thank you for watching 11. 11. Basics of IF Function: If function is one of the most popular function in excel, it allows you to make logical comparision between a value and what to expect in some place . For me, function says, If something is true, then do something other rights do something else. So if statement can have to result the forces Elise, if yours comparision is true and the second is if you're comparision is force so the original in Texas if function is if logical test coma we're live true coma and off. Still, argument really falls logical test is required Argument the condition you want to test believe True the require argument the value you want return If the result of the logical test is true value false is an option argument the value you want to return If the logical testes false Let's take a simple example off a functioning or excel demo. So in cell C 10 will apply a function if value in beetle which is called Yes, get it done The true value which is one what I want to. So we got one because the condition is Matt Which ones? Yes. Similarly, if C 13 is equal to yes, I want one when the condition is true, I got what I want to. So we got one here because our condition is my If there was something else yet really got to. As you can see, a function can be used to really a turbo tax and values. It can be used to evaluate others. You are not limited to only checking. If one thing is equal to another and returning the single result, you can also use mathematical operation and perform additional calculation. Depending upon your criteria, you can also nested multiple. You function together to perform multiple competition. Not if you're going to use Texan formulas. You need to grab the tax around inverted commas like tax. The only exception toe that is true or false with Excel automatically understands the best way to start writing. And if treatment is to think about what you're trying to accomplish, what comparision you're trying to make many times writing an if statement can be simple is thinking true. The logic in your head. What should happen if condition is made worse is what should happen if condition is not met . We'll always want to make sure that your steps follow a logical progression or else your Formula one do. What do you think it should be especially important when you're create complex Nestor lifts treatments? Let's take more example for a function. So if actual is less than by J, we want to return with tax with everything. Otherwise, we want to return tax. Since 7 50 it's less than 800. So it is within the budget. We got the return value over it. In budget, we can easily copy paste this formula down. We get various sensors. So in the next example actually is more than the budget. So we got over budget. Similarly, in the next example. By 20 if I was budget and actually 515 were within the budget and so on. Now let's take another example using the same data sick. So if actual is more than budget, we want we'll do a calculation now actual minus budget. Otherwise, we want nothing that zero, since the first example has value less than the budget regard. Zero. Let's cooperate, a formula down and in second example, the values more than the budget. So we got 50 which is a difference between for 25 cents recertify and so on. Let's take an example off a necessary function. So in this example, will be testing expenditure. Budget it an explanation, actually. So if your budget expenditure or you can say for expert explanations, spend is equal to your original budget, there will be no bonus, no penalty. But if your actual expenditure is more than the budget, there would be a penalty. But if you're saved, that was your actual exponential is less than the budget that will be bonus. So how to write this formula? We'll see here. So if actually is equal to budget, we want to detain word call. No one is no penalty, right? So bone is an actual are equal. I'm giving absolute value sell here because we don't want to move when we drag it down. So that's why are you in this dollar? Science? We want to test one more condition. If the actual is more than the budget, then they want you penalty. Otherwise saving. So if actual is more than budget, so this expenditure there would be a vanity will be giving absolute reference, and now there's only one condition left. It actually is more than the budget, there would be penalty so they can really want more. Possibly that is bonus. So we'll be selecting this value as a final false value will be completing are a function press center. Now, in our first case, there is a bonus. Why? Because actual spend his lessons a budget. So let's take this formula down in second example, since the expenditure is more than a budget, that is a penalty entered example. Actual exponentially is less than the potential there is. A bonus were in fort examples. There is extra expenditure. He called the budget, so there is no bonus no penalty. Similarly, in Laskas, also, actual expenditures equal to budget. So there is no penalty. So this is how you use necessary function. There are some drawbacks of using a function, while excel allows you to master up to 24 differently function, it is not always advisable to do so. Market Felipe statement require a great deal of thought toe. Build them correctly and make sure that the logic and kykuit correctly through each condition, all the way to the end. If you don't nest, your if statement 100% accurately, then a formula might work 75% of the time but returned unexpected results 25% of the time. Unfortunately, the art so bucha, including red peppers and this slim multiple apps statement can become very difficult to maintain, especially when you come back sometime later and try to figure out what you are someone else you was trying to do. If statements require multiple open and close parentheses, it's can be difficult to manage, depending upon how complex your formula becomes. This is the kind of function will be using in prepare mint calculation in our model. So we'll see this in more detail when we prepare our paper midmorning. Thank you for watching. 12. 12. Basics of IFS Function (Excel 2019 and Office 365 Only): its function. The Excel dysfunction runs multiple test returns a value correspond to the 1st 2 results. Used its function to evaluate multiple conditions without multiple mastery of statements. Ifs allows shorter, easier to read formulas. Important note. This function is only available for office. Texas devices Crabbers for Excel 2016. You fear X eligible 16. We don't office Extra Defies description. You won't have this function in your Excel version to buy plus dysfunction test Small for condition returns. The first true value return value. Where Lucas ponder the force to result is the return value and syntax. Is it called apes? Test value? One. If it is true, if it is not true, it goes to test True and tries to give the second value. If it test is not true, it goes to the test and gives you the value it goes until you. It finds that true value. Targu answer. Test one first logical test, and it turns the first value, which is true. If not, it goes to a second test and tries to see the value. If not, it goes on till 1 37 test, which is the maximum number off Chester country with dysfunction. Please know that you can always use or and and function within the if function so and and or you can use is a multiple criteria within. If statement, I was sure the demo off use off or in and within if statement in our exit date. Prepayment calculation. Very soon. Important notes use the its function to test multiple conditions. That eternal value corresponding to the 1st 2 result, unlike if function ifs, allows you to test more than one condition without nesting. This makes for wheeler deliberately, very easy. Are you go inside, entered in test value pairs. Each test condition. The present illogical tax returns a true or false, and each value is associate with the previous test. Ah, valued is return, but it's only when the test is true and the first times with the two result wins its function supports up to 1 27 conditions. Now let's see its function in action in Excel M. O. As you can see if function is argument off logical test one than if theological test one is true, then it gets a value and go on so you can do 1 27 conditions in its function. Next example. Here we have greats obtain and we're do give a B C D e f As a result. Now, I already returned from right here and you can see ah, the return formula and will be writing the formula again in this particular area. Let's try lighting formula in a row. 10. So is a quarto apes. If C time is greater than 89 we won result. A keep seat then, is greater than so in 29 Do you want we Cape seat and his character than 69? Were you born? See as a result, any seat and he is better than 59 be born result as a I see. And now last test were careful were to port a true value because if all these tests that is one to 34 fails yet please one of true value. So you can write a true like this as a function and you final answer, which is so this is the same largely gonna be using in our accepted prepayment calculation with help off if statement So so he Every fall up, the conditions fails. We want true value, which is our final answer. So in this case, if the score is less than 59 b one f, So that's the reason we're to put true because we know that anything less than ah 59 will always be f so you can put true and then the result off the same, which is F. I'll press enter and but copy the formula down. As you can see we got after in this particular scenario where the score is 58 and that it last condition was If the score is more than 15 only then we'll get the but followed these conditions. 1234 are feeling are not true that mere true. Otherwise there would be at a mill. So if I remove this that see what happens, we get an ad. We don't want that right. So toe award Dario to right. True. And then, ah, final result, which is the ultimate answer with this four condition couldn't get so I enter and I get that. So this is the basic logic behind this function, and I'll be giving you a practical demo in building our exit date formula complete template which we did with the nasty the function. Ah, but this time we're gonna be using if statement which would be far easier than the nasty relief. Don't forget this points while using if statement there is no way to set a default. If all presidents force that is, the valleys falls instead, enter the true for the last test and then a value that returns are before If it is false, that's what we did in our demo when I put true. And then I put F because all of the condition has failed. All logical tests must return true off us. Other results will case if stew return of evaluator. If neurological does returns, a true ribs returns an air as we saw when I removed the last condition Just true in F we gotta an error. So to be careful while using a function with this three criteria in mind. Now let's go to our axle templates that will be using if state when instead, off nest relief in both our example Where we're gonna be using this if straight men for ah exhibited calculation for the pre payment a cruel and also end up the month calculation Ah nrp permanent rule. So I'll see you in the next video. Thank you very much for watching 13. 13. Basics of VLOOKUP Function: let's go through some look off functions that will be using in our model preparation you can use. We look up one of the reference and look or function when you need to find things in table arranged by row. We look up simply means we look up what you want to look up. They really want to look up the value. The column number and range containing the return value exact match. Approximate match, which is indicated by zero false and one. By true, the formulas in Texas we look up is look of value. Omar Table Larry Kumar column index. It is numerical value and range. Look up. Look over Belize. Require argument the well you want to look up must be in the first column of the range Upset you specify in the table area. For example, if the table array, Spahn cells B seven to B 7 10 year Luca Value must be in column. B. Ebola is a require argument. The range of cells in reach we look up will search for look of value and return the value. The first column in the cell range must contain the look of value. The survey also needs to include the return value you want to find column in eggs require argument the column number, starting with one from the left. Most column of the table area that contains there it on value. These look up his option argument, a logical value that specifies whether you want to look up to find approximate one exact match to assume that first column in the table is sorted either new medically or five vertically and will dancers for their closest value. This is the default matter, if you don't specify one for such is for the exact value in the first column so far as will be using the false argument because they want to exact value. Now let's see. We look off former inaction in My Excel demo. Now let's look at Real can function with some simple example. Here I have a table here with division and column B sensing column C column DS Cost tops is and I have a profit in column. Now I have a small dashboard here where I'll be creating a drop down list in cell attached well with divisions, names listed, and then I want to pull individual sales cost top sales by changing the value here. So let's create a list from little valuation. To do that, we go to data data validation. We want a list from a range, which is here and now. You think? OK, now we have ah list with Division A division realistic. Now let's write our first Wilco formula. We look up now look up values division in this table. So we have to select that astral table arrays this table so we'll be selecting that travel entirely. But we're to make sure that that look of value starts in this first column, which is division because I look over losing column based on your table that we must start from that particular column and now column index. So in which column in this table array to return, value recites, so return value for us is safe. So it is the second column. So division is the first column, and sales is the second column. So this is the look of value column. Andrea or Togo, right for our little value. So sales in the second column so we'll be writing to here is a numerical value, and now we want exact match because our table is not sorted for the values center. We got 5000 which is the correct says Well, you know, let's change this division here so I'll change Division B and I got 7500 solar Division D. I got 8500 which is correct. And let's change one more time for Division E and I got 9005. Similarly, will be writing one more vehicle formula for arriving cost up since value. So for Division E, which is a look off value table, a wood remain seem B 2070. But this time the return value recites in third column, which is Divisions column, which is BCD. So 12 entry. So I'll be writing three year in column and axe again. We won't accept match so well with selecting false. So they got six set 50 as I return. Really, which is correct. As you can see here for Division E. The cost of sales. This except 50 and all the values are changing according to the selection in extra. So if I choose Division C, I get 11 sales value for Division C 6500 cost structures accordingly, similarly, will try to pull profit figure as well, depending upon the division selection. So astral is a look of value. This is our table area, which is B 12 toe e 17. Now the column index will change again because we want profit, which is fourth column from our look of value column. So it is. Divisions column is B C D E. So 12 3/4 off fourth color where under 10 values. And again, we want exhibit actually selecting Falls Press Center. As you can see the same place version of dashboard start off table, you can change the values ing in natural, and we can be rivaled the figures for sales, cost, structures and profit. Best way to use we look or function is to make all arguments dynamic within the function for start human, which is look over loose, quite easy to make dynamic. You just auto select cellar, after which has your look of value, and you can use them as absolute cell reference or writers have preference, or you can use a name range for the look. A vendor itself Look up area look up table can be made dynamic by using name ranges or Excel tables along with indirect function, which I will explain you in that indirect function. DEMO COLUMN INDEX There are more than one way to make column index argument dynamic. You can use columns function as it will give you column number from the related position, but my favorite approaches to use match function for the column index. I will explain you match formation in our match function demo. Soon we'll come back to this particular example again, and I'll be using match functions in direct function. Name ranges together to create a dynamic we look or function formula, and this trick will be really helpful in creating various reports and somebody's and also in creating dashboard reports on it will be really useful for you in any kind of other reporters well. 14. 14. Basics of MATCH Function: Match function. Match function searches for a specified item in range of cells and then returns the religion position off that item in the range. For example, if the range A 1 to 83 contains the value fight 25 38 formula is equal to match 25 they want to a 30 It does number two because 25 in the second item in the range. The same tax off match function is physical to match. Look of value. Luca Perry and match type look of value. It's a require argument value that you want to match in Luca Perry, for example, when you look up someone's number in telephone book, you're using person's name as a look of value. But telephone number is the value you want. The local value argument can be value number, textile, logical, really, or a sell their friends. Store number. Technological value. Luca Perry is a require argument. The range of cells being searched Mets type is an option argument, the number minus 10 or positive one. The Mets trip argument specifies how Excel met look of value with values in Lucca Berry. Before really for this argument is one. Let's see match function in action with some Excel demo. Let's see match function in action. We will be using same letters it as we used in we look or function demo. So I have ah, division sales, cost officers and profit from column B to eat and division was data from below 12 to 16. Now I have this list created from a previous example. This is a data validation list which I have showed you in. We look up them or how to create it is very simple. So let's try to find out. Position off division A BCG with the help of match function. So it will be writing match. Look off values are Division I 11 which is the value here and now You don't go for a look up areas. So which is here? So are look up at a is B 12 to be 16 and we want exact match. Close the parentheses and click enter and now we have four as a position return value from match function, which is division these position from our look up Aaron. So one through 34 that is correct. Now let's in this particular value in 11. Toby, we got two, Which is correct because Division B is located as a second position from our look up activities from Read. Well, so it is a second. Will you? Now let's apply match function for looking up position horizontally. So match. Now we want to look up. What is the position? Horizontally that is column position for cost of self in this particular table. So we'll be selecting car stop cells as, ah, look of value. Look up there would be this true. So it is B 11 to 11 and we want exact match. We got three as a position which we can calculate easily. So division sales and cost stops if it's a hard position from our from our Luca Perry. So we have selected Beeler 1 to 11. So so b is the first column sees the second column. These the Targa, which has cost ourselves we got three other they don't value, similarly will apply match function for looking up profit column. So Ash 14 is a look of value. Will be selecting this throw as a Luca Perry and we want exact Matt. So we're selecting zero. We got four as a return value because profit is the fourth column from division from our Luca Perry. So much function alone does not solve many Excel problems you might face. It works best when it is combined with other look or function like index or we look up. I like the look of function. Mad function does not have a table. Areas a look up are giving. Rather, it has a Luca Perry, which means Matt Function looks our values one way to give you answer of look of value position. He can add a look of values of derive related pollution vertically within the single column range or horizontally with single orange. We will use match venture to look off value in draw range within Luca Perry. When we will combine the match function in, we look up in a second accidental, so we'll use match fun. Gentle driver The column and eyes dynamically in every look up. Second demo 15. 15. Basics of INDIRECT Function: indirect function. Indirect function returns the reference specified by attacks. Strange references are immediately evil, ready to display their contents. Use in Derek when you want to change their friends to a cell within or formula without changing the formula itself. This in text for genetic function is in derrick in brackets from attacks. A one no friend taxes a require argument, our friends to a cell that contains even style reference. That is our one silver style reference our name defined as a cell reference or our friends to Acela's attack. Strange if reference taxes not well. It's a levels in directs the monster reference our value. A one is an option argument. A logical valueless specifies what type of references containing the cell in direct function individually is not that much useful. You need to use this function along with other functions, like we look up to create dynamic range of references. Since indirect function, converse tax trees to references they describe, effective while creating dashboard or using your tax strange dynamically read other formulas. Sonar occurs. It will be vehicle formula, so let's look at indirect function along with other function as well, so we'll use genetic function along with name ranges. We will see practical application open Eric function along with we look or function in our next we look up demo. I will show you how you can use this indirect function along with name ranges to create a dynamic dashboard and we look, a formula itself will become very powerful. 16. 16. Basics of NAMED Ranges (Name Manager): name oranges, a name or name ranges a meaningful short, and that makes it easier to understand the purpose off a seller reference. Constant Formula Table Each of is really difficult to comprehend. At first glance, the falling to information shows common example some name and how they can improve the clarity and understanding. So some C 22 C 30 is a range of some, so you can name this entire range C 22 C 30. As the first quarter sales, you can apply formula accordingly. So instead of C 2030 you cannot apply some first quarter sales, and you'll do existing result. You can use it as a constant. For example, product formula has two arguments. Product If I, which is a value in a five into 8.3, which is nothing but an accident so you can use name range for price as a five and 8.3 as a sales tax so you can apply product price, which is a value off. If I and sales tax eight point, it will do some dessert similar to product formula. You can apply their formulas that you can see in the table below. They believe name often Excel Table, which is collection off rate, about a particular subject that is stored in records, rose and feels. Column Exactly. Is default acceptable names like Table one table to insulin, but you can always change the same and give it some meaningful name according to your wish . Scoop up the name. All names have a school either a specific to worship, Also called local work, should level entire workbook Just called global workbook level. The scope of the name is the location within which the name is Recognize without qualification. You can define name by using name box on the formula, but this is the best used for creating workbook level name for selected range. You can community create names from existing rows and columns levels by using selection for cells in the worksheets. New name Dialog box. This is best use for when you want more fact limiting. Creating names such as specifying a local record level scope or creating name comment. Falling other syntax rules for creating names. Valid characters. The first character off her name must be a letter and underscore or a bachelor s remaining characters in the name can be either numbers periods underscores our characters. Seller Francis or disallow name cannot be name off a cell reference such as that 100 or are one C one space is not, well it. This is a not a loudest part off the name use underscore or a period as a word separate er such a sales under school texts or first door quarter name land a name can contain approval . 55 characters, case sensitivity, A name that contained upper case. Lower case letters. Excel does not distinguish between uppercase and lowercase characters and names. For example, if you create a sales with a capital s and then create another name called sales all in caps in the same world, Book Excel prompts you to choose a unique value as it is the same for Excel. 17. 17. Advanced Version of VLOOKUP Function (All other formulas combined): Now you're back to our original. We look off formula sheet here, I'll be expending you how to use. We look upon a king along with match function, using match function in column index in are we look up parameters. So in a periods example, we have hard coded the column in Next value. Just like to hear 33 here, as you can see here. Column index He stood out that will be applying match function to derive that particular column next. Dynamically. So in this particular small table, I have a list for divisions A to E as you can from this particular table here you can see. And now I have a least for KP eyes as well. So sales cost of sales and profit. It is a list former soul, but I think we look off formula here. Look up. Values are division name, which is in at 18. Here, table every would remain sandwiches B 12 to 17. Now, instead, off column index hard coded. We want a dynamic desire. So to do that, we need to use match function. So we're playing match function. So match and what is a look of value. You want the position off sales, cost, options and profit depending upon their values we put in this particular cell. So in the 19 whatever the value is, we wonder column index accordingly. So Gene and it would be a look of value. Luca Perry would with this particular role, and we ought to make sure that they're in sync with the same. We look up table area so it has to start from column B and accordingly, or to select Darrow Little repeat well to eat well and we won't exit match and again for re local formal. Also, you want exact match. So we'll selecting falls and press enter now. I got 7500 sales, Skippy I for Division B, which is correct, I can see here. But now the best thing is if I change to cost oxes, I get 6004 Division B, which is correct similarly for select profit. If I select delusion e, I get to 650 which is correct. And if I changed two revision Lee, I got profit off. I wondered which is correct. So this way you can make real co formula more powerful with the help of Matt Function in this kind of scenario, so I'll be going through a more complex version of we look up again. We'll be selecting a table areas a dynamic parameter, so let's look into that as well. Now, in this example will be using dynamic privileges with help off indirect function and name ranges. So instead of this kind of data, which is one table for all division and all KPs are listed properly when table suppose we have division wise KPs and there are different different tables for each division. So we have sales cost Thompson's profit for the region a similarly same structure for Division B use NC, Division D and E. Now we want to pull this KP eyes dynamically based on the import selection like sales, cost, upsets and profit, and also division ways we want so as to be Division B division agency. So how do you that let's write this formula from the scratch? So first I would be to create table limbs and name Lindsay's for neutral tables from Division A Division E. So to do that, we'll select this particular portion for Division A and we're to goto this name Upsell and type region. Now, as I mentioned in our name range, formal explanation, they cannot be any space at all, either it has to be underscore or not, but I can write directly division years. Well, if I want to give additional space, I have to use underscore like this. So I'll be writing the name of this particular range division. Underscore A. Similarly, I'll be creating for division Be, so I'll be typing the region under school. Be similarly for the Regency Range. I'll be typing division on the school. See, now we have all the table names given for each individual table ranges every genre score A , B, C, D and E. No next step is to create a data validation with the same names as we have given. Here he was will be converting this particular tax name with help off indirect function toe a reference. So let's get that first. So I'll be creating list years division under school and I just cold feet. And, uh so now this particular tax change are exactly same as our names, as you can see here, it has to be same otherwise are in Eric Function will not convert this references from a tax strange now will be creating in this particular cell on Let's stop this names because they want dynamic table arrays were no creator list for this particular names, which are exactly the names opened. Usual table. So we go to rehabilitation from a list and this is our range. So we got the list here. Now this list itself is a name for particular data set or reference orange. No, let's right the local formula with help off indirect function and name ranges. So we look up so look up. Rallies are KP eyes and what will be the table? Larry, we want an immutable area based on this name, so we'll be selecting this particular value. And I'll show you what happens if you don't use indirect function along with name. And so are we selecting division and discord, which is nothing but at 42 and our column in days would remain saying because all the tables have ah second column as our KPL value. So be selecting two and we off course one false as exact match. I got an error. Why? Because Accent doesn't recognize this as a reference and to do that. We need to use indirect function along with the name regicide disliked indirect, and I'll be completing the bracket for each 42. And now, if I press enter, I get that create value for profit for Division A. It's just correct. So if I changed two Division B, I got 1500 which is correct, which is a prophet of revision. Be if I select division D. I get 1000 as a value, which is correct. But if I select here self, I get division. These says, which is 8005 marriages correct here, so you can create dynamic table areas as well. You're to use indirect function along with name ranges, so we'll be using same technique in a people meant more or less able to create a dynamic prepayment. Somebody thank you for watching. 18. 18. Introduction to Model and Control Panel Tab (Important Sheet Tab): Hello and welcome. I will be doing exercise to building this model. The first model is formula based model which I will also call it simple model. So I'm not using power Kredi or accelerate being this particular exercise. So this is a simple formula baseball. But I would recommend few. We be a course to be used in this particular which are very easy to understand and it is really convenient. But this is totally optional. You may choose not to use it at all. I would highly recommend you to follow along with me. And to do that I have provided a blank copy of this particular model which is available in the resources second for you to download. Now we are in the first type is called control panel, but I have a small table which has key inputs. Now this yellow cells are hardcore. Did include feature to put manually but this model is for number of years. So the order only once when you start building this model as you can see in ceasefire cell C for there is a start here. So this is the year from Is the moral will start operating in your financial and account department and C six contains a number of years for its you more. Do you want to use this particular model? Right? So you might use for one year, two year, three year or even 10 years You can use it not only can expand the actual number of columns and rows, and I will explain you how you can do that when we do the exercise. Now see seven cell C seven hazard drop down menu, which is data validation for number off months, which is gentle December. So this is for fiscal, earlier or financially, Mr on your regional country. So for me it is January. So I will starting from January. And if you're another financial or fiscal year, start month, you can choose accordingly, April or ah suppose June. And accordingly you can choose here so we'll keep it here. January for now. Now sell. See it contains start date off the model from this the 1 10 will start. So, for example, January is my first month off the year in fiscal year Gender December, which is also Calendar area, and I would like to start the car collision. End of the month for all the paper meant that happened in the regular month. Similarly, if we have a parallel your first year, you would want Tokai cleared the prepayment at end of April because usually the count send books off accounts are close. That end up the month. So that's the reason we want this particular it automatic calculator from based on this train books, which is in C seven and C five. So here I have used end of the month formula which have explained before it did, I was the end of the month, a date from our date. Now, for us, this is not going to be straightforward. We don't have actually, we need to delight this date for end of the month formal, which is a start date and end of the month. Now we won't end up the month for this particular monthly, so we won't be having an inclement. So it would be zero is the second argument, which is months here and start date is the one which will be calculated based on ah in port in cell C five and C seven. Now, as I told you, we don't have a straightforward eight year, which is required for an abdomen, took a clit, that particular output. Now you do have a year and months so we can use a formula called date formula. So for the date formula, you required TRG, which is year, month and day. Now we do have a year as a number in cell C five, but we don't have month and it as our portability available to use in this formula. So to do that will be requiring another formula called month, which will be what, a month number calculation, and in that will require a date value function to convert this particular debt into actual date. Because unless you do that, this particular function will not work and you cannot arrive at the your dessert output. Now, this looks quite complicated. So to explain you in very simple manner, I created this particular table well will be linking each function and deriving this particular date. Now we want to achieve ah month number because we require a month number right, which is actually Phil number, which is 12 12. So, for example, if I type here months, it returns number from 1 January 2 12 December. Now we want to eat you from this particular tax value, right? This is not actually these attacks for excellent will considers attacks strange, but we don't look. And what this particular texting in to actual numbers Apple Should we call to four May? Should we call to fire? And similarly, Jan should be called to one. So we want to do that Missed on this particular formula. So to do that, we need a date value function. And also in that we need a small trick in there. So to do that will type date value function, which you can see the syntax can ward sedate, inform off to tax to number that represents the 18 Microsoft Excel Date table court. So simply Converse attacks date into actual date. Now let's start to convert this, so you'll better understand how this particular function works. So if I pull date value and select this particular cell c seven, it will generate binary because it still requires some form of late recognition, which this former like understand. So to do that there two x some form off number or numerical operator along with this particular tax. So to do that will be joining this particular cell C sandwiches January for us now with a number like first or second or third date because we want a particular date here. So I'll be joining and person and one with that because I want the first of January. And if I press enter now, we want to use this particular output for date. Well, you in a month function. So if I type month here and select this particular day didn't give me one Why? Because month derives the number of that particular month in that date, which was January, right? So if I change here to Fab, I'll get to If I change to appeal, I get for similarly If I changed 2 October, I'll get 10. And why we need this to use in this particular rate formula where we need this month serial number. So now if we complete this date function will be selecting year for Cell C five, which already Harrah's input Montel was selecting from one formula which is our put off this month function, and they we can put anything that I read the end of the month. So I just put one here which you can put directly into formula so you can see this is simplified version and this is more often nested function, which is coming into one formula. So end of the month date started with with this particular date from this date function day to 17 10 and one, and we don't want to move any months. We won't end up the Monday for that particular date, which would be forced October and the end of the month off us Doctor, Where is sort of a soccer ball, which you got here. Now let's change it to January because our model rebels John Gentle December calendar year now is expend before I want end of the month of January because that's where my first month off my student 17 accounting will start and I'll start the bookkeeping at end of the month . And since this is five year model, so it will end it end up their 50 year. So, for example, if I take this particular model and run for 16 months, which is five years, it should I not Oliver's December 2 dozen 21 Now to explain you ready simply how it gonna work? Let's put this number of years to one. Only here, and as you can see it, will run for 12 months, including the January month. So gentle December, if you can advertise from gentle December January Blue, your first Monday, December Rubio last month. So accordingly, 12 month period is calculated here for one number of years, based on your date selection. Of course, if I choose are a paralysis started, It'll end in March to the natives of Capital and 17 Dreamer forced terror. Peppermint calculation and March will be my end last month off that particular repayment from next lecture. I'll be starting with the actual peppermint calculation in our first example Peppermint health insurance. So I'll see you in my next video. Thank you. 19. 19. Formula Based Prepaid Expenses Model Deep Dive (Part 1): Let's start with building the actual peppermint model, and you can see our template already prepared here for the five years so the columns would be from start of the month, which is nothing but 34 generations. 17 which is the start year and number of years will be five, so we'll be starting that date still prefers December 2021 so 16 months is a peer of this entire model. Similar Leader Rose will be linked accordingly. For 16 months, Selby titty presents the year in which this model would be working. So today we are in Providence. I went in. But if you move to 2018 and you can put this date accordingly with hard coded entry now let's do this particular throw, which is profile and tar Date range by linking, started off the model from the control panel. You can see it, which is nothing but January 2017 and for the following months would be using end of the month function to implement this particular month by one. So it'll be Fab 17 March 17 at Maryland so on. So I'll be copping this and fire formula in the end of this model, as you can see regard Technicals December 2021 which is exactly same as this cell C nine in control panel or similarly, in cell A six will be leaking the start date from the control panel. It is started gentle, dozen 17 and and will be in committing the same by one month. Are using end of the month function like this. Just drag it along due December to those on 21. And as you can sell pre created of formula here with account, whether I'm putting attacks count adjoining with a function off concert, discounting the number off entries in this particular danger, which is from is six still a 65. So we have total 16 once as a conforming that this is a 16 month model. Similarly, you can count the number off entries here in the entire column range. 16 months. We should have given this is absolute reference just in case formula moves along. So both g file and a sexual absolute reference from the control panel and other formulas can be a relative references as they will be incriminating one month anyway. Now I'm assuming that every month and will be accumulating all the prepayments and entering the system and this particular people main model. So end of the month would be same as the start date for this particular month, So we'll be using this model from 24 January 17. So that is that mortal started so I can link radically. Sell a six in Cell C six and ended will depend on your prepayment. Months over example will be doing a paper Moreau $2500 starting from January, or it can be any rate. For example, you're paid it off. 15 January will be assuming that will be a motors in front government. Why we're doing that? I'll expending you in detail with an example, You'll be assuming a going concern concept, which assumes that your business will go on. And if the expenditure passes from one month, another always averages out. Don't worry, I'll be explaining you why we're using every month. And they did not exit date. So prepayment. So 35 Januarys. Our first paper mandate and support it is for three months so and it would be we'll be using and off the one function again So start date is our start date and number of months will be from the period. So it's showing us tip a separate within 70 which is incorrect. Why? Because we're starting from January and we want dramatized for three months or just re January, February and March. So it should be 31st March Madness. Everything here. Very reassuring year target A built in 17. So we have two minus one from B six year in dysfunction and we get the correct date. No monthly charges. Nothing. Bird 2500 the world one on one off months. So it is a $33 we can round it off up to two decimals now in draw fire. Starting from column G. We have these dates and these are Exactement, 10 dates. If you can work this particular display, for example, I will assure you the actual dates there. Almonte. Tendex, right. This is good. This is what we wanted for this murder to be as simple as possible and also to have accurate calculation off monthly amortization. 20. 20. Formula Based Prepaid Expenses Model Deep Dive (Part 2): now let's right the most important function in this entire moral exercise, so we'll be using a necessary function as well. So there will be two tips and I will expend you this particular function in as simple minded as possible. So far, we're rebuilding our first if function, and then we'll be nesting off with another function, so it will be easier for you to follow along. So if G five is less than or equal to do you six, we want the amortization in this month, which is at six monthly charge. Otherwise, we want zero and let's fix, grow range for G five column range for the six and column range for F six so we can drag it along as you can see. And it correctly, it's showing zero fighter plane because we're not gonna be immortalizing it beyond March, and it's answering. Marcia Jan. Feb. Marks are more ties and should show Jennifer margin. It should equal to $2500 which is matching. We decide peppermint now this formula is not foolproof. I'll tell you why in a moment. So, for example, will take another payment by corporate this particular date range down so support in February, we have another pre payment for $1800. Monthly charge would be $600 which starts from February. So it's it's paid somewhere in February, and since it is for three months, it will end in separates off fair March and April. So this is a correct date range. Now let's corporate is formula down. I'll see there's something wrong here. It starts in January as well, which is incorrect. It should start only from February and there should be zero here, and this is right because it shows apparatus. So it's correct. So it should start from February and UNEP it and this maze working fine because we're not gonna be advertising it beyond April. So this is correct. But how to fix this particle issue as this would be a dynamic model and every month will be adding more and more values. And this would be like a template. You want toe take care of this particular problem. So to do that, we're toe editor particular function with another. If onions will be adding one more a function in front. So if start date is more than G five or exito otherwise, if the well news false, we already have that correct function in place. And as you can see, it is now correctly done. So let's fix the column in age for Ah, see seven and grow in 45 Now what is actually doing in this particular example? In February, when we started, we want to make sure that that is the amortization in January. So to negate that we're to put if conditions. So if February is more than January, which is our village? The case. So it's a true case. Put zero here because we don't want to start from January Juan to start only from February . And if it is not the case, he was this poetically function, which is normally calculating anyway. So if you go to forever, it will make more sense in this particular scenario. What is the condition? Year. So C seven, which is February. It is it more than edge fire referee. No, it's not. It's actually same. Why? Because they're using month and accurately here, so the zero condition will not apply here, so it will escape through this particular condition, which is false. But you can find here here in this case if edge five inches, Feb. 17 is less than or equal to these seven, which is the case here. It is less than a print religion 17. Then we want more time. $600 ammo ties, but it will end in May. Why? Because it's not less than or equal to for Apple process, but it is actually more than they're so here, the zero values coming for our next a function, which is zero. Now we can copy dysfunction to our, uh ah, Liro to make sure our functions are all the same. And let's also corporate its function till the end of this particular period, which is December 2021. Similarly, we can copy his entire range. David Rohl, 65 and monthly charge also didn't rule 65. Now it's giving a division at A, as you know, it is trying to divide by zero. So it'll always ever division at eso to remove. That will be using if other function, if error we want simply zero. Next people would be in separate for six months for $5000 since I haven't corporate this so let's copy this down for us. Windrow, 65 will end up five years, so total 16 months now. Since it's starting from April, it will only start from a pill for six months, So if you count, you'll go until September. As you can see, the model is working just fine and accordingly. Let me just populate this particular details for next five years so we can make sure that Molly's working accurately for next five years. So let's take another amortization in July for six months. So it's showing correctly, starting from July and ending in December, as you can see here for six months, and also the entire amount is also matching, which is 2500 which should be the case as anti peppermint. Among now I just copy this particular scenario. Stilley end off the modern 21. 21. Formula Based Prepaid Expenses Model Deep Dive (Part 3): Now I have populated Demi figures for entire five years for this particular people, which is prepared Health insurance, As you can see, we're gonna be renewing are prepared at insurance for six months each year. So that would be truly news each year. And there will be nothing in between. So if I just make this progress green smaller and zoom out, you can see we have, um, organizations accordingly. So, for example, April 2018 there is, ah, new pre payment of $2500 for six months. It will start from April 18 and it will end in supreme aerating. As you can see here and accordingly, you can see the prepayment will be more taste. Now we can put some totals here. So, for example, we can take entire five years total like this can board it, and also our monthly people mints, which will be the entire range total. So, for example, January it's 8 $33 accordingly, we can copy paste Ill 2021 December 2021. And since this is a peppermint and there should be opening balance, right. So since assuming we're going to start this particular model from start up the one you to resume certain amount lacks exhume opening balance off this particular had insurance in our book as on 1st January during 17 is through those and $500 right, And we have to do addition for the month less amortization for the among, which is nothing but this figure in a row 66 and closing balance now to calculate edition in each month for the pre payments for this particular borderland for entire five years, you to use the Summit's ah function, which will calculate the edition for entire months like Jan Fab March dynamically toward the are period of mourning, which is still December 2021. So I'll write a formula here, which is some ifs now. First criteria. The argument is somebody. So where we want to get this particular among from so that this prepayment among so this particular column, if I were to take a car range Kinross 65 were to fix it because we don't want to move, all our figures for the people, in addition will be coming from this particular column. So we have to fix it with absolute referencing now criteria range. So Criterion is nothing but a month value. So what is the range? As you know, that all their particular montane dates are similar, so we can take any particular day train. So we'll take from this particular column, which is a and will be fixing that as well with absolute referencing. You know what is the criteria? January? Because we are calculating edition for the month of January and we can fix role reference and we go two dozen 500 as addition in Montauk January. Which is correct, as you can see here so we can drag this formula long still and all for modern and see whether our formula worked or not. That's tests like this particular addition in Apple, which is 23 to fight, too. We go to apprehend 17 and this correctly to fight to again. We'll check October 17 to 5 to three. Let's go to October 17 to 5 to three, so that is correct to 5 to 3, and so on editions are correctly populating here. Now we're to reference the amortization for the which is nothing but total amortization. In the month of January and so on. So it will be 66 and closing balance would be nothing but some off. All this on now. Opening balance would be free. Grossman's balance so and amortization would be same on clothing. Now we can drag this plantar formula in the end. And as you can see, we got closing balances correctly. So this is how you prepare a very simple but very accurate prepare mentioned you for any people. An expert nature you have. So now will be coping. This particular Pepperman sheet for at least five more people means to make sure that we have, ah, need auto work along and make us somebody up. So I'll be creating more prepayments with random figure, so there would be no new formula. There it will be same sheet. I'll be called ping your long and just changing the values and periods accordingly. And it will have ah different peppermint balances. So from that will be preparing somebody sheet. And once I prepared this fight shoot, I'll be showing you how to name them. Ah, accurately. And was doing that would be preparing people made somebody here, so thank you very much for watching you for any questions. Please leave them in the comments section and I'll be happy to answer that. Thank you. 22. 22. IFS Function Month End date Prepayment calculation: Hello and welcome to this video. In this video, I'll be showing you how to use ifs function, which is a new to Excel 2016. If your office six it defies description if you don't have office exit. If I subscription a fair standalone office 2016 that is Excel 2016. You won't have this function in your Excel version, as they've seen in the introduction video for the its function. Its function removes the nest native requirement for multiple logic condition. You can give up to 1 27 logics and deliver the value with its function. I'm in the completed model, which is my intended calculation for paper memorization. So this particular work, but you can find in the package you can download from the coast section and instead over. I think this whole formula again will be just editing this existing formula return, which is not straight if with that dysfunction. So let's try to do that instead off if will be just typing beeps. As you can see here we need logic and then true value. So this is the first logic and this is the true value. Already the second logic is nothing but this particular comparision, which is G five less cynical 26 the value true is F six. Now we're gonna be copying this again. That's copied this Andi based it here. How to select these six and then it's OK and enter. Now let's try to copy this. Do and oops, we got an error, which is an error. And And if you remember, when I give them off its function, if it's compunction, cannot find any true value, it will do any better. So in this particular scenario, all this condition which is CCX let more than J five Jennifer less than equal to 36 and Jeff Ellis included Sick This three condition have failed. So if function couldn't find any true value here that cities and it had given at off any so what it is we're too right true function, Which is this a Boolean logic statement And if it is true, we want zero so instead off and they will get a zero. So all this condition have failed and we need something true. So we have to write true like this, which is a function itself Boolean logic and If true, we want zero or you can get anything you want. But since this is ah, accounting calculation sheet, we want some sort of value and that value should always be zero toward any calculation. Adults regarding a people in a cruel and peppermint am organizations. So let's press enter. Copy this on a copy like this. So our values are saying it's OK. Collision is fine. Logics are finally go down on We're gonna be corporate. It don't so you can see nothing has changed and our guy collisions are correct. And as you can see, writing ifs function like this makes it brought easier to understand this entire ah logic off this particular formula. So if this condition is true, give zero If this condition is true, give this particularly up six. If this condition intrigue you zero this particular value if it is not any of this not true . Yeah, all right. Enough force value of true By using her true Boolean logic this particular way, you can ah, right. If function instead over next relief fairly question you got in this particular its function Please let me know in the Q and a section we're gonna be using this east function again in our exactly prepayment calculation. And that would really them for the frequent because we're used Lord off yesterday if function inside a functional function. So it is a big yesterday formula. So we're gonna be using its function in shock, that particular, huge formula and that also will be using existing formal. I just read it with the help off its function. So I'll see in that video and thank you very much for watching. 23. 23. Prepaid Expenses Closing Balance Summary Tab (Formula Based Summary): I will be preparing a prepayment somebody for the closing balances from this unusual prepare expenses sheets. So we want to create a somebody sheet within usual paper expenses with the closing well in this year and easy as me truly ready to use, we look or function along with the name Regis, indirect function and match function. Using this combination of function along with a look up will give us a dynamic world. Jenoff prepayment Somebody ship. So in the future, Any time you add a new prepared expert nature like this, you just have toe rename it, create a new name Ridge and just tracked the formula. And it should be fine. So first time would be to prepare name, ranges, table array. So if you know we look or function, it requires three arguments, which is, well, look up requires look overvalued Table area column and next and condition. True or false. So we'll be looking for false because they won't exit match. Look overly would be there gl in which will be pulling it from here. This paper intentions, I'll show you in the moment table area would be tricky, as we want to create a dynamic table area and we want this. We look a formula toe, get the table that is dynamically based on the name and to achieve that here, to create different name ranges and using indirect function can ward those neighboring years into references. So let's start that exercise. So we want to pull this particular data on this route 70 right And and we want to keep the names off individual prepayment according to the chief name. So let's copy these names here. So health insurance, property, angels, Visa, Ryan, toes and subscriptions and we look or function requires that your local Mel you should be in the first column in the tribal area. So currently it is closing balance here. So we have to change this to our prepayment names. Which is exactly are this so appropriate? But health insurance. But we were just leaving the name from it to so you can see like all the sheets, Goto Selby, 70 and link it to a two and press center. Now the next time would be toe create a table area, and to do that, we're toe creator name rain. So we're looking for this particular balance only so we didn't need not to give Enter table of selecting card table for naming you just total select this particular role, which is range be 72 me and seventies. So and that will be giving a name. So let's cure name. He paid help? No, as explained before in our formula, that was the name rate has some rules and you cannot you just any name. It has to be a certain name Iranian that cannot have ah reserve or from Axel. So basic rule is that here to keep a name with under school, you can argue space you can use underscore law docked but currently I'm just using a straight name. So currently have given name people health and how to press enter and now people has been You want a name to this range. Similarly, I will be giving names to all other sheets now arguing different names through all the sheets. As you can see with the range me 72 be in 70 in all the sheets. So let's copy those names here because they want to convert those names. Andrew reference with help off indirect functions. So we need to copy exerts this names really true unusual prepayment sheets. So, for paper held will be coping This name prepared, held against repair health insurance. Similarly prepared property. Go back to summary. Hey, paired rant against three perent owes prepaid visa against prepaid visa and repaired sub. Get a paper subscription. You cannot lie in them now. The time to write the formula. So I will be writing. We look up now look over lose this as I discussed and table area. As I explained before, we want dynamic temporary. So we're to use indirect function and we'll be selecting this particular Selby six because you want to convert this name range into a reference No for an enemy column Index will be using match function. So match and I look overly would be chance 70 and look up there would be from here from column B because you want to give this particular reference in sync with her, they will at a range which also start from column B. So we'll be giving be five. Still be in five and will be freezing that because that would be common among all the sheets and we won't accept match will complete the match function and we won't again except match. What are we? Look up, but I center and you guard the correct balance. So for 167 years, you go to people, have the insurance and it is correct. Next, fix the religion references here. So we warn C six as a column b six As a column. We won't rule 45 rest. It's fine Soil Press Center. This way we can drag the formula. So let's true somewhere new checks for the prepayment balances which have been pulled by this formula. So let's through random checks. So let's take three babies are in February, which would be triple 74 So we'll go to a prepaid Reza. We'll go to February areas triple 74 You can check one more like in June for ah, rental should be 65 to 3. So we'll go to Randall's will go to June. It is 65 20 Formalize, working fine. And so you can pull that endemically now. As I mentioned before, subordinate usually are additional people gs and people expenses. If you had one more tap, you just had to come here. Of course, you create a name range for the closing balance and accordingly updated, uh, name of the people in jail and name one year and dragged the formula down, and it should work just fine. So this is how you prepare a simple peppermints on many. We totally help of formula. Without the help off, any would be according or our radio people table. Now, in next lecture, I'll be showing you another way to create peppermint somebody, which is with the help off our query and people table, and I'll be using the same sheet and will be deleting this sheet and also removing the name ranges in that particular sheet to start with. So I'll be saving this year as a copy and work on that. And we re creating a dynamic people, men, somebody with people table, which is completely dynamic. If you are new, she types. In future, it will obey the prepayment gs without any. These name ranges additional columns, and it will be part of your tables. You can people the way you want, and in another was your, which is actually be a budget, which will be the most Radwan's word. You know, people man model aggregating a different way to create this somebody which will be discussing when we go to that particular section. So thank you very much for watching you fair. Any questions? Please leave them in the Q and a section Willow, Thank you very much. 24. 24. Protecting Formulas Cells and Fields in the Model: Let's talk about some protection in our template. So as you know, Selby and sell E. R for the daytime books. The rest of the cells are formula that cities, and they're being colored separately. So your loca is for import cells, and this says we want to keep always 34 the time put so rest of the cells we want to lock it in order to protect the integrity of the model and make sure that nobody accidentally deletes any formal because it will break our model entirely. So we are in this prepaid health insurance sheet, and we're gonna be selecting from B six to be 65 so that I'll be selecting and always selecting again column E with the same range I've been selecting be three as well. And for this particular template and for all the sheets, of course, were to select this particular cell very being putting manually. The opening prepared balance for this particular peppermint, for example, had insurance. Now you're to right, click homer self and make them a lot. Right. After doing that, go to the sheet that selected right click protect sheet and you allowing select lock certain select unlocks it apart from there, not allowing anything. So we'll put a password. A simple buster, Toby pp. For paper men, we had to put it again. And okay, Now she is protected except this particular cells that can add regard. You can select other cells where you cannot do anything. It's ensure that nobody accidentally deletes any formulas and break the model. Similarly, you can revolve the sheets. So this source, for example, now you can apply similar technique. Or you can do this before you copied the sheets. Ah, as a multiple prepayment heads. Similarly, you can protect this entire somebody should, because here there is no input. This all formula, right, except this particular column. You can keep it unlocked if you're gonna increase it. So let's select this particular range and make it unlock. And I stopped the says he wanted locked with the same password. Now accept this. All other cells are locked because in this particular model scenario in a simple formula based model, we are importing this. We'll lose manually. Similarly in control panel. You can unlock, district says, because he saw for the daytime put So let's keep it unlocked and protected with same parcel in same Cartabia. Now these two cells are available for input and rest up. This is our locked. So this looks like your model is professionally made and you know that nobody can My new paid your model apart from those cells. So this is how your protection to your model and it will increase the reliance on the date output, which is the most important thing for accountants. There is one more way you can add protection without putting ah, this formulas and she protection, which is by using ever formula. So this forward as we can convert into area and I'll be discussing with you how you can protect it won't be 100% protection, but it will add some level of protection, especially in the cells where we are putting for putting ari formula, which are this is like from column G tail end off our model. So there you can put other formulas and start off simple formulas, and those particular areas will be protected automatically. If somebody tries to enter anything or presses after in that formula range that particular range where the other formalize it won't allowed or any change. So I'll show that particular technique in the later section of this course 25. 25. Exact Date Prepaid Amorisation calculation Intro: as a risk using one off the previous lectures there. Why we're using one tended to start a peppermint. Ideally, should be the date for Reacher made the payment. So, for example, in month of January, I'm sure that there will be lot of prepayments on various states and not don't river. Jonathan, 17. But here I have assumed that all the agreements will be from today for January 2017. The only reason out and is to make this particular form a line or conceptual prepayment. And it's a mortgage is as simple as possible. Never explain you this particular scenario. Let's take this particular row, Row six and copy down. So had been copied on this state January. And you, uh, eight or 15 January 2017 and will be advertising same alone or three months. But here we want exactement tendex. So if this paper man exponential was starting on 15 general 17 it shoot unknown. 15 epa, right. There is a formula to get their date which is eat it, which is quite similar to yeoman function. But here you'll get exit date. So it was like started as just 15 general in 17 and I add team. Once trade, I close the parenthesis. I press enter, I get 15 happen, right? So we got exit 90 days, which is equal to three months now. Here we're supposed to get 15 days and motivation, so it should be half off a $33 here and how for $23 will be in Apple. So how great you there? So told you there were to write a complex nest really function which will be explaining you in this particular shaxing in very next lecture video. For time being, let me just type it manually here. So I'll be applying a 33 day whereby to hear so 417 for 15 days and similarly will be typing for 17 here. So if I some always it should be $2500 which is correct here. So this is more for accurate memorization approach which you can also follow. And as I told before, I'll be explaining the procedure to write the necessary function which will be complex function than this existing function. So you need to have a good knowledge off logics and previous writing off masterly functions . It will have a three necessary function and it will be, ah, function under a function. So I will try my best to explain you this particular function in a simple manner as possible. But if you want to follow this particular time bed where all the paper mints are starting from the mountain, you can follow the same. It will just simplify the things but for more or for accurate approach in. And if you're okay with more complex and necessary functions, you can follow along with me in my next lecture video. Well, I'll be writing Ah, nationally function ritual calculate accurate prepayment amortization from the exact stardate and exit ended dynamically for the formula I'm gonna write will be a dynamic formula which once you write it, you can drag it along throught our calculation portion here and it will work just fine. So let's get on with exercise and I'll see you in my next lecture radio. Thank you very much for watching 26. 26. Formulas update for Exact Prepaid Exps Calculation: Now let's start with exercise over. I think the formula, where will be concluding, exerted Prepare minimum organization That is, from the date when the paper when was paid. So let's do some changes here. First will be adding one more row at the top, which would be a hell Paro. So here is to write a full function called day. So they function is nothing but it got plates. They in that particular moment. So in January there are 31 days, so it is kind of reading that. So that's just corporate this in the end off our model. So for Fabrice, 20 for March is 31. And if it is a leap here, which is 2020 for us, it's crack reading guardian I. So this is a site a function for us in using our exit paper Memorization formula now also were to change this date. Rights rights change few dates here. Instead, off end off the Monday will be picking any date within the month to make sure we have random dates. And we can see the calculation accurately with our new formula. So I'll be giving 15 January well, Fab on 17 march. So then again, in this particular column, C, which was linked to the Montand it from column it will change now. So we're toe. Make sure this is a daytime put column and not a formula columns accordingly or toe oblate your protection. As I explained before now, we had to change his end of the month function so insured off yeoman function will be using either it functions. So eat it. Function is quite similar to end up there a function, but it checklist exact date by adding number of months toe the start date. So let's start to write that. So I'll be writing Is it going to e date? Start date would be See it a number off months close. But in disease, I press enter and I got 15 epidemic. So our prepayment starts on 15 January. This is the date when we paid this paper mint and it turns for three months, so it should be 90 days. So if I add 90 days in 15 gentlemen 17 I'll get 15 apples. You congratulated here by minus ing happening, huh? So I got 90 days as you can see her now. According the Rio to change his monthly charts. True Religion, because we want accurate days calculation from the date it starts and also when it ends. So we want power daily charge instead of monthly chart. So let's do that. So instead, off the riding it by three months. So we'll be replacing this, be it with the eight minus see it, which is nothing but ended minus started by President, I get daily amortization value, which is 27.78 I'll change this heading to daily charge. 27. 27. Formulas Update for Exact Date Amortisation 1: Let's try to write a formula. A more complex version of existing formula to calculate. Accept repayment amortization in within the month. Right? So for Janet should be 15 days in April. Should be for 15 days and famine march should be father and garment. So let's delete this form last first. So be writing if startac is less than them untended and again here to make sure we also get equally. Why? Because this date can be 31st off that particular man. So in this case, it can be treated for generators 17 in actual. So we might have paid actually on 31st January in 17. So we have to make sure we can consider that particular condition as well. So if this is true, what we want, we want 15 days ago. Tradition. So how do we do that? Go to minus 30 volt generators. 17 from the start date here. This is the number of days here, and you're doing multiplied with the daily charge. And if there is not the case, we want to see off. I pressed. Enter. We got 444 before I tracked the formula. How to change the references so far. See it? It's a column. So we're to freeze the column. G isro here to freeze the role column here on calling here, That's cough it and, as you can see here, regard incorrect figures here. Why? Because this formula is not functioning correctly when there is a full month's prepayment to be more so in here, it should have bean 28 days for the month of February 2 daily charge that should have been 774 and accordingly for marketers have in 31 days into daily charge should have been 8 61 to get this particular figures correctly and also in dynamic radio to update our existing father. So how do we do that? So here will be adding a one more condition. So we'll be using an in conjunction with if here so and condition. If you add, you have to have multiple logics and all logics in an condition has to be true to get a true aloof. So let me explain you with an example here, so instead, often leave will be using if and an now you can see multiple logics here. Now, this is our first logic, which is as you know, we're combating the started with them. Intended this logic and I'll be had more. More logic which will help us took I create are one claim a tradition correctly. So I'll become petting this date February minus start dead and whether it is less they know equal to number of days in that one. Right? So that is our second logic. So if these two conditions are true So this is the first logic if we compare in our previous formula in January, this is the new logic where were comparing their different between the two. So fab Trent Dilfer put on 17 minus 15 gentles. Everything, if that is less than or equal to at six with just 28 days. So this is also true. We want this original calculation, which we did. So it was nothing but month and eight minus the start date. Otherwise, you want someone Claremore tradition, which is nothing but a number of days in a month and daily charge and I press control, enter and I got the accurate figures were looking for. So before I dragged this along, let me just freeze the references. So this is at is a role reference. He again this column that is through reference at Israel Office column I press enter. Our corporate is in March and also happen it. 28. 28. Formulas Update for Exact Date Amortisation 2: now ignore the empirical question for a while because we're toe again. Update the formula, which will be explaining your shortly. Now Here, let me explain in more elaborate way with and logic as I mentioned before me and to have all the logics to be true to have a true value here. So this logic logic to a logic on both are true. Then only will get true here. The right will get false. So what was the logic? 1st 1st logical that we're comparing our started with the month tendex so until they are less than or equal to true or not. So let's let's try toe really that out last night. So it's true here, right? Because this date is less than them intended. But what about the second condition? So that's hardly really are that in our class F night? It is fourth. Why? Because the different between February 17 2028 February 17 minus 15. Jane is always more than 28 days, so that is false. And since it is false, his entire condition is false. You are moving to the valley false for this entire a function which is nothing with monthly And what tradition, which is number of raising a month. So in this case is 28 days into daily charge. So I escaped that there's something wrong with the month off Africa creation here. Why? Because we wanted only 15 days a multi judgment of weapons. So the figures should have been around 4 44 dollars on and it is not the case. It is guy creating entire months amortization which is not correct now to get exact calculation in the month top appeal for 15 days here to update our second condition. But just this condition right where we are calculating and Viramontes peppermint, that is 30 days for the apple into daily charge. So instead of the little red, one more a function. So what would that be? So we don't have if and did is there's then or equal toe April, which is the month ended in that particular month, right? What we want. We want the calculation for 15 days in Monta Peppery. How does secular decks were to mine us. And they told the prepayment, which is 15. I prettier in the case and immediately previous Montagny we just thought it was March 2017 . So that will give us exert days for that particular Montana. So for a pretty will be 15 days right and were to multiply it with their daily charge right on. Otherwise, we want a full month calculation if that is not the case, so next as anchor and regard the exact figure which we're looking for. So for 17 should be at amortization in Mantova Apurate. So let's start to reevaluate it. So if I total all this, I should have $2500 which is the case here. So our formula is correct. Now, let me just freeze the references. So here would be the would be our column. She would be role. The s column. I is the role. And after the call on on president, let's try to copy the formula. 29. 29. Formulas Update for Exact Date Amortisation 3: after copying our formula in May June, July August regard minus figures here, which is absolutely incorrect. As you can see, a prepayment should have ended in April, which is 15 April which regard here correctly. But it shouldn't have any amortization further than this 11 0 or near here. So from here on, what it should have been zero. And that's supposed to be our dynamic formula. Now, toe correct. This particular scenario were to read one more a function. We should be the start of this particular main formula. So I would call the center, formalize our main formula, but to get accurate figure an accurate calculation which in this case is should be zero on male were to read one more a function of the start off our main function. Yes, right. If they were to test whether and it which is my intended minus R and of the people mandate is more than the number of days in the month for me. I want zero. Otherwise, I want our existing formula as a false value. I will highlight this condition. The logic. So what is that is 46 days And what is this? A second value, which is 31 days, as you can see here. So a 46 which is the difference between the Monterey did and the end date of the peppermint is more than their normal of raising moment. I want zero. Why? Because that makes sense. We cannot help more prepayment amortization than the maximum number of raising a month. Right? So in this case is 46 days is more than 31 day the month off May. And that's why I condition is true and regard zero, which is what we were looking for. So our press escape and are formalized, working fine, so similar way. We are comparing June with the and it's so the differently trend is toward obviously more than the 30 days. In that case, we want zero, which is the case here. So let's corporate is formula to the entire first rule. So our calculations are correct. In case off Row eight, we got 2500 total modernization spreading across Chan to Apple and in Janet should be for 15 days and in our printed before 15 days or maybe less than that, according to their days in that particular moment. But photos should be amortization, which is 2500 which is case here 30. 30. Formulas Update for Exact Date Amortisation 4: now if I copied this formula in nine, which is immediately below there's something wrong here. What is that? It is this particular cell January here. There shouldn't be any people more digestion. Why? Because they're people men, shots from February. So start only from fragment and it should only for 18 days off. In this case, it should be 16 days. Right? So if I total this adds to l, I get to it fire to just accurately our paper Minimal position. So this formula from here is working fine. But in this case, it should have been zero here, Right? So do abrade a formula one more time. Now to get accurate value here, we should be zero as a people really started from fab And in January that shouldn't be any amortization were to add one more condition. Here are this particular if condition with your hair off our main formula there to add are so are and we're toe airport entities. So they're also multiple logic, just like a friend. And here only difference here that with our condition, if either of the condition is true, will get their true value. So I'll explain you that in a moment. So let's air our second logic, which is the key here to get our accurate zero value here in Mont of January. So what is my second lawyer? So I'll be comparing maybe friends between 31st January 2017 which is the Montand eight minus the start date, which is 12 factors and 17 right, if that is less than zero, correct. And I have toe clothes and new parentis is if that is less than zero. So in this case, if logic one and logic to If I don't know if this is true, I want zero. And if both of them is false, I want our formula. Right. So let's enter that and I got zero. So that is what we're looking for. So I'll explain you this condition in a moment. Let me just give you are references here. So g e is our role here. So go seven sees a column right, and press enter. Now let's start you turd destro condition with our so as I mentioned or will accept number of condition just like defendant. But in this case, or if either of the condition is true will get their true value. So that's tried to test the first condition. I will press F night. It is false. As you know, G seven, which is the portable generator and 17 minus. The intended is not the case. It's not more than G six, right? So that is false here. So if I just try to show you individually, Tu minus 1 32 days is not gated entered, even date, right? So it is false. So what? Our our psychologic to second logically trying to compare their 57 which is, um, untended minus the start date, which is 12 February 2 and 17 is less than zero. So what is this too? So if I just spreads F night finestra so minus trail is always less than zero. So we got true here, right? So I escape again. I go to second logic and I press F night here. You got true. So as I mentioned before, we are If any of the logic is true, we get the true value address. You get false, right? So in this case, we got true, which is zero here I escaped and our formalized complete. Now I can face this formula across the all of the sudden right. And if I corporate is also at my previous room, we make sure that all of formal are consistent. So let me just copy this down. And all our values are accurately populated here. So in Martin Martin's only starts from March April, it starts from a pill, right? So Napa Village starts from me. Why? Because 30 30 day pale is not is the last thing you reach. The people may start, so it moves to the next minute is the correct case here and accordingly are formalized, working just fine. So this is how you prepare at once formula for a function which will calculate accurate prepayment memorization with eggs that started and ended in yard, should you? Right. Let me copy this formula Cotard Formula section. So these are your right accurate paper minimum organization formula, where you have exactly payments started and you want to show exit paper and started anything else will remain same. You just have to change this particular formulas in your rest of the sheets. Right, sir. Job. Did your formula especially the start video to put a random started. No, no, you can keep the Montana it if you want, but I'm sure you love actual started here and seeing this particular formatting to the time put So it will be hard core today, trying to just like just like column B and e this ended a functionary changed from end of them. Want to eat it as explain and charge will be changed from monthly to daily. And, of course, I mean formally also change you to add these days as well, by adding one more row to have accurate decriminalization according to the started and ended rest of the structural remains. Same. Apart from ending this one more row, the structure will remains him, so there would be only one extra going down and you need to change all the sheets accordingly. In this entire model, you can also use same sheet for our power. Kredi, more or less well will be preparing dynamic summary sheets so you can use similar shit. You just have to use this sheet and keep it is the source which I'll be linking in the resources section. So I'll be abating this entire sheet and also used the same sheet to prepare our creative people table peppermint Somebody model and I will replace this shit without previous she. You can download that from the resources section and start using that. Similarly, I realized our bit that ones will be immortal sheet with this particular form less so. This particular structure I'll be using in our Radwan's VB a moral SBL which is also available for you to donor from the resources section. Thank you very much for watching. I'll see you in my next lecture. 31. 31. IFS Function Exact Date Prepayments Amortisation: Hello and welcome to this video. Now you're backing exact date Prepayment calculation model. This is a complete model which we have gone through and calculated. Accept payment. Ah, more tradition amount from the exact date off prepayment. So as you can see you dates are not exited month and there are varying according to the exit people. Man, done so as you can see, this is the complex formal I was talking about. Let me just zoom in. That are 123 EFS treatments and also that are conditional logic or an end. So instead of writing this kind of complex formula, we can use its function, which is new to excel without on 16 only if your office six testified subscription as are done in the previous video where I related existing formula. We are going to do same in this particular case as well, really keeping this audit if conditions, which is logic inside logic and will only be removing this mast relatives and using its function, which is a new to Excel 2016 for office 65. So let's write apes and we're keeping this our condition. This will be our first logic and this would be a second logic. And we don't need this bracket here, but we do need a closing bracket like this. Okay, this is logic One for one in two conditions and true Alu zero. This is logic to dissent, our condition and true. A loose this and luxury mood is if and the spaces were created for easy reading off the formula on this would be our third logical test. And here also So this is your third logical test, but you're moving the bracket. And if the values through this and this is the one where everything will fail, if we don't put true values so we'll be putting true. So let's do that. And I pressed, Enter, enter. And now you can see regard the correct answer. So let's copy this till the end. Now there's a problem here. I think I collisions are manuals. Will goto formulas on about America? As you can see, it's not correctly calculating. So let's copy this town and nothing has changed out. Allah calculation monthly is same that when the formula logics are correct, so this way ensure off complex nest, relieve formula and function, you can use its function. And you can make your, ah calculation readability very easy and formula is easily readable to anybody and understand the logic with dysfunction. Thank you very much for watching. So I'll be doing 3 to 4 more bonus video in addition to these three bonus videos for its function. Just we did I'll be seeing in my next video. 32. 32. Data Validation Controls: in previous section. When we paper, our formula bears moral, where starting it was from them untended better protected all the cells which had formula. So let me open the model. So this was our formula based simple model where you're starting, creating prepayment and more transition from them on TENDEX. But the area protected all this serves except column B and E re trader input columns. So the protection was working finding this scenario. But now we have changed the imports in column C which is not the case you write columns he was accepting which are the started from them untended from Calame. But here the started is now input And if somebody accidentally puts any other daito, try to enter a wrong radio. Our calculation won't be correct. I want to make sure that we protect this particular cell from the incorrect input. One way to do that is to use data validation with the tabulation were to ensure that any date and turn in this month in gross six, for example, it has to be dead between first to 31st January, similarly, and rose seven. Where there is a February month, any started off repairman input should be from first to granted February. Similarly, from March, it should be forced to 31st march if user enters any other did backs. I don't know by mistake. It shouldn't accept any input. So we go to data data validation and in allow. We have to select date. And here you are to select date between. So where does Iraq did between first off this month till end of this month? So any did in between this, we should allow us input how to do their dynamically for you to write off formula for that . And what would be that formula That we show that formula here in this space here, for example. And then we'll copy that formula another time in relation. So I'll be using day function as there used here to another days off the month. So a six is our technical January. This is the data off data for January 2017 minus the up same date. So what are the day for this particular date? 31 days plus one. I pressed. Enter. So you have to close the parentis is here and remove this fantasy and press enter how we got general and 17 I have to format this display exit date. I got first gentleman 17. Similarly, if you drag this formula down, we'll get first fair 1st march. Now let's see how this formula is working. So what is a cigarette I mentioned before is nothing but are waiting Column It is the Montand it. So all these dates Aardman Tendex respectively. So if I press f nine here it is 42 776 is a serial number a press escape. From that, we are minus ing What? We're minus ng 31 which is the day in this particular month. So if I removed his one for a moment which did I'm getting I'm getting immediately previous Montand eight, which is start of us December to them 16 and we're adding I want to wait so we get for stop that particular month. So let's copy dysfunction select C six goto data, data validation, etc. Like date and from the start date. We should be this particular formula and and it should be same month, which is my intended right, because this is, um, untended. Already we have given so we can use that particular input as a ended here in district of adoration, I press OK, now let's test this particular declaration. Suppose I enter 31st March 2017. I get this kind off validation pop up and it won't accept data unless you put date as you're mentioning the condition in detail allegations, it has to be dead between first without ever January in this cell C six. You know, I press retry. You don't accept the date. Let's copy this town now. You can modify the messages here. If I go back on, you can put in put my six year which improper, better input. Help comment, which are sure in a moment and added alert. You can call be same former. That's okay. No everywhere. This would be a They appear, which will help user to enter their exit date so it says input or leader date as in column a six. So if I call pit is regulation one more time. So everywhere. Whenever use a try strain put in this column C will have this kind of comment. So let's does this validation one more times. If I go to September, try to enter October date. I get this kind of head. And now we got our comment, which put it there. Time put other important date only for the month as in column a retry I do not accept unless you put the character which is for the month of September. So this is the way you can add one more protection to your input cells here. Also, you can apply it a relation to accept only numbers. Here, here also, you do same. You can apply the toleration. So for example, here we already applied date. If I enter facts here, it will not accept. It has to be any day between this particular month. Here also can make sure that your planet evaluation. So let's apply the same. So that a validation We won't hold number, right? Because months cannot be decimals. So between what we can put months like maximum one number off months. Why peppermint can last. So, for example, 65 years and I press Okay. Now, if I full here and copy it down the little 65 so be 65. Now you're ensuring that the only enforce our whole numbers. If I put 3.5, he could accept here also can modify the message for next. But if I bought 61 also, it won't accept for yeah, pretty sure that our prepare mint will not be more than 60 months. So according you can adjust your range so you can put 100. Also, if you want to put it, we can apply similar kind off validation that their relation in column e where are prepayment amount? Individuals are crucial column. We'll go to data data validation. And we had to select a symbol here like this. You can put minimum number like the zero and maximum number of your paper mint. So here amount can be anything. So I can put zero because cannot remind us so Maximum and I will put is around Ah, suppose $50,000. I'm sure that my payment will not be more than this. If you're more people when that And what do you use this particular issue? Do you can apply more maximum limit as well here. So purport. Okay, now if I press tax here, it would not accept. If I put miners under fire, it will not accept it will accept any in four day similar anything So let's cop. It is down the euro 65. Oh, sad assist, if I should think and we are done so you're now applied completely. Tyrrell Aeration. Now, Formula says you don't have to bother because it's already been protected with sheet protection. One more thing. Your toe. They care that since our model, it's like civilised fathers for skull years Wisconsin. So if you know that if you go here and if you change your fiscal year from Jan. To any other fiscal starting month, support apple, your mother will not make sense here, especially the tabulation, is not like a real time formula. It doesn't change your prison show immediately. Address right? So, for example, it's starting. Monday is April 17 year here. You're showing January 17 as US start the payment date, which is incorrect. So if I press F two and try toe enter here, it will not work. But there's a large show, other or message, anything. Radically. Since we have changes for the Globe month here, there's inherent limitation with dirty tabulation, so it didn't show us any other here because we change our formula bridges giving regulation . So the declaration is failing here. As you can see, it is still accepting the values. Why? Because we change this particular dates here, right? We have changed the range and reference off the date that our nation is mainly working as active control. It is not like a formula which will immediately change. So this limitation with little validation, this kind of situation you to ensure their do input all your debts again. So when it was usual, starts using with a different Fisk earlier, there was already input here to make sure you Leo to remove this and tired. It's like you're select this column from from C $65. 64 delete and anterior dates again, which either correct dates and then the relegation will work. This is how you can protect your sheet and formulas and also imports in in the model with the help of little validation. Fairly question regarding the same, Please let me know in the Q and A section. Well, I'll be happy to answer them. Thank you very much for watching. I'll see in my next lecture 33. 33. Bonus Prepayment Model Opening Balances1: hello and worked on this bonus fracture. In this lecture, I'll be showing your different version of pre payment expense. Moral. So this particular version is different in many ways. But most importantly, it has now opening balance. Now what I mean by that? So this is the Pepperman expense model for the BB accord. The term order I'm using. But this particular concept and the scenario I'm trying to explain applies to all the virgins as well. I will be uploading this version of opening balance was, you know, people main model in the resources section of the course. So don't worry, you will have this copy as well. So, for example, in this particular scenario, I'm using the Excel baby a virgin, and I would call it opening balance, as you can see here. So there was one column here called months which have the moon? No, because it doesn't sound that much purpose. We always need ah daily charge which is derived from this to date. Threats started and ended. So when you're using this particular version of moral, you need to have a started and ended for your P payments, which is usually end up. Is there. You cannot be anything without started and ended, right? So you want to feel by any subscription, For example, this is a health insurance, so your hat insurance policy will have a started ending. So basically, report that year an amount of repayment and everything else will just work off. Now, in this particular scenario, suppose you are employed in a new company or your auditor off your client, and you need to order the repayment or you're a counter you just joined in the in the new company and only for existing employees. Just want to implement this model in your existing scenario. Suppose you're gonna apply this model in 2019 and this start usage of this model starts into the 19. And I'm assuming your financial year starts from January month. You can change also to any other month. You warn once you set up your model implementation year, which is this'll. So we are using this model for 2019 since he wanted to live in this model from this particular year. And we're 10 years for this entire moral and are starting one. The financial year starts as a calendar year, so January is our starting year, and accordingly, the dates are calculated here. So if I come back with this paper health insurance, I have new column married, which is called opening balance, and this is date, which I will hide it literal. Normally we deem it with a light color, a similar raid, opening dwellings. We can hide it or we can keep it as long as you're over that this is opening balance column . Now, in this progress scenario, as you said, you want to use this kind off model when you are using this for existing business or you want to implement this model in in prom particular year. So I'm using this particular model from 2019. This particular drop down is for the display view, So we're gonna ties that for now. So let's work on the formulas and I linked to explain you why this particular column is very useful. I suppose this is to the 19 and I had previously other people payments for health insurance in various other years. So 2017 I'm taking example. So I put on 17 18. So there are many payments into the 17 and 2018 and 19 as well. And these other periods for them, as you can see and these are the people in amount. So I have just pasted that in this particular year to the 19. Now what? This particular column girl it can declares, What is the prepaid remaining? As on 4th January 19? As I explained, this model starts from four for the 2019. So for January 19 to 31st December 2 of the 19 this is the start date off this model. So this particular balance, for example, in this scenario where the preparing is starting from fat and ending on four Jen and Total Pepperman was 2960 and the daily charge was around four or something. And now, as on if I come to the 19 I want to see what is opening balance off this peppermints opening bell and should be only four days peppermint, right, because in January 19 we have still four days. Paper meant Toby return off and that would be $17 an urgency. It is return off in this particular month, so this formula will remain same. There is no change here. The one we wrote before. So just ignore that for this particular example as you know, already how to write it and wind it from our previous videos of lectures. So for this particular pre payment, there was $17 left for the 19 so four days. So we're writing it off in the month. Opportunity, Bart, opening balances most crucial, which was not there. In our scenario, we had poor opening balance. If you remember manually in this particular cell here which is set f 1 27 But that is not the case now. It is automatic eligibility here, So opening balance for January 19 would be 6 30,022 which is coming from here, which is total on all these balances. So, for example, this particular people, when which starts in January 17 and ancient for in December, will not have any opening balance when you start this year. So this date this particular column has opening valid only so in January. It does not have any opening balance. Why? Because it has been already turned off in the previous year. So similarly, for this particular preeminent role, number nine, we have 27 praises starting in 27 appeals, 17 and any on 26 separate 19. So we have still three months and 26 days water. People mouth off the 3473 toe good it in off. So we are always prepayment falling into the 19 and that are we are You're going to write them off, which is 553 as you can see, and similarly, you can city someone. So this particular column calculates opening balances to be written off in the year. Just really helpful scenario. When you are writing a company or in auditing any scenario yourself orator, you're tryingto assess some your juniors work or some of the department's work. You can do this with this kind of scenario and this kind of formula. Now let's try to understand the formal of each operator. So first of all, I have added this column right beside daily charge, and here the date is automatically coming from a formula. So is nothing but. And off the month your month function. Just taking G phi, which is there is nothing but prefers. January was a 19 which is the end of the month state for this particular month, Stark So it is coming from control Panel C eight. So this particular dared, which is when you put this number 2 19 year old formulate eclipse end of the month for this 2019 so that it has been linked here, Just linked everywhere. So I'm trying to get one month minus from this so 111 month and put as a minus. So that would be 30 for December to the in 18 which is always equal toe 11 to the 19 in the balance system. So these are balance sheet balances, so this two dates are same in substance, they are saying, But here are put this F five plus one, which is nothing but data for December 2118 plus one. So I get opening balance date +11 19 which is a call to 31 December 2. Other than 18 now it comes though formula, So this formula is again. If function I've used, I could have used the new formulas introducing Offer 65 like ifs and any other formalize which are new to the subscription model prescription version off office and accepted those and Alfred 65 exit to the 19 but are kept things be compatible with the older version of Excel. So this is a straight instrument. As you can see here, there are three apes and I'll try to explain, you know, one by one, or how exactly this is working out automatically. 34. 34. Bonus Prepayment Model Opening Balances2: Now let's do a walk through of this formula and go step by step. How this form leisure time. So in this first scenario, we had no have any repayment balance at all. There's no peppermint and the reason for is that the started off this paper plane waas 15 gentles and 17 and it's ended on 14 December eaters and 18. So there would be no prepayment as on 11 to the other 19. Why? Because it ended in December 18. So we have zero years. Let's understand how formalized deriving this zero balance here. So first thing we're toe evaluate is this particular condition. Let me zoom. Obert Yeah, so far scenario is that we're testing B six, which is the start date against F for what is therefore is the first day of this current year 11 19. You either start 1 10 is that it was janitors in 19 and the first date is for genital 90. Would you started off this current year comparing whether the start of the pre payment is more than this particular date, which is the first day of the year? If that is the case, then I want zero. Why? Because there would be no prepared balance. Because this will fall in this particular year, which is to the 19 right? So that studies on it is zero here. But it was not the case. I want the six. What is the sixth? The paper print amount itself. So that is the first condition we get it. So in this case, let's testes. So I'm going to say, like, this particular condition, the logical test, and I'll press F nine thirties. False. Why? Because January is less than 19 writes this particular date is less than 19. So that is false. And that citizen, we got B six year. So we're gonna skip zero when I get the six. So 3015 is our initial balance our initial result off this particular apps treatment. Okay, so now escape this because I don't want to freeze thes balances like when I write this formula. So let's go back. And this is the first test which is false in this scenario. And now, ever this false. I got this. But now I want one to minus the balance off this same amount. Why? Because they're only going to turn off in 2018. So there should have been the opening bell in the scenario, right? As I explained, So how do we get out of that? So till this particular condition for Steve Straight when regard the six, which is t zero wonderful. And now I want to remind us that that same amount Why? Because I want a zero balance year. That's how technically is formula will work and you'll understand in the next land. When I tried to explain you so on O minus exact Simone, how do you get exact same Ammon? So what is the role of e function? So it's minus if C 6 46 is the ended off. The peppermint, which is 14 December 18 is less. They know equal toe If I What is their fireflies? The a year December does in 18 is the immediate. The previous month. Tending off are starting year. So what is our starting year, which is starting first month ended? This is January to the 19 miners, 11 So this is the immediate previous years. Last day, right? So there's December 2 dozen 18. So if C six is less then, or equal toe f I, which is the case here because 14 December ease less than 34. December 18. If I highlight this particular logical test on a press f nine, it's true. So what? I will get these six. What is the 60 0? Wonderful. So this is what exactly I was looking for and this form will I will end here. The result will be accepted. It is true. So if I who controls the and I pressed enter So basically I minus ing these six minus 36 that's where it's happening here. That's why I get zero. You can quickly look again in the formula evaluation. As you can see here, first condition is false for Steve. So we get the result off false, which is 2015 and then we again test the second condition where we get true and the result off True is also Taser 15 So same amount teacher 15 minutes. 3015 We get zero. So Dachau zero. Okay, that saw the first formal of in work because since this condition matter, we never went to the last if turban. But we will in this second case. So in the second case, we have a starting date off if EP 17 and we are into the 19. So remember that and the ending date is for January 19. So, as I explained we have for this peppermint. We return off in 2019 and that is the balance for Dar this year. So 11 it was a 19. This should be the balance. How do we derive their dynamically with the formula with this particular scenario? So, as I really explain you in this particular scenario, were forced comparing the start date with the immediate start off the year date, which is 11 19. So be seven. He's more than equal to F four. Let's test that. So is 5th 17 is more than ah or you could go for generators in 19. If it is true. No, it's not the true because this date is less than this. So that's why it's false and will get the seventh or answer in this particular scenario that we just copy dysfunction. Yeah, so we get d 17. So let me just highlight again and press after nine fall. So if it is false, we gonna get 2960 as our forces. Right. And from that we're gonna mine us. What? We're gonna mine us with another if statement. So what is the if statement. So you're going to compare if c seven, which is the ended off the people man is less they know equal toe. If I So what is their fight? The year ended right, immediate year and they people's here. So everybody is somebody doing for if for January is less than or equal to f er why? Which is December than 18? Let's start. No, it's fall. So this answer will not be accepted. Were to escape this and come to the last. If street will know what is the last if statement it's a lot of state when it compares B seven. So be what is B seven b is only the start it if that is less than F four. What is therefore is the first day of the year 11 19. So this is true, right? Because this 5th February 17 is less than 112 the 19 right? So if I pressed f nine, it's true. If that is true what we want. We want exert days from this particular date. That is fifth Reptile data for December. Right, Because we want exit days and we want exit people and balance on 31 December 18 are one run through the 19. As I told you, this dates are same in practicality. I mean, for his finals are currently concerned the balance will not change overnight. So f five minus B 17. If I press f nine, I get 604 days and I'm gonna multiply those days with their daily people Religious e seven . So if I press f nine, I get this and the total multiplication would be night 2943 right. And otherwise it will be zero. So if that is not case, we have zero. And now the ultimate balance would be 2960 minus 2943 and that would amount to $17. And that is our opening Peppermint. So these are these formulas return dynamically. And if you total all this for go down, you get 30,062 were top repayment as opening balance to return off in 19 and in the future . So it is not only for 19 eighties for you, one in for future. So this is a fantastic way to our quickly audit before orator or your analyst, you can quickly ordered the people balances off any your client. Or if you're doing any review for any other your divisions, you can do this very quickly On if you're gonna change this, start here to something else. This guy question will change. For example, if it was not the 19 suppose you're 18 and everything else remains saying we could come back to people Health insurance. The scenario would be different. As you can see all this, people men will have opening balance and the same has been linked here and it's going on so previous months. Palace will become next month's opening balance and so on. So this is are you calculate dynamically opening balances in the pre payment scenario. You can use this template, start walking with it, so let me know chernykh ready in this formula or any of the credit you have regarding this template, I will upload this particular template in the resources section and for you to download and have a look. There's a fantastic way to audit and review the people and violence is even though you're going to use this particular workbook for the any future period or any other scenarios are mentioning for auditing or reviewing any other division department as a reviewer. This can be a great help. Thank you so much for watching out. See you in the next video. 35. 35. Power Query and Pivot Table Prepayment Summary Table Intro: we'll come back. Not really. Preparing another was in off the model, which is called Power Query People Table. This is the same version of model we're not doing. Any new calculation in this model will be using the same prepayment sheets as we have worked out in our simple model. But here, really preparing a more dynamic washing off prepayment somebody with closing balances. If you remember in our earlier example, our first mortal simple model, we prepare, prepare mention, really with help off formulas, which is we look up name ranges in direct function and match, and that matter is also working fine. But this particular matter is more foolproof. For other proof on it is more off our dynamic way or preparing prepayment somebody. We'll be using people table to prepare the final summary, but before that will be using Parkway True gender this kind off on people to table. This table is generated based on these individual sheets, as you can see here. So all these sheets are being manipulated and they have been marched with power credit tools to produce such stable. So basically, the source off these people table lease this particular Excel table now to give you a brief introduction of power. Very it's Ah, get and transform tool and it's part off for Microsoft New Business Intelligence tools. It is not really available in Excel 2010 X 113 version. But for Excel 2016 it is part of the package you just available and their data tap and under get and transform toe. But since I'm working with Axl 2010 how to download it as Aryan? As you can see, so have link our detail. Article whatis park ready. And what are the different books you can use to master the power Kredi? It is one of the best data manipulating tool. Once you know more about park ready and how it is easy and especially if you're walking with irritable park ready is must for you to learn when sir familiar with park ready, you can do data manipulation automation off your reports and also you can get data from website different servers from tax files and link them as a source and generate this kind of tables The tears that part Grady automates his entire process. We'll see that inaction while I do this exercise in this particular video. If you're not familiar with Partick ready, Don't bother. This is an option exercise as I mentioned before, if you're curious to know what is part of ready and how it works in this particular scenario, it will be a fun exercise and I'll be going through step by step in tractor. Explain you as much as possible while I work in park. Ready. As you know, this isn't a new somebody cheat which I have prepared with people. Tables here are put this slicer so you can slice unusual years prepayment balances. So currently we're in January are Pieris January to December. So it is 34 January to June 17 deal that it was December 2021 so you can slice accordingly . If you select all of them, you'll find complete data like this Still to those in 21 from 35 January 2070. Now, if you add any other people men sheet suppose I had I'll just copy the last sheet, which is paper subscription to a new sheet. So I'll just go here and create a copy. Now out of sheet is prepared, which is called prepared license. I will just name this. This is important to change the name in a two and also here We have to stare it. I will show you the power credits Magic. I will go to this particular period table. I'll goto data. It's fresh, fresh and you can see people. Business license has come, which is its closing balance. The balance is same as people subscription because we didn't in any figures. We just copied that. She didn't rename it. But the idea here is that you can pull as many sheet you ed and you want to pull in the are prepayment summary sheet like this. Every time there's a new peppermint she to adhere, you just have to go here and refresh twice and your people table well bred it and you're somebody will be updated on Lee Kerry artist that year to make sure that the name of the sheet is same here in a to and also here. So rather, you can just link your name like this everywhere. The only problem with this summary sheet is that if you're different Fisk earlier that in January to December this will not work properly I'll show you. Why. So suppose currently we're having January is a start Want. And if I change it to Apple and I say the sheet I go to people table somebody, I'll refresh and refresh. Now you see it starting from a place everything was just fine but is ending in December. Why? Because people table works from generally somewhere the year, so there is no custom format available in paper table to display your data. So if I go to 18 a crucial Jan. 2 December, Rather it should be showing apparent 17 to March 2018 and also there is one more year added . If I click on 2022 it will show Jan. Feb in March, it should have bean 2021 2022. So that is chosen 21 April 2 200 to March. So this is the problem with this particular somebody forever the friendlier than gentle December. Not to work on this year to prepare a hybrid table. If you die every table and with the help of people table, we will be able tow, keep our custom date and pull it out accordingly. That particular functionality and technique. I'll show you when we do this particle exercise in our next video. Thank you very much for watching. And don't forget to read the entire article and the references for the power credit. Thank you very much. For what? 36. 36. Power Query and Pivot Table Summary Deep Dive (Part 1): as I mentioned before. Power Kredi is not available. Facts sort of those in 10 x 113 as part of the package. So you're to download this park ready as a adding It's a free area is only available from Microsoft website. I have provided links to download this power query. Add in for Accent wasn turning 613 in the previous. Like when I'm explaining what this part Grady and giving references for acceptable in 16 is our mansion. It is part off their data ribbon in the ghetto and transform section, but since very acceptable isn't 10 I'll be having up separate tab, as you can see here. So to start our exercise was, let's create additional sheet. I would just name it Toe pp under school table. This is what it will be preparing our table from which will be preparing up. They were table somebody Now in this shit will goto power credit will go to trial thrown for dinner. This is important your you select fuller not radical Xel file. So basically I'm copying the fuller part with this particular Excel workbook with just call pre payment model walked through by lesbian saved. So we're to find the location. Where do copy here in this fall. Apart here to click. Ok, how to click at it now? This is the party created window. This other steps, whatever we do hear any manipulation, it will be recorded here. So basically it will be a sequential macro. Now we hope poor data from Fullers or whatever is there in the forward will be populated here. Different, different files. So of course, they're the main file from where we are working. But there is also one more fire ridges like this isn't nothing, but I mean file which we nose open when you working with the file itself. So currently we are in this for naturalised follows opens. We can filter that out, But looking this arrow and un selecting now our main data lies in This particular column called Content is binary. So we don't need other columns. You're too right. League and remove other column. Now you see, other steps are being recorded. Whatever you do will be recorded here as a sequential steps. And you can also call it sequential macro. Now it's in the source of this particular content is Excel file and concurrently, Power Query does not support pulling data automatically from Axel if eyes, which is quite strange. If it could have been a CSE file, you could have been just ableto expanding. But since it's an Excel table, were to do a work around how to do that. Here to go. TOE AD COLUMN Erica Stone COLUMN and you do Writer for Villa, which is called Excel, Don't work. The formulas in power crazy are case sensitive, so he should be capitalized and w should make applies in what book now will be selected. This content will be closing the parentheses, and since there's no syntax error, we are fine with the formula. You have to click OK, and now you can see there is one table has been prepared and you can see data here earlier when we click this binary right beside the by native, you see, on leader, she did. Salvage is not useful, but this seems useful now. We don't need this content columns. We can remove it now you can see this double headed arrow. We can click it and expanded later. We don't want to use traditional columns as a perfect so we just will just untech that and click OK, now you can see all the details in the file. All the different different sheets which are there in the file has been listed here. The most important column married is Data column where you can see table table. So if I just click writes beside this table world you seen usual table. So if I see prepared with that, you can see prepared with attitudes on we don't need these three columns. We can remove that now. We don't need all these sheets. We don't need data from all the sheets, right? We only need data from prepaid prepared, prepared, prepared. So wherever the water pre Brady's, they're prepared in space. We want those sheets data while other shoots control panel evaluation and calendar. We don't need that so we can filter them out. So to do that you do right. Click here. Go Tax filter contains prepared and space click OK, how you can see only prepayment sheets are edible, so that means whenever you create a new sheet here to make sure you our prefix off prepaid and space and gender in usual aim of your prepayment GL therapy payment, Expense name. Now, we don't need these particular columns. We can remove it. I will see again this double headed arrow. Right? So we're toe clicthat. We're Do click Lord more. And of course, we don't want to take this click, OK? 37. 37. Power Query and Pivot Table Summary Deep Dive (Part 2): So this is pretty payment property. The first pre payment Waas prepared health insurance so they are stacked right. And now the runoff Collins, which we don't need for us somebody table. So this period months, we need this true column temporarily. But other columns we don't need so we don't need start and people in Monument Lee Child we can right click. Select them brightly, can remove columns. These columns we need before this all around prepayment till December to those in 21 when you're to remove those because we don't need. So there's not Rose we don't need in this column, right? So basically going to remove all this except pro the name of company prepared health insurance as a heading because you'll be getting there heading anywhere in this particular column. So if I click your and show you that we have these names here, so it will be using those names already because you have linked them in that particular last straw against closing balance. So let's remove is no little value from this column. Seven. In our first month off, our paperwork calculation is simply clicking down Arrow and Lord more un select null and click OK, the roads are gone, which we don't need it. We can promote this for stress ahead as we go to transform and use for stress. Heather Now your toe again Do one more for you to go to this Pasquale. A month years and you're un select all and we only want closing balance. Click. OK, now if you remember, we're closing Bell is in the first particular column and then we gave a name off unusual prepayment sheets which you can also link from a two which was the heading up in neutral sheet. Right now we don't need this column as well and we can rename this column too. Be big Teoh name now that Carlos your toe on pure them. So you have to click this particular first color right click on people that their columns you see, all the data has been on pivoted there've been struck one below another. This is the Dates column, so we'll be just calling it a month. And this one is amount. And in power query, you can change their data types based on their values. So this is the tax. So we just call it tax This is data again. Select date. And this is a day similar Monster can select a similar number. We can also extract different different attributes from this date. So I select column here first months I goto ad column I go to date and I want to see your So I'll just click here And a new column is a record year. So whatever the year is there in this date, it is poorly here. So it for 19 2030 wasn't made to other 19 years with the 90. Similarly, I want to see month name. So are you. Go to that column again. Months Goto ad column did photo month and name of the month. So whatever month is there in this data it has been extracted like this in a separate column and the data dive is also correct. This is tax. This is is not our base table number. We can convert it to a whole number, but it doesn't matter anyway. There won't be any calculation there. You can pull this column our front Now we can reorder them like this and we can also rename the credit to be payment. This will be name off our day Well as well 38. 38. Power Query and Pivot Table Summary Deep Dive (Part 3): now the task that is ready. Let's load them into excel Table. So to do that here to go to home clothes and Lord. Now the table has been loaded to access sheet in Excel Table format. As you can see here now, Miss Dundee's we can prepare people table. So to do that, you can do a shortcut like this year ago. Table tools, Kodo design. Summarize it. They were table and I want in a new sheet, so just click OK and you shoot is at it. We can close this and we can t neighbor to From Murray PT Hope your table. Now that's paying the detained. So you want prepayment GL Women draw level MUNCIE COLUMN On a mountain when loose now, everywhere table is beginning to take a ship as a people bring somebody that's quickly former from feel So, first of all, our former the day to be a short diversion like this month and year. Now, we don't need this grand Total column doesn't give any meaningful information so we can remove that now. Let's form at the number Sotu rightly go to number. Former Could a number we wanted a similar to zero. Now we can remove the grid lines. And also let's add some more space about the table, just in case we need it. Let's hired this first role and also change this to peep edgy, and we can also give a design off a different look. You can get a company name like this. Now let's add a slicers without were to click in pure table goto options in such slicer will be selecting year. Okay, this is a vertical slice. Everyone call longer. We learned one more regard will be expanding it to one more year later. Now you can see you can slice your data like this. Let's add one more prepayment yell to show you how it will be abraded in our prepayments. And I have brought this tooth. The sheets are front. Besides control panel, the table and also the they were table somebody and let's go to the last sheet and copied who are copy clear to copy before relegation. Okay, that's cute. Them expert Rezac piece. Right? Then we need to have same name here B 70 and a two. Save it. This is important for part of credit to update unifying the same sheet. Now let's try refreshing this table. Goto Day tired of fresh and it's been operated here since we call paid prepared subscription, the values are saying, but you can see the seventh prepayment expense has been a little six earlier. If you remember now, there's seven so accordingly for air, more paper and expenses as a different sheet. And keep the naming convention like this all similar in a two and B 70. You shouldn't have any problem at all. Refreshing the sheet all the time you want for different years. So this is how you prepared are dynamic. People have been closing balance. Somebody should, with the help of power credit and people tip Now, as I mentioned before, that is gonna care. We are to this. You cannot have a custom fiscal year in this. So if I change it to June starting instead of January Syria, Syria, Syria, Let's make sure and refresh. Refresh. You can see you, John to thousands of wanting. It is June to December. What it's supposed to be June 17 to May 31st May 2018. On and off the yard. It should be 2 30 was made 2022 which is fine here. If I see like the entire slicer, that is entire period. It's showing correctly, but for individual years it is incorrect. So it starts in June and ends in December. The reason for this is that people table as a limitation. You cannot work with the costume fiscal year off financially or other than gentle December . So will always consider a year from January. Somewhere is a calendar year. Now, to work on this particle invitation, we can prepare a hybrid table we truly prepared based on this period table only. But I'll show you some techniques there and also use up get the word function, which is unique to pivot tables only. So let's try to do that. 39. 39. Power Query and Pivot Table Summary Deep Dive (Part 4): First of all, let me select everything and remove the slicer. We need to have this pure table 100% selected for all the period. Whatever. There are mortal periods, which is June 17 to May 20 to 31st. May 22. The columns for this particular period. Should we have a level all the time? You shouldn't be What, this or slice it? Awful correct. Otherwise, this particular technique may not work. Let's move up your table for to move that. Or you can also move this to another sheet. Where are we keeping this spirit table? In this existing shoot, I will just smooth it down So we'll go toe options More table on that school, deliberate down and go door 0 90 and click. OK, and again hired it. Now I like the formatting off this particular people table. So also liked everything Control. See, I would come up here faced like this and since it has pestered values were not bother with anything else. Now we did this just to get the format off this table. You can remove it because we're using a formula to pull all this. Now here we are, apply Same technique as we did with our simple model. So we're to go toe our control panel and take linked like this from here, and we'll be using and off the month function. What's up? Secret month. So we have our period, which is in sync with our individual paper mentioned with as well, auto pull peppermint jail name dynamically. We can link the same to our people table with three more earlier. Right? So we can click here like this that we just give some space. Suppose we gonna be having at least 30 prepayment expenses, so we'll keep some before space. So I have linked to relocate what they were like Can copy and paste now, since we only have a seven prepayment deal currently disappearing like this correctly But after that it is linking the ground total and blank rows as well, and it is fine. We can link up to 30 roar in the paper table itself, assuming will be expanded to 30 ppm in jail. But whenever there is nothing, it will appear like grand total injuries. We don't want to see that we all do, right? If function to work on this particular issue off grand, total and zeros. So let's do that. We'll goto sell you a six Where a 113 which is from people tables. Foster is already linked so we can modify the same. So we're gonna right If or the first test is if even 13 which is Ah cell range from our people table His grand total Yeah, a 113 Is he called to No or nothing closed. A logical test What we want. If either of this condition is true, Toby were using or function with If so, if either of them is true We want blank cell flank, will you? Otherwise we want 113 France center and copy down You can see In row 13 there was Grand total. It is gone now you can copy this formula down, General Type ET. Well, let me pull this value prepaid business license from up your table. So you go to appear table and link. You see, it's not a simple link like is equal to sell rain It has given get people function which is by default. If you pull anything from people table values, it generates its function. You can off course, disable it from paper table options. But we're gonna use destroyed one patient. I'll show you how. Here. What are the arguments off the? This kept people from here. So they feel is amount. Of course. Be what table feel name is prepayment people jail And I, Tommy's prepayment businesspeople business license. Right, So we can make it dynamic. Currently, it's hard coded as a Texas you can see here, but we want to give us Albanian stirred up there so we can believe it. And you, this particular cell range and make the column absolute. And now months it's a feel. But did we can make a dynamic how we can delete Dieter a link this date and make the throw absolute press enter and regard the same value. Now you can drag the formula down like this and also you can drag it horizontally like this . Yes. And uh, we are done. Oh, your diamond future affair New peppermint Jill. It will be pulled from this people table. So your also your today time to refresh it and it will be automatically poor Now we should copy this formula down. Pilar Maximum rose Which there was you but 11 at her like this reference centre. Why? Because there is no data currently available. This is for our future proofing. Whenever we have additional prepayment expense deals and sheets, we can ignore this era by using, if other functions. So let's wrap this. Get people function with if error. So you're fatter. We want nothing that sex you can call Peters and Green and it's done now you won't see any difference centers on. We can hide it. Can you talk? Um, no. You to make sure that we're pulling still raw target. Now let's add one more Prepare mint, explain sheet or preparing expense. Besides this, these expense So Mawr copy here to copy. No, we can name this prepared vehicle insurance. And there that's Q. Same name in B 70 and 82. Serie A couple of times. Justin gifts. Now go to our paper men. Somebody goto date. I refresh you fresh. I didn't see anything changing here. Why? Because this is not a paper table. These are hybrid table. And if you remember, we had a maximum limit and we have learned a blank rows before. Right for your toe on highlights or select this on height and you see a new prepared vehicle insurance paper, man sheet and G. L has been addict with the same battle because we off course cooperated from the previous year. And now again, hide balance, Rose. It has been pulled from our what table which is here, as you can see. I suppose if I changes to a thrill again, save it, come back here. As you can see, it is already you an apparatus because this is a formula ling. But that's full figures as well. You can see it has come now. So this is how you prepare Ah, hybrid table like this, where you can use people table functionality and also use formulas. As I mentioned before, people table cannot over great custom of fiscal years like starting from April June. So in that case, we can still use pure table what you can do into hybrid table like this, where you will be using formula and also pulling information from the pure table. As I showed you power created a very powerful to land. If you're using people table all the time, I highly recommend you go through power credit courses. You can also read power credit books, which are linked in my previous section. If there any questions, please let me know in Acuna section below and I'll be happy to answer that. So I'll see in the next lecture. Thank you for watching. 40. 40. Using Array Formulas to Add Formula Protection: the only issue with power Korean people table somebody model is that power critic cannot accept protected sheets since the source so far in usually later on this paper sheets, As you can see here, they all are protected, right? We did a protection in our previous example where we discussed about simple formula bears model ended the N'Dri added protection to all the formula says and kept open only futures like this column e column B and this particular Selby tree. But if your protection in your sheet as a source data power query cannot work with it, I do not accept any data from that particular source. In this particular case were to apply some form of protection that we cannot leave our template Oprah for Nick and off for other. So any accidental formula relation one way around would be to use every formula instead of simple formula. So currently, we're having a simple formula, and we can add aren formula, especially for this pre payment monthly calculation, which is the key formula structure in the entire table, the entire model. We'll try to update our existing formula inara function, and I'll be copping this at a function to entire modern itself. And once it is corporate, you cannot delete any unusual selloff formula you cannot even edited. So let's start with that exercise, so we'll be starting with this cell G six. Where are orginal? Formalize. Now we are to update this formula with Sarah function. So what we're do we're to select a raise instructing the yourself. So in this case, there's says C six selected, but we want to select entire any. So what is the area for seasick? It is. See six to see 65 And here the cell. Vince's extra value to make it absolute, since we'll be dragging this formula down. Similarly, G five is this particular self selection. We want area offic. So what is the area we had to select and tyro? So where to go? Tell the end. They'll column. Bien will be in five and here as well. We'll be making this particular BNL Saleh's absolute reference with double dollar sign, right? Similarly, G fire again were to make it and there s election, so I'll be just typing me and five and let's make it a salute. Well, you were right and column 36 again is similar to what we selected receive six. So it will be just typing the 65 and make that our friends absolute. Similarly, AP six is our monthly charge that also selected 65 AB 65. Let's make it absolute usually V press enter after this kind of formula, writing were forever formula to work properly or to press control, shift and enter. So I have selected from cell G six Still be in 65 right? No, the formula is reload before the other function. Here it is available here, right? We want to apply this formula everywhere. So to do that, we go to press control shift, enter and now you can see the Cali backers are better here. That means this is an error function. You cannot enter this particular curly brackets manually. It won't work. Excellent is automatically And it tells us that this is an area formula. How somebody goes and try to delete this formula, it won't be deleted. Why? Because it's part of finale. But I suppose you can add it with after key. But you cannot Or I did our religious formula straight away or liver together off this is to select and cars feel like this and press delete this. Reconnect some form of security to your moral structure, especially the key formulas, each other peppermint amortization. This will work with the power equity without any problem, because this is still not protection, which we usually apply with shit protection. I hope you have enjoyed this particular lecture. You fear any questions? Please let me know in Q and a section of this lecture. Thank you. Element for watching in next lecture will be going through our mind wants to be more than which is more for program. So I'll see you in the next lecture. Thank you very much for watching. 41. 41. Bonus Allocation Intro 1: Hello and welcome. Drew this bonus lecture in this bonus lecture, I'll be showing you how to create this kind off a location, table or location, People, Table for your people. Expert nature division rise or cost centre ways the way you want. Now, if you remember in Section Shaman, we had created this kind off power query People table based somebody for all the people. Explanations you can see here. So we had created this kind off table and he had created this. We were table somebody. He just completely automatic and dynamic. Now what if you want to locate your people explanation, for example, people health insurance to serious call center in a certain location issue like this. So if I remove it division A location. As you can see, this was our earlier solution, as you can see. And this is the somebody we had prepared in section seven of the course. Now, if I bring individual location, your people table exports and it is complete enemy now, how to create this kind of a location? So to do that here to create our location sheet like this are clear location, table and your to prepare table like this Now I created this allocation ratio just randomly and how fired the region. So it's up to you. What is your actual location? You can create accordingly. Now, the key is that this particular tax description is our matching column. So we are to Mars the tables. Basically, we look up inside power query and ensure that is our same How? Just copy and paste eight. Let me undo this. And I just copy and pasted this tax. It is the sheet name itself Andi based right here when lose. And I located in 100% ratio to all this fire division. Now, clearly this check as well, where you can see if your type like suppose you 5 15 here. So this total has to be hundreds or to reduce six from somewhere. So let's revisit from Division B. Now it's 100. So ensure that this is 100% always. Now let's see how to clear this kind off location automatically, and I'll see in the next lecture for that 42. 42. Bonus Allocation Walk 2: start building this kind off a location. So to do that will be creating a location. We sure table. Just besides, thesis PP table it is our main table, so I rightly can insert a sheet on this more it here, let's name it. Location the shoe for a location. People rather. Now let's keep column one as a pre fade exponential time on. That's I would do you okay. And as I totally before we just had to copy from the somebody. So this is gonna be our He called him for comforting and having a real look up in power credit, which is called March on. Let's create table out of this so I can press control t like that on LaBella's. Haider's like this. Now let's add some percentages. I'm gonna 25%. Let's go pee the former on Let's create a check here so it has to be a percentage is incorrect. So make it 65 25. So this is 100. This is fine. And let's copy down and make a lady eternal for so make it Dan on making friends D 45 like that. So this where were located? 100% for each people exponential like this and make sure that it is 100 and you can do whatever presented you want as you like, based on your allocation preference. Now let's go to data and given name. So better we go toe table design and you location table has a name. And now goto data on and create a credit got off this. So we're creating credit from this particular source, which is this particular sheet and in that also we are selecting the source, this particular table So this one we considered so don't worry about it on the this particular table we pulled in you the power credit. So let's create from tables Last range and Parker years pulled it correctly. Now we just started change the type. So I want this to be a percentage. So replace current. Yes, and now it's percentage. Now we have this table ready and it is linked to other sheet, which is a location, and you can always change it and come here and refreshing, which is we don't automatically in the back and or parkway. Now you go to the original table and were to create a march. So you're to go toe mulch Grady's and I want to march in this particular quit itself You can clear to new on as well. But I'm gonna create it with the existing credit, which is people somebody table, and I'll click much credits. Now, this is like a real look up. So this is the main table. This is the table, Papert. Somebody and I want to select a location table. And I want tohave a match on this too. So we have 100% matching because their taxes matching Andrea click. OK, so now it has created a new table like this. So we have to expand it by clicking this arrow and we don't want or Internal column as a prefix. And we don't want people exponentially. Just only one daytime column. It e like this. So we got the location like this. And now we have toe. Since this is selected already, or you can just select like this right click and on people columns. So now we got the division. So we just name it right now. The region and the value Rio to multiply with the original amount so it can select this column years a Motor Press control and Select values and Goto ad column and go to standard and multiply. This has multiplied This column your started among with the Valley, which is the location issue, and we got the new value. Now we don't want this to call them anymore, and we just name it us all our amount like that. And now we can Lord this. Now we have loaded the credit, the solution inside our original PP table. Now make sure you save this burger table as a connection on the So if you are accidentally loading it, it will create a new shit and add the stable, which we don't want because this table is already there. You only want a connection. So this has to be a part credit connection. And this is the final table, which is the division. And now we'll go to the somebody type here in the people table on we just right click and refresh. Now you can see there is a new field called Division, which you can drag like this and you'll see the division and are located. So what? Each year it is a looking at locating this spark lines, which is easy just to select this particular role like this. Wherever that it ties and go toe insert and ah, lying the spark lines, they will get this. So if you click here, you see the changes in this mini charged in itself. Now, this is completely dynamic, as I said before, and if you come here and change the percentages for prepaid rentals, let's see if I make this 25% and make this 15 and save it, I come here to the summary and just score toe. They try and refresh and refresh twice and the figure has changed here. So this is our it is done. Our dynamic a location table. We just help off for simple table, which is fully dynamic and automated. Now you can check as a counter and if you're closing the books, you can go and check that these division has been located properly for each month. For example, 2017 or 2018. I need to make sure that people health insurance they're located, which is so on it fire someone in this particular ratio, which is your location table so quick and easy fix on a completely dynamic solution. So this was a very nice solution. Quick and easy fix for locating your prepare explanation. Monthly to East Division, each cost centre. Let me know if any question like you in a section middle and if there any comment or suggestion and what you want to see in my next course, please let me know I'm working on. Few knew at once exile courses and I'll be coming back with that ready soon. So thank you very much for watching, and I'll see you next time. 43. 43. New Version Power Query and PT Model: Hanno and working with this bonus lecture in this lecture, I'll be sharing with you an updated version of the somebody for the Pure table power Query Prepayment Moral As you can see now, this model has been a bitter, slightly two main moral area with opening balances. See the previous or most recently, lecture, have publisher regarding this particular scenario where we have no opening balances and you can start using the morning from any date you can already any other prepayments? Or should you? You may have infinite auditory like analyst, you can already the prepayment expenses for any organization, your client or your own or conditional different division with this kind of scenario. So I've explained this and processional So the new formula and this new column retarded in the lecture. So check out that bonus lecture. Now. This is the same moral, the exert core engine of the morally same. But now this is the world's general power query, people table. If you remember, I had a one more bonus Lecturers have shown you how toe Ah, locate this pre payment expenditure of people in my lessons to division in a certain issue or if you know, exert the region water. Look it. You can put it here as well. Now, in the earlier bonus lecture have created this table manually. But in this particular scenario, I created this particular table. The location, table half. I mean, you can say this particular section it is automatically generated based on this particular table. So let's go back to the credits. If you go inside the equity, this particular table has been created on the first column. So this is the location table start. So this is the table from where you're starting. So Howard is coming. So it is coming from this particular Kredi. This is people, some very quickly, which I worry, shown you how to create it. Just a consolidation, all the sheet. But we will go through that in a moment. So once I get this particular table, I'm linking that equity as a reference says you can see here the sources thesis table, So is ical toe prepaid table. And then I'm removing other columns, and then I'm reviewing the duplicate. So I get this column. So each I'm loading here in this section here. All right. So if any new people in explanation is added here, it will automatically come here and then also here, and it will be added as the rose. So currently we have total six pre paper in sheets, so prepayment expenditures. So we have total six rules. But then I have added this particular sections division A BCG, so you can put whatever name you like. Oh, if you're last so you can do that, you can just delete the columns and you can put the percentages accordingly. So this is Esther. Off Check control. Check that this all should be called 200. Always. So what time being? OK, did this kind of Graham mediators with this particular section off this table is manual. But this section is automated. So whenever you hit refresh only this particular section will be operated. But this is there any changes of the any road military radio toe, really toe add accordingly from this table, educating another query. So if you go here location, table two. The source of this particular equities that table a location on the school table, right? And from there, we're just changing their types of greater So this is a tax and this is percentage and then you're merging this particular table with this table. So preparing somebody and a location table toe, if you must this to you get this table final location, table like this. So let's see how we have done there. So steps are almost same as we're doing in the alley. A bonus lecture where we have shown you how to rule this kind of summary. We got the A location table as a short moment before how regard this. And now we have the final. We had a prepayment somebody table, right? So how it is done. So you go to source. So sport is the source here from their dynamic part which were created. And then we get all the she's in the entire world book, and now you're filtering with the sheet with hazard, starting with paper name Rex. So we had only people, um, in sheets and then And if somebody table is already included, we're removing that. So better you keep some some other prefix for the somebody table. So I changed it to be be somebody. That's why it's not showing up here. Now I removing the other columns, then table because thes columns other than this Data column doesn't have any Detain it. So I'm reviewing those scholars and then I'm explaining the data's. Once I get it, I'm clicking this double headed arrow and I'm expanding their data and I get full details. So since the structure off are all sheets and prepayment should do, this is exactly saying we won't have any problems. So you have to ensure that whenever you're dealing with power Query, you need to have this kind of structure data. So in our case, as you know, all the she deals are structured properly. They're having exact same number of columns and rows structure and ah, later are no more. A bruise in each year is also same. I'm explaining it, and then I'm gonna moving top bro's. So if you go up, you don't need this 1st 4 top row. So I'm removing for us for top pros and then probably the headers, because I want these dates and then I'm changing the data type now. Reason why I'm doing that, because I want to extract on Lee the world people. Once I changed type all these digitize becomes text so I can go here and do attacks filter , which I've done here. You can see here. So have selected begins fit paper. So anything begins with prepared I wanted. So I got that. And then in the second filter, I'm selecting the start column and removing anything which is no. So this is the does not equal to know. As you can see here, we have nuts in this particular column, and we don't need that. So I'm gonna filter that in the start color. And now I'm removing the colors, which I don't need to want to. 344 columns we don't need. Really need from the start of the month is with 19. And after that, I'm one p putting all other columns apart from these two First column. So all this date columns, um, doing unpaid work. So I'll get this kind of structure. I'm changing the litter. Thai prime renaming the column. So proper names. And then I'm trimming the tax just in case there's any spacing at the back at the end of this tax, right? And then this table is ready, and I'm not gonna lured this to sheet. I'm going to keep it as a connection now, I come to this Kredi final location table, which is nothing but Marge off prepayment. Somebody table in a location that school table toe. So if I go to source here, you see Mars Kredi like this. So I am Margit as a nuclear early, just in case I want to be used the thes two credits. So I'm selecting people when somebody table as a main table for stable in a location table , a second table and I want to march on these two colors because they're this common key columns and I want o do a joint off left outer I click OK, and then I get I get this kind off column called Table, which is the data from the location table, As you can see in the down here and I'm gonna expand this so I don't need the description. I just need this division, a BCG data and those column headings and then once exported at one p, putting a B C d e. So column all these columns here to hear someone people thing like this I'll get this kind of two columns, attributes and values attribute is that thing but division name and values and think about percentages. So after that, I'm inserting, Ah, percentage multiplication with amount. So I'm selecting this two column and I'm adding, you call him like this, Adam multiplication musically and multiplying this amount with this person that has a location to each division now renamed the column. I added the year because I want to show people table properly. Year by year, I added a custom column here. I could have used the Arab Month name, but I added a short month. I'm like this. I used this custom function, which I'll show you. So the custom function is date to tax and then select the column. So our date column is my intent and what format I want. I want him. Mm, which is nothing but short form off. Months of January and one gents off. Everyone fare for March. I want Amy are then So one. And then I heard the name the column, and then I changed the type, and I just the order duck this true columns here near the date. And then I go to home and I Laurette. So this is the final word. You know? I get right and the most important thing you have done in this particular scenario. Let's go back to the pre payment table is opening Barras addition. Deletion, Right. This is the call of your getting from here. So if you go to pre payment she Dios, I have added additional four values here in column B for starting from B 67 will be 70 100 opening balance a region which is additional pay payment amortization, which is the charge from this particular among for this particular month and closing balance. So this is the repairman should use having operated like this so we can get a somebody of this as well. And I linked that exert name of the shoe here in this column A from 67 to 70. So I'm keeping all of them same. So they're still happy in the consideration. As you can see, if you go back to the somebody, you go back here, you can see this is Carla Medicis Column B on That's citizen. How capped the repeated values. And I can get this kind of reader. So all these steps are recorded, and when you, when you don't know this temporary will be able to see you are exactly Arab ended. So if you delete this so after this, this particular somebody will not work. So be careful with that and join our final location table have created this kind of new people table like this. So my entire year you can slice data like this. So do you Resonated You should be you can see and another table who created which is more important for accounting point of view for analytics Is the movement for example people electricity subsidy account or this particular GL? I have all the details opening balance edition tradition in a summarized manner for each year. So if I go to those in 20 out of the data So, for example, prepared prescription, we have opening additional tradition and closing balance through this few simple tricks and applying ah, or equity. In a smart way, you can generally this kindof refresh trouble report, which you can use it for many purposes for your presentations for your budget, most important thing would be budget and forecasting, which would be really helpful. And you did I would exact figures without any errors. It also this was my bonus video bonus lecture for you and you. This particular template will be able to for download for you. So don't forget to download it from the resources section and I'll be coming up with one more bonus. Like General, I'll be consolidating the VB model with power query. So if you remember, I mentioned that power query will not read data. When there are sheets, each are locked. So if you remember, RP payment will be a model WB immoral was you know, people men expenditure has control. Send checks and all the sheets on lock programmatically. Right. So in that scenario, power quick and orderly data. But I have found a way to work around that, and I'm currently working on it. And once that model is ready, I'll be sharing between the same and will do a walk through of that monitors bell. So thank you so much for watching and I let me know. What do you think about this particular model? If any questions are ready, let me know in the Q and a section. Thank you so much. 44. 44. Complete Walkthrough Advanced VBA Prepaid Expenses Amortisation Model: Welcome back, guys. This will be the last word in off the model, which is called Red ones will be a moral. So in this particular peppermint model, I have ah, this particular date filter. So if I select 2023 I will see only the years columns for 2020. So gentle December 2 dozen quantity. And similarly, I will see only the rose for gentle December 2023 that stopped. The roads will be here. And so let's change again this true 2017. So if you see the columns have been arguably a breaded to show 2017 gentle December and similarly rose are being upgraded to show 2017 gender December. Now suppose you want to create another prepayment should you'll for another kind of prepayment expense? To do that, you're to go to the last prepare expense sheet, which is prepare taxes and we're to click this particular but and add new sheets will be clicking that and I'll be typing pre paid for 30 insurance enter and now you can see prepaid property insurance. She has been created with the proper name is re enter in the input box. Similarly, sell it to has the same name. So this is our done where there will be according we're going to add a new she. Then you're typing something else like, uh, guard charters instead of prepaid card in Jordan. Let's see what happens. But the message is popular with the name new sheet named Tab. Name must come in with paper. So this court and shows that your sheet name starts with people and this You can maintain the integrity and professional naming style for your paper and model, and people mention use. Now what about the protection So this particular sheets are protected with a workbook protection. But while doing that, you to make sure that your password would be same as your sheep are sexual If I go to the baby, accord modern for this particular sheet, which is in Model One, and I'll show the parcel riches ABC. So here, if you go to back and off the coating window in VB, you can't change the past or the future. His current there captured Army pass or BBC so you can change the passer here. But protecting workbook is a different thing. we're protecting sheet. So to do that, you do click here, protect what books that will protect antiwar book not in New Jersey. So we ought to protect the world work in order for somebody not to relieve the six. All these options with the several when he protected and I entered the court similarly, That s whenever you add a new sheet modifier the sheet it automatically new protection will be added to the news sheet and according the court will work. But the protection for the workbook will have to have the same password as you enter. So in this example we entered ABC. So if I try to un protect this world were granted saying possibility, we see I'm going to protect it again. The workbook out, Renters impossible. You're doing Terry place. So these if you create a new sheet once again, like supposedly had prepared business, he's up. You see protection is applied so nobody can believe the sheet. Now let's go back to the summary impious example. The sum Later there was formula based on we used our credit impure table. But here somebody type is bean prepared with the baby. According now, this will be a cord is very simple. It's simply goes to unusual sheets with the name people, so it has to have the prepaid name as a prefix that's citizen out of the system where you should has to be created with the three picks word people. Otherwise, this updating off this somebody should will not work. Basically, this court will go to each unusual sheet and we'll track process this one people. And then it will select this row row 1 30 which is nothing but range. Be 1 30 till the end off a moral which is R 1 30 and it will copy and paste values here. So let me just click this border. As you can see, all the prepayment shifts have been updated here with their closing balances since I didn't change any values in individual shoots. For for this example, the values are pinning same, but it will update the values off those unusual sheets as a closing balance. Every time when you have a bit, you must click this button and it will copy here and how kept some buffer space in case this model expands to many more people. Explanation in the future control panel is same as before. So if you change to another starting months apart, we choose April. Accordingly, the ditch will change everywhere. Other functionally really worked just fine. So this not accelerate. Be. Of course, I wouldn't be explaining your detail in in Netegrity off our baby a court, but you can always go and access. This will be a chord with our models. Model one and wonder to here and this court's are easy to understand. You fear basic to never Knowledge of VB will be able to understand this court and applied anywhere else. You can always modify this court according to your ovation, how we want to make it more complex and at once it's all up to you. I'll be locking this model for protection purpose, but I'll be providing you password in a separate while so you can always access it and the way you want. This is a VB based model, and this is more for professional pressure, preparing a show you where you are controlling most of the things and you ensure that that accuracy and the integrity of the calculation is maintained all the time. If you're any question, leave them in the KUNA section and I'll be happy to answer that. Thank you very much for watching 45. 45. Bonus New Version Excel VBA Model: Hello and welcome to this video. In this video, I'll be giving your demonstration off operate. Axel will be a pre payment model, as you know. You know, of course, we have learned how to create formula based model or a credit your table water and accelerate via monitor. Now, on all these Mourners, the main engine behind the moral calculation is formulas. Now this this will remain same in this model as well. But if you remember in accelerate, be immoral. We had this somebody and we a lot of features, like adding new sheet. I made some updates. The reason why I did that because recently are downloaded these templates and just playing it on with demand phone some others that was do Trudeau worksheet door calculate anybody rents which is automatically calculating everything so that I will change and I'll show in the background. So this is our we be accord model BB. And as you can see, if I goto any sheets for example, people health insurance. This was the old macro animal Iran which was working which was worksheet under school calculate that has been changed your shit on this cold change. So that is more efficient way to work with. We be according here in the time model and it will increase the speed so unnecessary stops retreated. And then earlier, court and I used this one where now it is working just fine, especially by adding new sheet. If you download the existing workbook actually be moral and tried to a new sheet you might face, um, issue, especially when you're using your words, you know, back So like from 2016 and 19 on even offer six is 25 version that says that I made this update to this particular model actually be model now what has changed So as I showed you already, the change Iran court has been changing each sheet, as you can see, and the older court, our Decebal, you can still use it for some other will be a macro when any other application you want to use our captain as it is now. Also the drop down menu STD. It was a clear combo box from active control acts as you can see night. Now there's a protection here, but you can insert an active control X top down which was there already now change it to a simple job down that has made this court more efficient, entire working off this entire sheet more more efficient. And also the add new sheet, but an esteemed, too active control expert and, as you can see, and also I made some cording changes into somebody. So now all these roads will be ultimately hidden whenever you add new sheets, so I'll show in a minute. So let's go and add new sheet. And before you do that, just just see this somebody that only four sheets somebody here. As you can see, 1234 and let me add new sheet and I will go to any sheet and yes, okay. As you know, you can go to any shit and add new she child go to this business with a sheet and are a new sheet. Soured name it prepared land Lendl's on the new shit has been added. Prepaid land rentals and the name also been changed here and no, let's go back to somebody and update the somebody Now I want to see for 2000 and so this is most important. Before you click this object somebody button, you need to change the year for which you want to see somebody. So I want to see for two dozen 19 and now I'll click upgrade summary. As you can see, you only see the rules which has data. So that's the new macro credit. So people land rental has been added which was not there before. So this is working dynamically. And whenever you have you did eat any sheet, it will automatically change and it will reduce the role here. So I want to see that I'll show you. So before I do that, I need to disable the workbook protection. So let me disable that. And like we delete this relate, go to somebody and updates somebody. You see that particular row for people? Land rental has been gone. So this is the change and the macro for it is very simple. It is very useful Macro, actually, let me show you how it is working. So this is the macro is called somebody higher entire. Oh, so first of all in your toe Decebal the screen operating and then you deem the rangers arrange worker in years. Arrange Now we're gonna set the range for our Ah hiding. So for this, the ranges somebody list. So basically, this is the range. Let me manually unhurried Known over how to and protect the sheet. So let me on hired it. So this is our complete range for the somebody. So we're assuming we're gonna have this number off people sheets just for this model so you can have more or less It's up to you. But if you go here in the name ranges and select somebody sheet So this is the name Rangi refined. So basically, it's such this particular age and then see that any value is there in the ship's. So any value is here in this particular area, there isn't be column b so entire this range. If there is something in any cell, it will not hide it. Other logical. Hide it. So that is the rule here. So basically, we don't have anything here. They'll and total total people. Monroe 49. That's why it is hiding automatically. So whenever you have something here, so basically, when you goto our original macro off creates a bread sheet, this create index summary. I put this court here called somebody heights off merit based everything. So basically, this court starts with un protecting the entire worker because we cannot copy anything unless it's unprotected. And then first thing it does. It's Alexa somebody sheet and have put this macro called rose and tyro. He didn't fall, so basically when you update this rose already hidden so we need toe unhurried them. Otherwise the copying will be completely wrong. It will copy or the total people me right. So we need to Owen hide it and then the whole macron's to copy individual prepare men balances from each sheet and then at the end, I'm calling this macro Call somebody Hydro, which is here is basically selects this row and heights all the roads which doesn't have anything. So it selects anti range like this Somebody list this range which have defined as a name range which is Robi five till b 49 that is our name range is easier to reference in the macros I refiner name Ridge and it goes through all these roles and wherever there is nothing, it hypes it. So if you see the court again, I am putting zero there Angel you zero it hides it and in in excel zero means nothing anyway, so if you put zero, it will assume that there is nothing here. So it is this M p is equal to zero basically in excel, so that will hide it in entire this particular selection. So this is the change I have done in the macro. And also, as I told you, the new button and also or all engine performance off entire model is working more efficiently than before so that we know if there any questions regarding this new workbook . As I told you, this workbook will be available for download in the resources section. You can download it and try it with your own application and for any question As usual, let me know. I would love to help you out. And you have my email ideas Well, which is for shot that exit appropriate dot or and do not forget to visit my newly created block level every every dot exit, probie a dot art the link for the same. So you can see down here like that you are so thank you very much. And I'll be releasing more video soon on my YouTube channel s well for the link for the same is in my public profile. And you can always such exit probie A on Google to find my YouTube channel. Thank you so much. 46. 46. Dynamic Dashboard Overview: Hello and reckoned with this bonus section in this bonus section will be going through cities or video lectures in which I'll be showing you how to create this kind of dashboard . The dashboard looks intimidating at first, but it's very easy to create. I'll be using name ranges. We look up Match function and Emeric function in creating this dashboard. Now, in this cause, you already gone through the basics office for four functions. How covered them with the Laura example. So if you want to get yourself have a refresher, you go through those lectures again and come back to this particular lecture. This particular dashboard has a three D Regions profit and loss statement, so this is more for somebody in a different manner. So here where we have our income radic, labor cost, vehicle costs, other costs, total cost of savages, air subdural of this tree and there's a cost profit, which is nothing but income minus total cost of sales. And there's a gross margin, which is nothing but cross profit, divided by sales, operation, overheads. And then there's a net profit, which is nothing but gross profit, miners, oppression or it and then ah net profit margin This state eyes for the month. So, for example, if I change it to February, you have AH data for February for Division A, B and C and in year to date, February Load a tough for Jen and FAP together. So it's a year to date I laid out yesterday February 2018. Similarly, if you change this to May, you have only may months data and then you'll have a year to the data from Gento me. This has been done with the help of the local function in direct function using name ranges than match function. To make this entire dashboard are dynamic dashboard here the charges which is the revenue chart. So the shows ah, revenue share for division A BNC and this is gross profit share for Division A, B and C and this is ah not profit share. And in down here we have another A dashboard. Somebody read. We have ah, rolling dashboard where you can see Gento December how the figures are coming up. This is a monthly figure, and it's it is showing lesser data than what we have here. So we have income cost oxes cross profit operations overhead Net profit course margin percentage, Net profit percentage. The best thing about is that when you choose a particular day, for example, if you're gonna choose June, the data for other months will not be appearing. Even though the back end the profit and loss statement has data for judge July to December . Right? But ah, how sort of formal in such a way that once you choose June or any other month support like August, he will see get up from Jan fair march till August only and not here so accordingly you contribute it for the period you want to review. So we have for division A division B and emergency and then here today decide the area charge we show sales cross profit and that profit for creating this chart have created a separate data structure here which is at the bottom in this great box where ah, I'm pulling data from here so accordingly these charges will be abated. I will brief flexpen you how to create this kind of charge Donor chargin area church as well. Here, This is not a chart Costa visualization course I'll try my best to explain you how to create this kind off, clean looking charts and using Ah, any function which is more off a hack, where you can see that are up to a particular level only like this. It will not that I will not be dissipating or the charts are not going down to zero. It will just cut from here. So this is a trick you can use in charting using a any function, which I'll be showing you to get this data. We need three financial statements or profit and loss statement, which is for Division A division. Be individuals says you can see here. So as you can see the structure of this date, I similar. So it starts from column me in and sing column and and then the additional Columbia to read column. Oh, so the main columns are eight to end, and it has a structure drove from called row for still Row 56. Similarly, in Division B, the data may be different for that division, but the structure is saying which starts from column me and saying Ah, column and there is additional column, or which is here today, which is not compulsory, but I had just captured because of this part of the main source report. Similarly, division she has a similar structure, but the figures are different because they just completely separate division. But the soil structure should be seen now created your to red Division P NL based on this particular it. So this is a monthly figure. This all our monthly figures. So 123 this all our monthly figures for that particular month. So we need ah, here to date figures. Why? Because we need this in this dashboard. But I could have used more complex function to derive year to date figures from this particular monthly Pinellas. Well, but it could have been more Ah, complex formula and, uh, on the other side, just go for a simplified approach in creating dashboard where you can just copy paste this particle division here and create a year to date form off penal. So it really simple ejector to link that sell because structurally same, you can just copy this particular file here and then create a year toe just link theirselves like, for example, in general ruling January. Can you lean January plus preparatory and are calling Luke create a year to date profit and loss statement like this. There's evaluation for dates which you're choosing here. So the red edition is linking here and this is also dynamic. So if you change the year, if you change from 18 to 19 for example in this particular cell, because everything starts from this self. So this cell is linked with that relegation formula. So if you change this date to 2017 there is other appearing here because the validation is not in real time. It started working in real time, so that's fine. You just go here and change it accordingly. Everything will work just fine, so this is a dynamic way to create evaluation. Now let's go through this particular dashboard exercise and we'll go step by step in creating this dashboard I have uploaded in. Ah so section the fights to work with this. So you have 25 completed version which I'm showing you now, and that is a start file which you can download and start working along with me. And also there are three data files which is nothing but profit and loss statement for this three d vision. Do you genital region B and emergency. So these files are I'm calling the data files because we're copying that file toe our dashboard file. So let's start in size, and I'll see you in my next lecture. Thank you very much for what? 47. 47. Importing Profit and Loss Statements: So let's start building the dashboard. So this is the starter file, which you can download from the the sources section. We don't have anything currently. How Just kept the dashboard as we don't want to go and work again on the structure as it is already defined. So this four function we're going to be using we have validation tap, which I have kept it and ah, we're gonna import now the profit and loss statement for Division A, B and C So these are available here in the date of ice folder. You open this file and yes, we're gonna move this to our dashboard files. So I'm going to right click here and I will, uh, go to more and copy. And let's create a copy. In my mind, Master filed the start file after this dashboard a sheet and click OK on Let's name it, Division A. Let's close Division a file. Now Goto division be filed in the data files Mamma's division B and we'll move it. Let's go to our And here it is division be just rename it to division similarly will bring in Division C five from what a tough order. So open division. See, I'll just copy this. We'll copy before alliteration. Rename it to Division C. Now. We don't need other files. We can close. Ah, division we we don't want to save. Similarly See, you don't want to say and let's say you are mean Ash board file. Now let's create copies off this files A, B and C. I will select them and right click, move or copy and create copies. I will rename it to Division A Year to debt. Do Jin be today? When did we just see a tradition? Now let's color this to a different color, like purple like Papa and this to ah, darkly. So we know each other files for year to date and month. Now let's upgrade. The figures for your today were to go to go to division a year To that sheet is equal to go division a month figure and for the month off Jan. Go to link Division A. You just sell, see, fight and enter enough for Fab Uni Janet. Perhaps their toe are gen from this particular she reaches. The was in the year to date in San C five and Goto Division A again and select Fab. Now we can drag the formula now. We don't need this year today, so let's dilute it. Because December traditions I wanted itself is a year to date figure, which should be matching here. Will irrigating a validation check in both the sheets, so I'll be showing you that. So let's do one thing. Let's delete, uh, later. First like this. Now let's linger. Basic salary roll, which is ah nine to go to division a month sheet select, see night and enter court to February 17. Year to date sheet is equal to Jam plus Co division a month Shoot Select February and Trap Control C copy and dragged the formula till the end. Now, since we know the structure is saying because we cooperate this file ah for division year to date file So way can just easily copy and pitched formula like this. Similarly, go here and do same for Matic vehicles section. Similarly, Goto 23 to 28 which is direct other costs and copied. Now we can continue copy and pasting for operations overhead section as well, just for down and selected. Now the idea is that yet you did column in this particular she division A should match with the December column, right? So that is one of the checks or escaped and other check and check. The former writing is cooperated, Trudeau warned Off. Make it bold and direct. So Allah check is this minus. Come to division a month, Select the year to date column Jeez, or column or five. Enter and just copy and drag it down. So everything seems to be okay as everything is matching. Similarly, I'll be completing division BNC now. I'll be fast forward in this particular section as we had to do similar steps. Just tracked the formula here for Ah, you're predict and create an at a check. So go to Division B is equal the month of January. Go here, Select chan in this division B etter that she'd go two months sheet for the vision. We select Fab and enter Dragon due December. So control See pissed control. See, let's create an adder check here. I'll just quickly grab the tax so we don't have a type is equal to this miners. This column here to date enter So we're fine. Similarly year to the division. See Relief column or Goto Cell C seven Equal Goto Month column for the regency. See like the same cell Goto Fab Individual year to date. Go to month Regency Select Fab Enter Control C and dragged the formula. Let's copy only formula we don't want for march to get must so using a shortcut Key control Yes, F which is for basting formulas and now similarly like, Just like we did in other to here today cheats will create at our check board it color it is record toe and fire Goto Month Emergency Select and fire There it should zero and drag it down. 48. 48. Creating Dynamic Data Validation: Now I'll just delete this and create a fresh relegation. So we have to use function called date and ah really want to create the range here in this particular cell, C three in the dashboard. So that's created here and input for creating this date. Stranger Validation range. See to where you'll be manually and telling the year So for here we are entering 2017 because they try belongs to 2017 financial year. So let's create this range. I'm choosing to the 17 as our financial years. Well, your formula date and year is so see through Let's freeze it since my financial year is Jane to December and the Montini's always January 31st so I can manually input this like this. And now I can link this cell here with end of the month function you month, so I'll get fat. Let's keep the former same get down to the December like this. So now this dynamic day trained is ready. So if I go here and changed through 2018 and I come to a validation tab, this is changed. Only problem is that this critical evaluation will not be a paraded in real time. So you're Do come here and again when you change this year and select appropriate view date now creating validation. Very easy's at me. Remove the valuation first. So validation clear all now, There is no validation here in self C three. So to clear relegation. There you go to data validation and select a list and will be choosing source this validation range like this week. Okay, now it's ready. 49. 49. Creating Named Ranges for Dynamic Table: the next step would be to create the name range is now in the dashboard. We want only somebody state. All right, so we are not concerned with this account Courts Chartoff account courts were concerned with the tax. So for this dashboard section that tables, we need income first of all, so we need income from here. So to make sure that the taxes same in both answered here, the tax should be seen. And here, so basically photocopied attacks like this from here and come here and based it like this, you can link it as well, since we're gonna assume that your structure will remain same. So if there is no row gonna be added here, the structure will remain seem so that's fine. But I suggest you do it manually because in case that our morals are added from religion CD , the local function really is gonna use to looking up this particular section, catch this particular road tax and try to get the correct data. So let's copy the tax rather especially be born. Ah, direct labour cost. As our first summarize figure, cross properties there and opticians ordered is already corporate by just to ensure for whatever the figures we want to take from this particular tables, we want to copy the exact force column details like oppression ordered. We want toe. Bring it here. So to copy exactly the same because we look on formula looks exact back. So there are some Spacey's, so there's a spelling error. The formula will generate another Let's use name range to create 34 name ranges for this ABC month later, and you do the data. So since we look up when a look, the left Most column we don't want to use this course as I mentioned before going to use this tax. Look up, right? So we're gonna choose this like this. I'm choosing from column B two column and on, Dude the role. 56. This is that profit percentage. I'll go to this box and given name revision a month, and now the name Ranges created for Select division Aim on It will select this entire range . All right, and similarly for Division B will be selecting column B two and and tell Row 56 and let's name it toe division be month. Go to your to date. Same, my third. Now the name ranges already 50. 50. Dynamic Date Column Headings: Now you're to ensure that the column hearings here in row four for all divisions A, B and C and also in your credit files is dynamic as well. And it should link to our this cell See toe. Wherever we change this to any other year, the date should be changing, as we've seen in the validation. So let's do that. So in I'll come through division a month. I will link this cell to our dashboard, Cito, and make it absolute like this and copy the same in all the files. Come here on here. So everywhere it has been corporate now, Ah, formula with date Here is this exquisite month is one and 31st So terrible. January toes in 18 and again e o month function Select the January date and want to it and record February and Mystic. Now we can just copy his entire range, come here and based it so accordingly. Since the structure is same, this can really, really fast go to your to date, Agnes. So this is important to look up the column number when we use, we look up in the dashboard and using match function will be deriving exact location off the column. So 1234 like that and match from should be doing that. But in order for us to use match function dynamically, you need to have this dates common everywhere. Similarly, let's Goto dashboard here and try to replicate the same so we'll be using date. Or you can just link them to this particular being a statements. It's easier copy the formula. You can go to your division B sheet here and do that, or you can link it here directly in the dashboard itself. Like here in Division A. Because all the reporting for all the division would remain same. Copy the entire thing come here, based in Division C and also in the entire company. 51. 51. Dynamic Month and YTD Dashboard tables headings (PRO TIP): No, let's start building our dashboard. So, first of all, I need to create a dynamic aiders in cell C five and sell G five. How created a Marge Range? But the formula will be appearing in C 54 month and for you to date in G five. So here we will be using ah and person days tojoin the tax and a function. So let's try to write that. So is he called Toe Month. Since this is ah, a month's data for a particle amount, we'll use n percent age and space very clear space than again and percent age and then amusing tax function. So tax function is quite simple. The sector value. So value here is the state we're gonna be giving an absolute reference to freeze it. And now what exactly? We warned this particular value toe appear as attack. So I want ah, month name. So So we need to write in this Internet coma. Mm mm. Which is a full month name. So for January, Janet will be January. So I'm gonna close that and press enter. So months. January It came like this, and it is a dynamic one, right? So if I choose to February the Jews. If I choose February, it will be month February. Similarly, if I choose May, it will be month Me. But I also want the year along with this month, May 2018 or 2017 in this case. So I want that as well. So let's add one more joint here, so I'll be using this personage again and tax function similarly here. We're gonna be freezing same date cell which is C three and instead of month this time will be using Why y y y for year and anta Now if I choose this February, I will get full tax like this month for Putin 17. So if I changes year 2018 and as our tradition has just changed the tax and heading in C five for this month section will appear accordingly. It is a dynamic one. Now for you to read it simple. We're gonna be select basic formula like this as a tax and come here and based it. Oh, it's more self. It's finally gonna be selecting with after and paste like this. And now instead, off month, we just have to change this two year to date. Enter now. More trainings are dynamic, so whenever you changed it here, accordingly it will display. 52. 52. Dynamic VLOOKUP Formula First section of the Dashboard: now that we look or function. So we look up, select the look of value and freeze the column, which is $8 7 Now, this is the tricky part. Well, we need a dynamic table area. So to do that will can be using indirect function. So in direct and we need a tax reference, which is division a month here, and we're gonna be freezing before so see dollar. For now, we need a dynamic Call him and neck so but you that I'm using match function. So match or is a look of value. Look over. Lose this the month for the report. So let's fix it with the It's absolute reference it is not going to change in all report, right? So let's use that as absolute reference. Now, what is the look up at? A look up a day or two. Choose Division A from me do. And as I explained before the look up, a range which was part off our ah name range in A B and C and the look up today for match funding should be same. So it started from B and ended it. And so similarly for this range Also, we're gonna be starting with be an any times with and I'm gonna be their friends Get as absolute after there's not going to change and we want exact match close parentheses and again we want exact match. Well, we look up, enter and here we go regard the figure for the month of October. So let's go and check. 2097 Identify should be the sailing October in month on October. Yeah, it is 209795 So our former lessons to be working fine, dynamic formula. Let's copy to Division B and C as well, and see if it is working hopes we got at her. Why? Because the frieze. So instead of column for a seven. So let's correct that copy again, and it seems to be working. Let's just quickly check it. Division B for the month of October should be 36 992 and the Regency for the October should be 68 74. So let's go to this month and October is swine 36 992 Let's zoom in and see October 68 8 74 so the formula is working fine so far. This is the sum A Since formula worked just fine for the income revenue, it should also work fine for direct labour, cost vehicle costs and other costs and also operations over it. So we'll see, like, this formula like this and called Pete and based. And it seems to be working fine. And thats copy this part operations over it as well. Now, let's clear the other a handler here. We have a fair already setting because it passed a part of our dashboard when we started using this sheet. So I'll just corporate here like this and here, similarly here also, we can use if error in case there are We don't want toe Blake the formula copy. Pissed, pieced. No. Since this is the whole dynamic function, with everything dynamic will dare, I should say to corporate here and see if it is working and it should work. And yes, I think it did work. The charges were here already such it appeared now because these are yet to the charts. They were not working, appearing because the data was not there 53. 53. Creating Rolling Dashboard with Dynamic VLOOKUP Function: now let's quickly test this formulas whether it is fine so far. Example Cost top sales for the region. Eighties 1 67 5 35 which is a month off. October is correct. Natural changes this to February and let's JAG Division B 52 fighter devotion because, top says and 12,000 should be net profit in year to date. Right, so we go to February, zoom in. This is fine, and this is also far. So this particular sexual dashboard is ready and ah will be doubling shore by inserting Laura patter checks, which are here. I'll be fixing this soon, and we'll go through that together. So don't worry about this other check. It will be fixed and will be quite confidence once people this number of others with just seven error check. So quite a confidence we have with our dashboard data are being correctly populated. Now let's ride. The formula in this rolling dashboard section will be using We look up again. We look up look oh, values in Campbell Avenue in draw 2121 were to freeze column, which is Then again, we'll be using indirect and refreezing Division A month because that is the reference which we created using indirect function. And this time we're to freeze it completely because this entire later belongs to Division A and I'll call em Index again. Magical match function. So the look of value is here now, So let's freeze the road, which is 20. Look up at it again. You can go division and select before till and four Freeze it and we won't exit match close parentheses False or vehicle Grew on exit match on Enter. So it worked Fine. Ah, we got ah sale for ah went off January because here in June so exchange to January on this to our imagining. So it's working quite now Let's see what happens if we copy and paste formula in fact, deferment off fatness quickly check it when it if I have a digital for for division it which is fine. But as I mentioned before, it should be zero here. Why? Because our reporting view is chance. I want to see any other months traitor monthly data under then January. Right. So if I choose fabulous, I want Janet fab. But if I choose march or, for example, may I want only data from Jan Feb Margin. If I just copy this like, here are your data for till December. Right? So you want if I'm choosing suppose August. I'm getting data out in December. Here, here. It's fine. But here the ruling dashboard is showing that are incorrectly so it should be zero starting from September till December. So how to do that? It's very simple to use a function like this, so ah, head off our vehicle function. Your do type if and the logic is that let's try to read it everything here first and go to SAB, so I'll select chan firsts. So it'll be easier for you to understand how this is gonna work. So we go here, we put if in front, off we look up. If the logic is that if the February months or with this particular cell which is deep, 20 here and we'll freeze, thorough is more than reporting selection date, which is in cell C three and less freeze it completely. We don't want to see anything here because since we are reviewing data for generally and up to that particular month, so going to see it on little January because I want zero. Otherwise, we wonder formula value, which is as usual and press enter. As you can see. Since this is January, that is no data for fat. Ben Efi dragged this. Everybody did zero. Why? Because the if condition is making all this figure zero, since we are only living January. So if I change this to suppose apple, I will get data from gentle heparin and all other months will be zero. Now, let's copy this formula to January as well. So all Formula A similar chan through December like this and will select this, including the total and copy in cortical Stop says cross profit should be a simple formula . Every new minus cost of sales like this and now for warheads we need are we look or function with the condition. Now let's copied his entire section like this and based in Division B. No, the new ties seem because the problem is we didn't change Division B as, ah, table array. So let's do that. And also you can change this gentle December references as well. If you want to keep the formula consistent, it will not show other because everywhere the day trains change. Really? Somebody seem so. But for the sake of consistency, let's do that like this. And for ah, table array. We need to move this to be like this. Press enter. So we got the correct figure and let's copy and paste December like this and also for operational or X. Similarly, let's copy just January indigency and more the they strange to see these when she section on also moved our table a reference from ah me to see like this. So we got the correct figure Copy till end formula selected based. Okay, some problem here. We need to give a simple formula like this now that we just copy here, selected in overhead section now, total section is simple. Just some everything. So income, income, income everywhere, Similarly cost up says for three D regions and or edge for three d regions. So if the daytime this three is correct, this total will be correct. But anyway, we're gonna be using ah are other checks which I'll be explaining you very soon. They all are zero. No, because ah, everything is matching. But don't worry. I'll be removing all of this and we'll be doing it in the next lecture, step by step. So our dashboard or all is ready on. Everything's working fine. But as I mentioned, Let's try to work on this error checks. I'll show you how toe set them. 54. 54. IMPORTANT Error Checking (PRO TIP): Now let's set up an order. Ever check and to ensure that all this address checks are zero. So we want to expand this like this. So are charts appear. And since this is a moral for hidden section, and every time we might forget to see that everything is zero So let's bring all together at one place by summing all this other. So all this should be zero, as we know, in order to have a complete confidence that the date I spoke correctly for a B and C section in the dashboard, right? Yes, Capital, Seven checks for each division and year to date and total. Let's sum everything up like this. Um, and we'll go here in this section on Let's freeze them and enter. So this is zero now you to ensure that it is absolute. So even if it is minus, it doesn't matter. It will committed to positive this one or any other figure. It's an adder, right? So we understand absolute figure. So it should be always zero. So now go to conditional formatting and we'll goto rules. So we're going to get I can sex like this. That's take this and we go to manage rules? No, Here we don't want person. We want number number. So always it should be zero. So it is less than one or Ah, for example let's make it more than one. It should be at her. And if it is between one, it should be correct. Otherwise it would just let then one also It's another. It is not gonna work anyway because we have put absolute functions. So it will be always checking this that it is more than one. It should be at a check like this cross. And if it is between zero, it should be our current take like this click OK, and before God to take this because you don't want to see the value only see only the tick mark the Eiken click. Ok, apply. Okay. So now, for example, if let's make this has fire, ordered their formula and you see that is an adder check feeling order. There's something wrong, So let's undo that. And now our dashboard is ready. Only think finding here is ah the preparation data for this charge this area charts which I'll be showing you shortly 55. 55. Data Prep for AREA Charts (#NA Function): Now let's prepare the charts. As we're seeing in our completely template, we're gonna be preparing the donor Charge three donor charts for year to date figures for Division A, B and C, and will be preparing area chart for Division A, B and C for sales. That is income, gross profit and that profit. I will recreate it. This area from which will be linking the data for our idea charts here are changed. The names, I mean, the terms we're using, like income revenue. I change it to sales cross profit net profit because this names would be appealing as a categories in our area charts. So let's sling the data like this. So for Division A simply link cell C 21 like this on gross profit is equal to cross property Division A on that prophet, simply not profiteer individually. Just drag the formula like this, so our data is ready for our area charts. Let's prepare. Our first area chart will be selecting little for divisional from here in this great section where we're prepared, it are only for our area charts, so we'll go to insert recommended charts, hit your chart, and that's put it here on. We're gonna resize it like this. We don't need chart title because it's just besides Division A. So it makes sense that it is 40 Visioneer, right? And let's former legend on the top like this on can you some over decide cricket board? No, the area are not correct because we want sales on the top and we want to see cross property and profited the bottom just like the hard in our completed model like this. So this is sales. This is grass profit and this is not profit. So similarly, that's select this chart, select data and we'll move this town like this will move gross profit Danza Net profit first class profit second and sales at the bottom. Here we go. This is correct. Now this is says this is, ah, gross profit and this is not profit. Now let's change the color scheme. So forces, let's select dark three for grass properties fire net profit this point. OK, The problem is that since we don't have daytime September, October, November, December since we have selected August only till August, the chart is crashing, which is incorrect. This doesn't show. Ah, good visualization as readers might get confused. What is wrong if they're seeing only this chart? So how to award that? I'll show you a trick, which is a using and they function because only the any function chart doesn't understanding. Excellent. When charge sees any value in the values for chart creation, it completely ignores it. And ah, charge will look better as if there is nothing there. A charge would look really professional, and data from August will not be there. So it will cut from here. And that's why we're to use any function where chart cannot understand that particular field. Currently it is zero, as you can see here. So charges considering as a value zero that tries going down and falling completely for sales, cost, profit and that profit, which is, as I said before visualization. So if in syrup zero, if you put any here, are charged, will stop here in August or whatever data selected for the report view. So that's true that so we're going to be anything this existing formula. So let's I needed here who were using if 21 is equal to zero B one and a otherwise we won't k 21. So we got any Corp it on corporate here now, since our formula wasn't dynamic dragged this down, how to manually change each and everything. So I do that quickly in the first quarter manner, and I'll come back to more explanation. 56. 56. Visualization AREA Charts for Month: now how copied the formula on wherever there was zero is any now and you do this. The chart will ignore the values, and it will not crash 20 from July onwards as we have seen before. And, uh, you know, we're selecting this section now and go to the committee charts and select a D A chart like this on click. OK, now, still, it's crashing down, but that seems to be some problem. It XLs off now, so let's try to fix this Former deter cities select primary cities again. Primary access and knowledge seems to be working fine. As you can see, since we have selected at our June all the data from July's not appealing, it also charges completely ignoring it. So this is what we want in a professional visualization. We don't want to confuse our audience where there is no data. So if the date I selected till June we want to see chart in June after that should be nothing. So let's track this chart just besides division a rolling dashboard here and ah, lest ah, remove the chart pattern, we don't want to say grade. We want legend. Um, that's movie legend on the top like this and chart, we can still drag it on. That's smooth legend in the corner. I want to change the color off sales. So let's make it dark gray. Something like this year's on profit and cross poppet are fine. So six also changes values to thousands like this. So you almost space now you can see sales cross profit, net profit. So sales is the biggest area. Sort off. This this much is the gross profit and out across profit. This much is the net profit. We can do some modification, like board this board this and board this section. Now we can simply copy this and based for revision, we and now select data Andi Select division be like this. And again. That seems to be some problem. No, we have to go to for my data. Idea is re selected, and it's just fine. Um, has to it for C Select data early. We didn't see like this again. It's crashing for my data. Cities just re select primary access and for total Also, we can based one more time like this selected, uh, select the total section here. Okay. Come here. former data cities select by Mary Access Again. As you can see, the visualization is really nice, and from the charge you can see you Agencies seems to having the highest net profit. But it's also quite fluctuating toward the year. Like for division and me. The performance that is that profit revenue and gross profit is quite consistent only in the Regency, where there is a highest amount of profit for a sale made, the fluctuation is also quite high. So this is how you can visualize your data. And if I go here and that, resume out on select till December. As you can see, chart has been abated, as I mentioned before, Division seasons Trevor Laurel fluctuation throughout. Deer in there, KP eyes while other divisions are just smooth out a drawer, dear similarly, or our performance also smoothing out just here and there. Ah, the spikes are there because of Regency. As I mentioned 57. 57. Visualization DONUT Charts Revenue, GP, NP 1: I will be preparing a donut chart here for Ah, you're today Division A, B and C, but will be preparing our category based on sales. Cross profit and net profit on the donut will represent A, B and C as a share. So let's try to do that so I'll select Ah, this settled Region Abyan Senior Treated Section and select revenue like this you go to insert loner chart on will be placing it here. Now that is a pre selected teams, so I'll be choosing this one. And I don't want any media. Ah, color like, ah, remove the Sheriff Phil like this and outline. And also chart Di Toledo warned on We don't want Division A, B and C that's seen the color for this particular section. Let's say, like gray, which is the highest, are off three ridges a division A on for the region B uh, Let's seal act under color blue and we'll see. Let's keep the dread. No, we want to see division names here so we can added ah later labels that you want to see category name like this. So this is perfect. Division A, B and C and will increase the size off this whole plot area. So let's do like this. Andi, move it. Never here. Let's try a little bit on the right side is we can reduce the size from the tax. So now you can see reach area in this particular donor charts belongs to which division? So A, B and C. As you can see, the region has the highest share of revenue than the regency and the region B. Now we can put something in between here. Or we can also add just the size off this particular area. Ah, donut area. So let's make it like this. Yeah, this is perfect. Let's copy this chart for gross profit. Net profit control C control. We like this, but it beside it and again sported with Ari. Now let's select data again. We can just drag ah to cross profit. And here we can track for that Crawford. We can deliver a just man for aligning them together 58. 58. Visualization DONUT Charts Revenue, GP, NP 2: now audience may get confused when they see your dashboard. That did what belongs to walk. So this donor belongs to watch revenue cost up says our ah gross profit so they don't know . So actually deserve any of this is God's property is not profit. So we can insert attacks box here, which would be dynamic to ensure your to read revenue. And yet today across profit And here today, net profit. So before we do that, we're gonna vacating a dynamic label here for this three charts. So is it cool to have in you? I'm gonna be using n percentage again and less link this cell like this. So if I dragged this a bit down, you'll see exactly what I mean. So that when you hear to the December to the Senate in this what we wanted to appear here right in this particular section, similarly for gross profit and that profit. So let's copy this here and say, like this cell like this, uh, here. Now, instead of revenue, we want cross profit so on, stirred over when you hear, we want growth net profit. So let's shrink this because we know what we're gonna select and what will appear not to put a tax label here for every new gross provident profit we need toe insert attacks box like this and select the tax box and link the cell it just behind it So said True is here and now we can see the value off K two. Now let's format it. We don't want any full o r outline and drag it here like this. Now we can, ah, align it on, make it board and increased the font now since December and also these side like this. Redo the phone toe 30 and it's it's fine. Let's select this to right click and group. So if you move this now, tax walks will move along with the chart. Now, after creating this text box year, we can simply corporatist Xbox to a next chart that is gross profit and instead off cell Kato. We can select sell order, which has gross profit tax and press semper and also we can copy the former from the previous label like this. So it's done, and this is also grouped automatically because we cooperated. Similarly, we can copy here for ah net profit and this changed the cell reference toe as true like this. Come here again, former and based on format. And this also here is linked now so we can put them do with closer and we can either hide it or you can do it. Very light grey soups not okay. Foreign similarly here at four. So the taxes here but is is not appearing, but rather I would do it light, create like this. So we have some for more visibility. So our dashboard is complete now, As you can see, it is completely dynamic dashboard on If I select Ah, any other month like August the retired changing in the charts Also bidding select October Accordingly, the and die dashboard is refreshing. So these are you prepared a dynamic dashboard with help off few simple tricks. That thing was difficult in this. You just had to be focused. Now, any time you have a fresh data for next year, for example, or budget their tiny that it as long as the structure is maintained for this A, B and C, and also just make sure that the aggregate figures also correct. You can just change this Ah year for next year like 2019. And this changed the year like this, and everything will work just fine. Our everything is a building like this. So if amaze there, so the taxes are breeding in this donor chart and accordingly date eyes also walking just fine. So thank you very much for watching. And please leave your question in the Q and a section. Next bonus lectures will be creating this kind of dashboard using power query. So will be directly going to the folder here. The data files like this and selecting the source files and using the power Kredi engine will be abating entire dashboard and creating such visuals. I think it's a power query. We don't have to do a lot of manual work instead of this kind of tables. We're gonna be using a people people very fast, and it's sort of discharge. We're gonna be using people charts and also in case of power Query. If there is a new division is at it, it will be automatically updated and all the charts and reports will be abraded also dramatically. So that would be a fun exercise. I hope to see you there and thank you very much for watching once again. And I'll see you in my next section in my next bonus video. Thank you very much. 59. 59. Introduction Formulaless Dashboard: Hello and welcome this bonus section video cities in which other creating a dynamic dash for the help of power query and people tables. This will be a completely dynamic dashboard without any formula at all. So we'll be using Power query and people tables to create this kind of dynamic dashboard In earlier dashboard bona cities have shown you how to create dashboard and poor formulas and function. He's also very good exercise, but this day spark weight is very powerful tool which help you toe concert it and create this kind of dashboard with help off simple clicks. Now, as you can see this dashboard as a two section, this particular section which shows Montanier to did our figures for Division 80 we shouldn't be indigency and also total of Do Jin A, B and C for the Montanier today. So if you select robbery, the figures are appearing for preparatory for division in the for the month of February and in year to date throughout January and February. Similarly, for division be month off February and year to date, February and similarly for division See Mont and yet you did. And this is the total. So if I isolate June, according to the figures, are being a critic. Similarly, this charged three donor chartered represents segregation off revenue for Division A, B and C gross profit and that profit with are also being abraded whenever you click here. So you can see here if I slide here November December that the chart, the pies in each donor charges being slightly modified according to the data. Now, in this below section we can see here is a separate dashboard and separate charge. This particular dashboard represents divisional later, which is gross profit, net profit and revenue and it shows the progress. So this is a date slicer Pierre Slices. So, for example, if I select Division A and I say like till June, I see a progress off Jan to June for revenue, gross profit and that profit for Division A, you're select division B. I see gentle June performance for revenue, gross profit and profit, and I can select slicer Peter accordingly. Now, only key thing here. This this this represents accumulated data. Also, if you select I want like this, it will not work. So how? Toe click and drag like this or you're to use control. Keep going to see a particular three months like this. Oh, you can use shift and hold and then click the next period like this soil selected giant would July. Or you can select August to December like this and you see the performers being abraded in the line chart. Now, when I say its dynamic, it's completely dynamic. So have been sure. The parkway table here are concentrated data flies the source files for Division A, B and C, the help of power quitting manipulated accordingly. Now this is the parkway repent, which I'll be showing you and sharing with your do created in the next section of videos. And as you can see here, really three divisions data A, B and C. The beauty of this dashboard is that is completely anaemic, as I mentioned. Now let's add more divisions. For example, we have two more divisions that are coming that is division D and eat. I'll go to additional divisions. I will copy them in this data file. What is your profile for? That is the former from which this park ready window is pulling all the data. So now we have two more divisions. So Now we go toe data and the fresh twice. As you can see, we have got the reason d and e appearing and you can slice that accordingly. And also the charts being abraded now it includes tuition. DNE is a part of the price for that we knew gross profit and that profit here also, we have two more divisions. Now you can slice and see the data. As you can see, I didn't use any kind of formula here except in these two lines where I am showing loss provident Provigil very simple formula, which is nothing but cross property and wherever new. Apart from that, I haven't used any kind of formula at all in this entire dashboard. Everything is being done without pop art 20 and people table. You might not be completely worse with the power credit. What I'm telling you, it is the best tool for the transformation having Leni Parker distance many years now. And it is the best tool ever for the analysis and consideration in excellent. So if you don't know park ready not to bother, I will try to be as smooth as possible. I'll try to be very slowing in explaining you all the steps. Are we taking in the park with the window? And you need to have a basic understanding of people table how that we were table people. Sharks work. So that will help a lot in this course to understand how to get this kind of dashboard with no formal at all. So let's get on with this exercise, and I'll be seeing you in the next section of the video in which I'll be creating a data conservation like this in the power query that will be our first step. And after that will be going through the dash medication exercise, which is nothing but pure tables and people charts. So thank you for watching, and I'll see you in my next video. 60. 60. Understanding the data files: before we start building our dashboard with the help of park waiting people table. Let's look at the data set. So this other three fives were having It's his division in the region. We do. You can see I opened them already here, as you can see. So this is division is property loss treatment. It is part of them. As report, it starts with the role for an answer. Drew 58. It has a header. Three rows, a 123 as you can see here, which has division name, then he will be report in the financial year. These other date columns and it starts the E and answered. So this is a structure for all these three files. So it starts with an answer it or similarly in the regency it is Same starts with a an answer, though. So this is a structured data on a table. So this is important for us to work with power equity, especially when you're a pending the reports and concentrating your table. That is exactly what we're gonna do. So for us, luckily, the structure is same. That be easier and wouldn't be much problem at all. But engage your reports and your later table specially is kind of reports are not in same column structure. Former. It is quite challenging to work this kind off reports in power query and concerted them to have a consistent little table. It is not impossible at the same time, but it is difficult. And you need to know at once courting like M court, which is a formula language of power Credit before our structures are saying for all these three reports and also for the Edisto, the region's report, which gonna be adding division e and division d, the report structure the same. In the practical scenario, if you're working for our company or form most of the accounting systems and TRP systems or any kind of business is legend, system will have a structure reports like this. So the room US problem in getting this kind of reports in a consistent and structure former . So let's go around with exercise or building the dash. Thank you very much for watching 61. 61. Consolidation with Power Query: Hello and welcome to this video. In this video, I'll be showing you how to concentrate their target. Hello, Power query. First of all, you need to download Parker. He added You for Excel 2010 and 2013 you need to have a partner install with help up Excellent in which is a free at in you can download from Microsoft Website There are two words of Parker which is 32 which is a shrubby to need to know your words. You know, Faxon. So I'm gonna excel 2000 pen pro Plus So I go toe file and I go to help and you can see it I have ah, Terry to beat off Ah, professional 2010 axles and I need to download and in which already downloaded it and it's installed. It's a comment in here to go to your options like this, go to options and Goto added, go here and select comedies like this. You will see Microsoft Power carry for Excel and took click. Ok, so this is how you're gonna install your power credit at it. Once install, you'll see arable like this automatically add it with your other tables, like former Loretta, I will say, a power period for 2000 time and also power credit here, less important data. So in Parkway retired here to go to from file from folder and you can browse the folder. My newly like this, I would rather suggests, Just copy the full apart based right here like this and click. OK, so this is our fall apart, which I copied, and Mr Here, and you can see whatever the day ties there in that folder. The files are appearing here. Let's click at it now. We don't need any other column other than this content column, because this has star data, so I'll rightly can contain column and remove their column. Now let's name discredit two more appropriate name like corn. So he and now I will click this combined files pattern, which is down arrow like this double down arrow and let's see what happens. You to select a sample file because all our files are structured in exit, same manner, they should work just fine. We have to make sure that whenever you try toe march their top from various files, the structure of the columns is same. So for our case, everything is same for the regional division B indigency. So let's click. OK, now Parker has created some additional steps. You don't need to worry about the step. This is done automatically in the park ready engine. So we're just like this. Transform from Conso. File this particular credit and let's hide it. And let's let's start our process off cleaning and transforming this data. First of all, you to remove these three rows, which we don't need, and to do that, let's go to home. A move more top pros and harmony rose. You want to remove three. Now those three rows are gone, and now let's promote this first riser header. So to do that, go to transform. Use first Roy the header and let's unpeeled this particular later because this stage we want as a column not column heading. So before we do that, let's remove this year Today call of you don't need that and I'll click Cord and Description column right click on people. Other columns. So other columns were date and amount, which is value here, so let's can work this particular data type toe a date. Former now it's proper date this to our tax and that he named. It's called a month and you can also change it to currency. You really want subheadings from our penal statement which doesnt have court. So fortunately, the data set is preparing such away wherever we have a sub totals like direct labour cost never knew. As you can see here, he doesn't have court right? The Cordillera having only against the actual jail court in this popular loss treatment. So we can easily go here and select only null for the courts regarding leader subheadings. Now we don't need this court columns writes Remove it now. We don't need this net profit and gross profit percentage also because people table won't be ableto put a calculation on it and the proper way. So we have to do that. Emanuel Turo Calculation, which I have shown you in the dashboard. So that's removed that GP percentage and that profit percentage. Okay, now our data set is ready. Now we have to goto our main credit, which is Conso Penal now were to remove this change type so we get ah, data which we manipulated in that particular credit transfer from from sample. So here only problem is the data format is not converted properly. So let's do that. Changes to tax this do date and this to a current. Now we also need a column for the divisions. Right. So here you can see the date eyes there, but we don't know for which division. So we needed a column for the now to do that. Well, to goto this second step here in this credit Conso pl remove their columns, click on this gear I can select name. We chose the name of our file which also has the division court. Now again, Come here it with this step. Remove other columns One and select name you can see. Now we have the column for the division back that's closed This I would call it the illusion And we don't need this pln XLs name. So we can remove that. So right, click a place values. Oh, there's a moody space as well. So relate. In war, nothing click OK and extreme this to tax. So goto transformed former and trim on here we can name it one hand. So are the test that is ready for three divisions exigency A, B and C in the power credit. This is completely dynamic. And in future, if you're more divisions are shown you in our demo video The first video, it will be added automatically without any average. Let's do a close and lord for this so close and Lord to our table the new work. Shit. Now our table has come here. 62. 62. Dynamic File Path Trick in Power Query: before we go for the let's save this file. So I already saved this file as, ah, power Credit Dash Sport. And you need to do that same before we go further if you haven't done so. And that's changed this formatting display for day 2 14 more. Zero on forward like this and for amount less changing to accounting. Let's create a dynamic file part time data folder, part for disk ready. So if I take you back to the credit window and we go to source, you can see here the folder parties hard critic. Remember when we have imported eight out from from file and folders, we pasted the exert folder part off our files Division A, B and C, which is this? And this particular for the part was hard coded at that time, right? So in case you want to move this particular solution this entire for the rocker dashboard and little folder to another location or you want to email it to somebody else, that particular person whenever it opens. So whenever you yourself opened this particular dashboard folder with Power Kredi, it was showing other. Now, this is not a disastrous that are you lost everything. You just need to remap that particular ah fuller toe your new location, right? So it's very easy. You just have to just browse it and select that the tough for that again. But here you're preparing a completely dynamic solution. So there is a way around to do that which is called using parameters and is more off at once in power credit. But if you following with me, I'm sure this will really help you. And if you know this trick, you can create your particulate solution complete dynamic, and you can send it to anybody. Or you can move it without any other popping up whenever you opened in the new location or somebody has opens your part of the solution. So let's try to do that. I'll go back to the Excel and here I'll use sheet one. Name it to see Panel, which is a control panel now being in a table called parameter from our existing completed solution, which is here. I'll explain you what? This perimeter table is so here. I'll just based it here. This particular table is called parameter table and I will just rename it toe bad I'm return and it has two rows and two columns. So first Roy's name file part and second row is named right off order in this column And this values derived from our complex left cell and filing function. You can see here and this data files particular hardcore did. Value is nothing but name of this folder. Call it if I so fear any other name like source data or any other name, you need to select it. Do after a on your keyboard, copied and come here to this particular control panel table and paste it and put ah, this kind off hype on it. The back back would happen it like this. But this particular values coming from this left and cell function, this is nothing but a complete folder part who had this particular file is located. So this file Power Kredi dashboard is located here, right? Is located in this to you, Dr X Account being one job except pro. And you, Jamie courses, prepayment course and pique your dashboard. The exact values being dynamically pulled here with the help of this particular formula. So here to select this particular table now goto power Kredi and select table or range Relocate a Curreri out of this table. So it is now ready. It is called parameters, which is fine, because we're gonna change this names anyway. So first thing you need to do is select filed park like this and go to these values a record here and you were drilled out so nor drill around it is regarded as the exact value from that particular record. So let's name this too. Five. But like this and that's duplicated. And name me two data for Xcor to this filter Rose in this data folder, Kredi and Select create a fuller value here. So if you go to value here and you're tuffelli to get data files, which was the name off your this folder? Very. Our broader ties. Right. So now we're gonna use this particular names file part and a towel folder in our for their part here in source. So instead of this G X accounting is long, I thought we're gonna be using file part in it off holder tax. So I I filed part and for the right irritable and percent it because you want to join this together, you can see there is no other. Now the file part for this particular folder is pulling from this particular two credits. The values from this trail don't values right. So before we go for their let's corporate this and go to this sample file and go to source here also the or for their part is dead, which is not dynamic based. It here and now we're done. So our entire solution is what completely dynamic. Let's go back to excel. You can former days if you want I removed again. 63. 63. Conditional Cumulative totals: Now let's add a community will amount column as well. Any totals in park wh