Transcripts
1. Promo: financial and data analysis is an indispensable part of corporate life today. How do you approach analytic work? Do you get confused? Do you get anxious and panic? Do you become despondent when analytic work fails to impress through adopting sound analytic processes and practicing good habits? When doing analytic work, you'll be able to regain your confidence. YouTube will be able to excel at performing financial in data analysis, introducing habits for excellence in analysis, a set of highly effective and easy to enforce habits that professionalize your analytic work. The most important aspect of doing analytic work is knowing the context, meaning knowing the why the who the what? The winds off the analysis, your clear understanding of the context. We ensure that you are on the right track. Next, you'll need to set up the analysis. This involves having clear and consistent filing system, including folder and file structures and use of consistent color and text formats. You need to know what input items you need and have to work with. You also have to plan ahead at this point as to what type of a model you want to build. Next, you'll be building in an Icis, you will construct pragmatic and robust formulas. You want to test your model and check your results every step of the way, and you want to be sure to documentary analysis adequately, so in appropriately skilled person can repeat the exercise. When she obtained the output from the model, you need to consider whether your results Havel the desired attributes such as validity, consistency, presentation of the results should be appropriate for the audience and be effective. And finally, with newly gained inside, refine your analysis, the return it of refinements.
2. Intro: welcome to my course on habits for excellence in analysis. In this course, I will introduce to you a set of highly effective and easy to enforce steps that professionalize your analytic work. Upon the completion of the course, you will have learned about the four elements of the analytic process. I will introduce to you a number of steps in each of these four elements that have performed habitually. We'll upgrade your work from entry or mediocre level to professional level quality. For example, you will see that your analysis will become more effectively answering the question. You will perform the analysis more efficiently through planning ahead in preparing the necessary elements upfront. Your work will be more structured, making it easier to follow and check what was done or to make subsequent modifications. If necessary, the analysis will be more accurate. The result will be more effectively delivered. There are four chapters in the course context preparations. Building an output. Each chapter contains sections that break up the content into easily digestible chunks. Each section then begins with a video that introduces the key principles, followed by videos that illustrates the application off principles. Three examples. Although the examples are illustrated through spreadsheets. The principles introduced are universally applicable to other platforms. They are approximately 40 minutes of videos. Each chapter also provides a poster that summarizes the key steps introduced. You should print these posters and pin them up next to your world computer so that each time you perform analytic work, you can quickly review and then apply these steps. The course is aimed at beginner to intermediate level analysts who performed data, statistical, epidemiological and financial analyses.
3. Context: let's begin by going over the most important step of the analytic process. Understanding the context. This is most important element of doing professional analytic work. Getting this first step of your analysis right is critical has this will ensure that you will move in the right direction and answer the right questions. If there were ever a silver bullet to turn mediocre analyses into great analyses, a correct and clear understanding off the context will be it. What do we mean by context? Understanding the context of an analysis means you know the lay of the land, knowing the four W's the why the fee, the what and the winds of the analysis the why at the onset of any project, make sure you understand the objective explicitly state the objective. Writing it down often gives you more clarity. Don't assume anything. Test your understanding of the context with your manager or client to WHO who refers to your manager, your client and your colleagues. No, this style and expertise of your audience. The consumers of the results of the analysis may have very different levels of expertise or interest than you do so you should design your analysis and communicate your results at a level of sophistication that is appropriate for your clients. Get to know your colleagues on both a professional on a personal level. Specifically, you should learn about your colleagues strengths and weaknesses. This will help you operate more efficiently within a team. The what? What refers to the subject matter being analyzed. Make sure you have adequate knowledge of what you're analyzing. If not, learn quickly. If you do not believe you have enough time to gain the understanding necessary, say so you will gain que doors when you gracefully decline, work or get needed support early on. But you'll lose all your credibility when the analysis force flat because you failed to deliver the When those who ignore history are doomed to repeat it. Invest the time to learn about historical developments of the project. This will help you avoid known pitfalls and take shortcuts. No, the current deadlines, as well as the history of the project be disciplined and stick to your timelines. Also, to ensure continued valley rod, try to align your analysis with future plans so that your analysis continues to be relevant . The importance of understanding the context cannot be overstated. This is the anchor of the rest of the analysis. So he invest the time up front to gain as much understanding as you can about what you need to do at each step of the analysis you insure what you're doing is necessary, efficient and correct through checking against your understanding of the context. The only stupid question is the unasked one.
4. Context example: throughout this course. Each section begins with the video that introduces the principles and habits. Then the concept tress introduced will be illustrated through an example. The example will use is the business evaluation analysis for business will call. What a Treat Chocolate Factory. Please note that the example was created to illustrate the principles relating to good habits for analytic work and should not be viewed as an actual business evaluation model. Let's take the situation where David wants to sell you. His chocolate making business were to treat Chocolate Factory, he says he is willing to sell it to you for 100 $50,000 he tells you you'll also need $50,000 per year for operating expenses. He says he has had no problem selling 17,000 units per year at a dollar 73 per unit, which generated profit for him. Furthermore, David or provide sales data as well as production related costs to you, you know, have to get a better financial understanding of the business and create a model to value the future income less expenses off this company. You want to know how much profit you expect to make off the $150,000 investment and compare the profit percentage to other alternative ways to invest that money. So that sets the scene. Let's see how the elements of the context can be extracted the wine. The ultimate goal of this analysis is to understand whether the returns from the investment is adequate. Well, while the element of secondary to this primary objective, the Who you're an entrepreneur and has the experience in building financial models, David, your friend, is a dedicated chocolate here but is known for sloppy operations and careless mistakes. The what? You'll be building a business evaluation model to assess whether the income less expenses had they expected. Sales volume generates enough returns from this business. You've done similar analyses in the past, you know. Production costs and sales volume might be the important factors to get right. The win. You need to reach a decision in two weeks. You have no past experience in making chocolate. As you identify these elements of the context, write them down in the first tab of your model or in a word document. I found writing things down, forces you to verbalize concepts, making abstract concepts clearer and easier to remember. Please know that throughout this course, supplemental materials are attached to each section. For example, click on the button to the top. Right of this video, you'll see the water treat chocolate factory cash flow model that will work through together.
5. Filing system: in this section, we introduce the habits that will help you set up your analysis in a clear, consistent and logical fashion. First, consider the filing system. You need to use a clear folder structure. It's important to use this for all your projects. So you and other people know how to navigate your work folders. We recommend the following structure in the Emails folder. You keep all important emails to contract the development of the project. For example, Project Kickoff instructions Assumption Setting emails on final delivery emails should all be kept here in Finished products folder. You keep the final outputs and exhibits along with all the necessary files to recreate the results. All data sets Assumptions should be stored in inputs folder you sub folders If there are too many files. Working is a folder in which you work. During the analysis upon the completion of the announces, this folder should be cleaned with only the relevant files moved into the final part of folder. The consistent and logical far layout is another aspect of a good filing system. This refers to how your sequence that different tabs in your spare cheat we recommend that you set up tabs from left to right, separating by function. For example, you could use a sequence such as notes, inputs, assumptions, calculations and output color code the different clusters of taps to make navigation easier . The third part of the filing system relates to the use of tax and colorful Mets. You should use consistent tax and colorful math to God yourself and other users. This is not merely for cosmetic purposes. When you get used to a certain way, specific types of tax look, you're finally easier to spot problems. For example, use italics for comments. You specific formats distinguish dates, texts, whole numbers and monetary amounts in terms of color coding. Uses red team decay. Potential problems. Black for formulas, blue for imports such as data, an assumption and yellow for incomplete items. Remember, make it your habit to consistently use a clear, logical filing system. This helps you and your colleagues to navigate your work
6. Filing system example: So now that we have discussed the principles of a good filing system, let's see how it looks in action. Before we start doing the chocolate factory analysis, we need to ensure that we're using a good filing system, starting with a folder structure. In this example, we will break out files into e mails. This folder contains all relevant emails, including emails from David. This avoids irritating retrievals of archived emails and also allows others to follow the discussions and see major decision points. The final names should reflect the name of the sender subject and date of the email inputs . The raw data This photo contains all the inputs used to perform the analysis, including input data. Such a sales data from David Sources of Assumptions, for example, costs of production for the chocolate working files. This fuller contains the files used during the analysis process, including the most recent version of the work files. The working progress versions of the business Evaluation models. Finish the durables. This photo contains deliverables with sub folders. By date of delivery, finish the liberals, including documents, spreadsheets and pdf's. The idea is you want to keep as much of the working files as necessary so you can reproduce the results. Also, you should keep the delivery email for record keeping. It is important to know that not every folder structure must look exactly like this example . Folder structures should be created to best 50 users needs one so photo structure is set up . We'll consider how the file taps should be laid out. Here. You can see the tabs are color coded. Ah, well sequenced, Clearly laid out consistently color coded spreadsheet is an indication that the person who worked on it was thoughtful, careful, structured and forward thinking it is reviewers, as color coded tabs allows anyone to instantly know how the model is structured and how the tabs relate to each other. The color scheme used in this example is as follows Yellow is for context or notes on the file. This provides a concise summary of what is in the workbook. Blue is important. Assumptions clearly lays out the assumptions. No the sources for each assumption and make notes and comments so you can track your thought process. Light blue is for calculation taps. These are broken out by type of analysis to help reviewers follow your steps green is a results or exhibit top. Here, you present the results of your analysis. Some tips are to use clear labels, pleasant and clear formats and necessary caveats. This stamp should be print ready. Next, we'll discuss tax formatting. You can see in this tab that there are a few different kinds of tax formats. If people are not used to your workbooks, keep a description of what your color scheme means. This helps review is and future uses to know how the model works. I recommend attacks. Color scheme is as follows Manual user imports, including data and assumptions, should be blue. This will tell someone new to the spreadsheet where to focus their intention. If they're looking to very far assumptions in other using inputs, regular formulas should be black. Critiquing formulas requires a different approach than checking assumptions. Special trouble formulas are called out with Brett use only when necessary. This highlights a special critical formula that reviewers should be aware off links to other times and workbooks are called out with brown and finish formulas. Inputs are called out with yellow highlights. Lastly, let's look over text formats. It's important to set appropriate format for all tax and numbers. This will facilitate easier understanding and avoid mistakes, said appropriate funds consistently throughout your workbook set date and time. Two decimal places retained decimal places only, if necessary, distinguish between percentages and ratios. He was a talented in our comments by users. It's shown negatives as minus 5000 and not 5000 in brackets. In the next video, we'll discuss the principles of building a model.
7. Calculation principles: up to this point, we've gone through how to develop a clear understanding of the context. How to set up the work folders 100. Decide on the structure of the model you want to build, how to gain a thorough understanding of the necessary import items and assumptions. Now, as you start building the model and writing formulas, a number of principle should be kept in mind. The most important is that you should aim to keep your formula simple, simple formula so easier to write a check and modify. Do you only what is necessary to achieve your objective? Given the context, nothing more. Keep your formulas short and break up long formulas. Well written formulas also tend to be adaptable. Adaptable formulas could be easily modified to fit other purposes. This is a useful attributes because you were likely modify your formulas multiple times before arriving at the desired result. Specific examples of writing adaptable formula smart involved never hard coding formulas using flexible summation function. Such a sum ifs. Instead of rudimentary additions or building error correction mechanisms, some minor errors in the data are taken care of. The formula should be transparent so that your thought process. It's clear to a fresh pair of eyes. You should leave the trailer comments and sign pose to explain what you're doing, highlighting important assumptions and steps in the formulas when writing formulas into right consistent formulas within the tap and a cross tabs, for example, keeping the formula the same in a continuous row or column. Also try to use the same set of formulas in the same table structure across Taps Company in the same set of formulas across taps is both quick and error resistant when writing formulas aimed to automate repetitive manual operations. When you feel like you doing mind numbing work, chances are they are ways to simplify and automate what you're doing. Macron's Air Great help. Be sure the analytic processes well defined before use. Macros, also automating, does not only have to do with macros, a suitably set of model structure could also reduce repetitive operations will demonstrate these principles in our example
8. Inputs/Assumptions: at the outset of the analysis, you need to have a clear idea of what data and assumptions you need, given the context and your objectives. You also need to understand what you have to make sure that the import item to use a relevant correct on well documented. The first question you should ask is What do you need? Asking the following tops of questions may help you clarify what you need. What am I analyzing? Do I need to know changes over time, in which case static data on one point in time will not be sufficient? Do I need to project forward? Which means I need assumptions about the future? What did I expect the drivers of the experience to bay and cannot measure these in my data ? How accurate do I have to be what was done in the past? Always check against the context of the analysis to see whether you have missed something. Is it really necessary at all times? You should check whether what you're doing is absolutely necessary. Keep it simple. The more complex, the easier it is to make mistakes, fight the urge to do what you like to do, and do what is needed when in doubt, all someone with more experience, whether what you plan to do is necessary. Confidential information such as names, Social Security numbers, commercially sensitive information should be given appropriate levels of protection. Be proactive and Archer, manager for data protection policies and methods, read these policies and do what they tell you to do. Getting sued is not fun. Make sure you know your data. This is very important. You need to confirm your understanding of the data, either with a person who provided you the data. Or if you obtain the data yourself test will. Your data is correct. You'll save a lot of time by identifying problems. Now assume nothing. Document your understanding. Ideally, there should be a data dictionary that explains the source of data and the caveats. This will make it easier for yourself and others to pick up your work in the future. Carefully set assumptions focus on getting the most important assumptions correct. Cross reference your assumptions through multiple sources instead of taking from just one source, it said. Reasonable assumptions. What is reasonable depends on the context, but whenever possible or on the side of caution for example, you want to overestimate assumptions such as expenses, time to launch inflation and underestimate items such as revenue, profit, margin and growth. As you gather the assumptions, documentary sources put documents such as What Pages and Data Says into the Inputs folder.
9. Input/Assumption checks: when picking assumptions and input data, you should be diligent with understanding and checking the input dater. I've learned through many hard lessons not to blindly accept data or assumptions because these input items and assumptions are the first steps to you analytic process. You want to make sure that these air relevant your analysis and that they're correct. With an example we obtain to set of input items from David sales data and production costs from sales data. We're primarily looking to verify the sales volume that he suggested the 17,000 per year and the per unit price dollar 73. We need to confirm whether his production costs are reasonable so we can have a realistic projection. So let's take a look at that. You set of items in term. It's stocked with those days have received from David. So this is what it looks like. Firstly, you need to understand what the input data means so you can see order number dates, find I D units per sale, cost of sale as well as attacks. Next. You want to make sure this data seems reliable. How you would do that varies depending on the input data in our situation, you probably want to look for extreme items such as overly high cost of sale unit costs that are unreasonable, etcetera. So let's go ahead and take a look. A copy David's data into the second tap going down the items ordered 21 seems far too high . Order Number 35 appear to have a very high number of units again, with a number 73 seems to have a sales number very high. So on so forth through doing the checking, you would have identified a number of out liars, which you note as potential bad data. So let's go on to summarize that this data, firstly, we're going to summarize all the data. This gets us almost 18,000 units of sale and the unit cost off dollar 73 which is in line with what we expected. However, after removing the error cells, you see that the sales volume drops to 16,300. This is the type of checking that you need to do on your input data. Next, we move on to the assumptions. David provided us a number of production costs items. We don't have experience making chocolate, so let's refer to industry reports to verify Davis production costs
10. Model structure: we're preparing for your analysis. You should also consider the structure of the model you wish to create planning. At this point, with help, you create a model more efficiently. Firstly, you need to decide which suffered to use. Each tool has its strengths and weaknesses. For example, Excel is great for trade and complex models that is no very powerful in crunching large dreams of data. Access is better for mechanical data, Management Steps, says. An SPS says are great for crunching large amounts of data but requires more knowledge of the software. Use the right tool for your project. A logically inconsistent flow will also help you navigate your model. This flow relates to how data changes between different software tools. The sequencing of taps mentioned in the previous section and how formulas between each tab is created. Divide and conquer. When you find that the model you're creating becomes too large to manage, break it up into bite size chunks, Drawing a simple picture that depicts the flow of your model often help to break down abstract aim office concepts as much as you can. You should aim to automate repeated tasks if the process is well established and repeated regularly. It is probably a good candidate for automation steps, such as data extraction. At certain times of the month, we're generating similar reports for different clients, often great candidates for automation here, every common types of model structures that you were used in your analytic work. Segregation is when you perform simple calculations on the input data to calculate output measures such as averages and counts. Tracking is when you calculate output measures up on the input data over changing variables , such as time. This type of model typically involves calculating up IT measures based upon interest of additions of data. For example, tracking the profitability of monthly sales projection is when you take historical data and project forward into the future. This obviously requires setting assumptions about the future scenario. Testing is a model structure in which output measures can be calculated subjects of changing key input variables. What is quiet here is robust model to allow such changes, input, variables and a clear and concerns way to represent and compare the range of outputs
11. Model structure example: our example is a business evaluation. What I'm analyzing is the future cash flow off the business specifically, profit over a number of years as measured by income, less expenses. The model needs to take a starting point, which is based on historical experience and forecasts what the future will look like given some exemptions. In other words, I need a projection model. When setting out to build a model, you should try to imagine what elements of the model will be and which key results are needed. Then think about how the elements connect. To get to the results during a picture might helps. With this in mind, Let's see how I built a model for a chocolate factory example. Foods obtained the sales data and production cost from David. Next, I calculated a production costs based on David's input. These three elements for my starting point Dan and performed monthly projections with the first year and then annual projections for subsequent years, based on a number of assumptions on costs and growth, etcetera. Finally, a set of results of gathered and shown in the last tab. Because I'm interested in the break even year, the model had to contain results for each year so that the first year of break even is recorded. Also, my prior experience of doing business evaluations told me sales and production costs are likely going to be important. Factors build a model so that the impact of variations in both volume and production costs can be easily calculated. These are examples of a heart knowing the context and the central question of the analysis . Inform how the model is built. Noting such considerations upfront ensures you build a model that answers the question. It saves you from wasting time modifying the model again and again. If you're junior analyst, be humble. Asked more senior analysts about whether the model you plan to build makes sense do. There's early on in the project. This will show that you're responsible, thoughtful and almost certainly saved. Wasting time later on
12. Dos: the previous video introduced the principles to building can letting models. Now we discuss a few specific dues are good habits for bowling models. You should have weighed using multiple workbooks as much as possible. Try to condense all you need to do into one work work. This reduces the need to link to other workbooks and makes it much easier to follow what is being done and to check your work in. Sure, there's a logical flow in each tap and a cross tabs. The items of calculations should go from left to right and go from top to bottom. You should use similar formula structures across tabs, although our example is fairly simple. In situations where similar analyses needs to be repeated multiple times having the same formula structure, it makes writing and checking of these formulas much easier to the extent possible. You should make it very easy to plug in input data. This means minimum modifications required on the input data before it can be pasted into the model. This makes updating very easy break up long formulas, always trying to keep things simple. If a long calculation is unavoidable, then break it are being to smaller components, writing formulas be cautious and use dollar fix cells referencing were necessary. This is obvious, but avoid pasting formulas blinding without ensuring that all the formulas are doing what you intended. You should aim to use only one type of formula per row or column within a table. Changing formula softly through is difficult to spot, and I easily missed in future updates. You can use control, told her to look for breaks and formula patterns. Leave a blank row before summation formula. This is both visually appealing and computation. We safer use name rangers if there are a lot of assumption items. This makes cell referencing easier and more intuitive. Use the trace dependent function to identify how cells are used by other cells. This insurance that you spot linkages between cells, building checks as you work. This should be done as you work, and not just something you do once you have some results. This ensures that your diligently revealing your work and that your catch critical errors that they appear this conserve you from being led down the wrong path. Explain formulas inwards. Leave a trail of comments. Do this both for documentation purpose as well as to make sure that you verbalize what is being done. This forces you to think twice about what you're doing. Lastly, remember, they use spreadsheet of farm protection where necessary.
13. Donts: the previously do introduce the deuce of model building. In this video, we'll tell you about a few. Don't. There's no knows that you should have weighed at all costs. Long, complicated formulas are big. No, no, remember, always trying to keep it simple. If the formula is complicated, see if you can simplify it. If its complexity is necessary, then break it up into smaller components. This formula is very long. It's complicated looking but simply intention. It's merely trying to total of the sales for one client. It also missed some entries, so it is incorrect. Instead, it's better to use functions such as some myths, which automatically sums and is applicable when new data is entered. Do not hide rows or columns. Hidden rows of columns are easily for gotten and can be overlooked or mistakenly included in the four calculations. If it's no necessary, don't include it. If it's not something you want people to see, don't put it in the model. If it is necessary and not something people need to see, then use the data grouping function. Rather, this leaves the rows or columns tucked away from side. But there is still a sign that remind you so that rows and columns or not for gotten or mistakenly used hard coding numbers within a formula is a major? No, no. It is very hard to spot during checking and subsequent updates. It also defeats the primary point of modelling, which is to see the impact of results when assumptions who input items change hard coding does not allow such flexibility. Instead, keep the hard coded numbers separate from the formula and in blue here units hours produced this hard coded, which is better entered in the formulas of arable land. Left on the assumptions tab, try your best not to change formula in a continuous bro column Again. Doing this will be difficult to remember, and I easily glanced over and forgotten about and pasted over. If you have to use to times of formulas, either break them up into two separate columns or rows, and Murder Gang will use a different color coding to indicate a breaking consistency. Here there are three cells representing changing your formula. I have kalakota them in yellow to highlight the break. Try your best to keep one set of assumptions. In other words, do not have the same assumption appearing has an import more than once in the model. Multiple locations are hard to track, and future updates might easily miss duplicate entries elsewhere. In the model, you know, model the assumptions. TAP has entries, each showing once, and this is only place where these assumptions are appearing. Limit extended links as much as possible. External links slow down your model. External models also easily can be moved renamed, leaving a Marley limber, instead pacing the entry and in comments No to follow a path for yourself. Also, remember the color coded a perfectly.
14. Calculation checking: to air is human, has the analyst. You should be aware of the potential impact of errors and apply the appropriate amount of caution in error. Checking This is especially the case for junior analyst whose nose smell out potential model errors has now been trained. The risks of a spreadsheet model depends on many things, including complexity and size of the models, frequency and type of use, the number of users and developers. It's especially important to test and check the model if the output from the analysis may have significant impact on the organization. When checking assumed that the model is wrong until proven right, assume that they all heiress and resist becoming complacent over time. Remember how embarrassing it is when someone else points to an obvious error you had made. When testing and checking sweat the important stuff, focus on the most important aspect of your model, he ordered. To do that, you first have to know what the important aspects of the model are. These might be input, assumptions or specific aspect in any formulas or your model structure. Test the output sensitivity to various aspect of your model through sensitivity testing. Make sure you get these important aspect of a model. Correct. You're checking. Must be methodical. Else you will likely leave stones unturned. Checking should be done as you work. This ensures that you're not led down the wrong path because of errors and save you precious time while checking. Do this in a sequential fashion from checking big picture contacts, the understanding to model structure, two inputs and assumptions, and then the specifics within formulas, you should record your tracking. This is to remind yourself as well as others, that you have been diligent in checking on work for the most highly visible work. You wanna have a senior colleague review your work? Here are a few times of checks that you can do checker input data through data scrubbing methods. For example, checking Minimum Maxima doing simple pivot tables for label king consistency as well as comparing to past data. Triangulate our assumptions. You must know which factors influence your results the most. If you don't find out, verify the most important assumptions. Triangulate through research and with more experience, analysts note your sources as well as checking process. If you cannot verify in an assumption item state, so let the audience decide whether the results are still reliable. Remember, you would rather point out weaknesses in your own analysis, then appear as though you were trying to hide them back of the envelope type of calculations, bypass the complicated formulas and can often indicate potential problems with your model. Try to break the model by setting inputs to figures or formats that may cause errors to the formulas. For example, putting a cell to zero or too extreme values as just a model, so that alert you to problematic inputs. Important pieces of analysis are typically printed out, so printed report to make sure it looks good. Check the spelling. Make sure formats color funds are consistently and logical. Check the figures in your report for consistency with past results. Any major differences should be explained and investigated as potential errors spell check everything. This is an obvious one. Yet how many times of easing obvious spelling errors and immediately started thinking less of what you were reading
15. Checking example: in the previous section. We talked about the importance of performing, testing and checking. While we work in this video, we show you how testing and checking was done in our chocolate factory. Example. When checking assumed that the model is wrong until proven right, the first step we took was to check the sales data for errors. Remember, we identified the marked entries that appear to be out of the norm and excluded thes from subsequent calculations. We also check that the average cost per unit of chocolate produced was $1.38 throughout the projection period. These are simple examples of our checking is done while working when testing and checking sweat the important stuff focus on the most important aspects of your model in our example the most important aspect our input costs and volume. Since we have a set prize to sell the chocolate at this main intuitive sense, because we have fixed prize, so I'll cost the production has a direct impact on the profit. We also very fighter assumptions as we went along. Also, we know we have a fixed cost component, so the larger the volume, the higher the prophet. Since we could not verify the cells. Volume resorted to testing the sensitivity of the rate of return to changes in sales volume and concluded that the rate of return does not become unsustainable if their volume drops to 14,000. If you did not have an intuitive sense of the prophet drivers, although the model is too complex, you should rely on sensitivity analysis to test which drivers are the most important. At this late stage of the analysis. You should also take a step back and ask yourself whether the model structure was reasonable. Given the context. The monthly projection is relatively simple in this case and answers the questions fairly well. You could also try to break the model by putting in outrageous numbers for example, Selig zero units or having input costs such as eggs costing $150 per dozen and see whether the results change, as you'd expect. You're checking must be done methodically in a sequential manner. We started by checking on input sales data. Then we checked our input assumptions. Then we checked our calculations against expected average yearly costs. You should also check your formulas as you write them, to make sure they are correct and had done what you wanted them to do. Finally, we test their output using sensitivity analysis every step along the way. Ask yourself where you could have gone wrong. Does this look right? You should also record your checking. As you can see, we left a trail of simple comments that highlighted what we checked and the outcome of the checking. Important pieces of analysis are typically printed out, So print out your report to make sure that it looks good. Check this felling. Make sure formats color funds are consistent, clear and logical. You should check your results against past records if they are available. Spell check everything and correct any errors. In the following section, we introduced the practice of documentation.
16. Calculations documentation: which he ever taken it, polls from a bottle that had no labels. Similarly, you need to document your work. The most important thing to remember regarding documentation is that it is an integral part of analytic work. While you may initially think it is tedious and boring, you should always document your analytic work. Documentation should be done while you work. You may be tempted to say, Oh, I'll just do it when I'm done with the delays Defeat. The role of documentation is a concurrent self review, and sometimes the delays turning to Nevers. When you do the documentation while we work, you're more likely to be diligent and ask more questions. Your documentation should contain enough information for an appropriately skilled person to repeat what you did. This is as much for someone else to critique what you have done, as it is a reminder to yourself of what you have done. Trust me in six months time, you will not remember what you did. Documentation could be done in multiple places as long as you do it consistently. The documentation file is a file that contains all the major elements of your analytic work and should enable and appropriately skilled person to repeat what you did head. We include folder references, assumption references, major model designs as well as key iterations of your analysis. You should keep all important input items as well as emails for future reference in the workbook Used Notes tab to keep eye level documentation. Also, you should leave a trail of comment to sign. Pose in the model to indicate your thought process. When boating the model, we'll go through a documentation process in our example.
17. Documentation example: the previous video introduced the rationale for doing documentation and some practical suggestions of where documentation can be done. Now we illustrate how we documented our methodology in our example. Keep in mind, the purpose of documentation is to record enough detail of your thinking so that inappropriately skilled individual can replicate your work to start off. We drafted a simple documentation file. This file contains the essential elements of the analysis. For example, dates, purpose data received, general analytic approach and output. This is intended to give any user an instant view of what the analysis is about. For more complicated analyses, you will want to record more detail on the analytic process, the fire path as well as enough information so that iterated version changes are clear. As you do your analysis, you should have this documentation found open so you can record what you're doing while you do it during. This also performs a concurrent self review. Similarly, you can use a notes tab in Excel to list these essential items as we did here. As you start developing the analytic model, you should Sprinkle sign posts and sailing bit of information, so it's easy to follow what you're doing in the model. In our example, we started with a context tab which listed the purpose and background of the project. When we analyzed the sales data recorder where we found in subsequent add its we did to the data in the inputs tab, we noted the source of each item and how we verify the each item wherever necessary and practical. We documented the formulas on the results tab re tabulated the output and noted our findings. Specific findings should be recorded and written in italics. Lastly, we should mention that, as is the case for other elements of this course, there isn't one way to document your analysis. You should record your analysis in a way that you are most at ease with. However, when you go about doing your documentation very mind that documentation is an essential element of the analytic work. It should be done as you work and issued contain enough details so that inappropriately skilled person can repeat your work in the following section. We'll introduce the habits related to our puts
18. Output of Results: At this point you're approaching the end of the analytic process. You will hopefully have a set of results in order for your analysis to add value to your employer or client, there are a number of criteria that your results should satisfy. Your results need to be valid and consistent. Your results need to firstly, onto the question. The question, of course, depends on the context of the analysis. At this stage of the analysis, you need to review again that your understanding of the contacts is correct and that your analysis meets the objective. Given the context next through your checking, ensure that the model was correct also ensure that the result is consistent with past results. Any major discrepancies need to be explained. The results also should be understandable. As always, keep your results simple. Show only what is necessary. The way you communicate your results to the audience should be at an appropriate technical level that the audience appreciates use language that is easily understandable. Your results should be concise and contain only what is necessary to meet the objective of the analysis. Everything else will need to track from the central purpose of the analysis in Suria analysis is action oriented. No matter how sophisticated your analysis, the results need to lead to some action. No one will come back to you time and time again for academic studies that do not lead to real change in someone's work. So give your analysis towards some actionable results. Your results should be well documented. Remember that sometimes people will not go through the remainder of your analysis and simply jump to the results section, even though your analysis has a more complete documentation elsewhere, your results, tables and exhibits should contain a few key assumptions and include adequate caveats so the audience can decide whether they can rely on your results.
19. Results example: the previous video introduced the habits relating to generating and interpreting results. Now we will illustrate how we apply those habits in our example at the end of the analysis . For what? A treat chocolate factory. We have a set of results. We have total outlays versus final gain, break even year. Also a set of sensitivity analyses to show how the profit margin changes with changes in sales volume and input costs. There were a number of criteria that we introduced in the previous video. Let's remind ourselves what they were. Your results should be valid and consistent. They need to firstly, answer the question and should be consistent with past results. Your results should be understandable. The results should be concise. You need to ensure that your analysis is action are rented. Here is art should be well documented. Let's review whether our results satisfy the list of criteria. The central question, given our contacts for the chocolate factory analysis, is whether the rate of return is adequate for Sami Smart guy to buy the business. He would also benefit from knowing a few other items, such as How do other factors influence the rate of return and When will he break even? Do our results? Answer these questions? Let's see. We know. Given an outlay of $200,000 the yearly rate of return would be 18% under our current set of assumptions. We also know that break even will be in Year three, and there the sales volume and input costs will influence the rate of return. Since this is the first time we analyze the chocolate factory, consistency to pass results is not relevant. We should also consider again whether the model structure was best suited to answer the question. I would say the monthly and yearly projections we did answer both the rate of return as well as time of break even questions adequately, are your results understandable? The results are laid out clearly with highlights and comments to guide interpretation. Since Sami is a savvy business person, he should have no problems interpreting these relatively simple terminologies. Are the results concise? I think so, since we show only what semi need to see everything on the page is relevant, our results action oriented, I would say yes since based on our results, Sammy conduce side whether the rate of return is adequate. Whether the break even time is too long on whether he is comfortable with the risk he is taking on. If the sales volume and input costs change our results well documented, I would say so since we recorded our analytics steps. So overall I would say our results meant the objective of the analysis. In the following video, we introduce habits that aid the presentation of your results.
20. Results Presentation: At this point, you comfortable with your analysis and the company results. You'll not present your results to the client, the presentation maybe in person, your report or just a simple email. Whichever method of presentation you should bring mind of following your presentation should be at a level that is appropriate for the audience. Use language they can understand. Keep just the amount of detail that audience appreciates. You should keep a presentation simple and transparent. Rule of thumb is to keep the presentation below 20 minutes maximum say only what is necessary, but ensure that you highlight all critical components of the analysis. People associate better with stories that hard numbers. So Carvey analysis into a story, starting with explaining context. A simple narrative of your analytic process, culminating in a concerns summary of the results. Try to align your presentation approach with the past. No one likes surprises, so give people where there used to such a simulated formatted results. Tables will side by side comparison to pass results. Make sure your report and tables print out nicely, actually print out the report and catchall the silly mistakes that your spelling mistakes, inconsistencies or unreadable funds and format errors. The next video we'll illustrate how this was done in our example
21. Results Presentation example: Now let's move on to the final step of your analytic process. The presentation. Whether this is dining person on the email, you should ensure that you know what you did. This may seem trivial, but there might have bean significant gap in time between when you did the analysis and when you're presenting the results, so you should review the work before delivery. You want to be well prepared and published when you go present to the client to recap. Ideally, when presenting results, you should such a presentation at a level that is appropriate for the audience. Keep your presentation simple and transparent. Try to tell a story in the Nahr. Just hard numbers. Try to keep your presentation consistent with past presentations. Make sure your reporter tables print out nicely. So in delivering the final results to Sammy, I word used the usual business analysis language. Since Sami's of savvy business person terminology such as break even point and rate of return should be familiar with Sammy in other situations. When the audience is not well versed in the jargon of the subject that you have just analyzed, you should spend some time defining these terms, ensure the audience is on the same page as you are before delving into the numbers. This is a relatively simple analysis, so I would aim to keep the presentation to less than five minutes. I would explain to send me the following where we got the assumptions while we use monthly and yearly projections over 10 years, and that we estimated the input cost for each bar of chocolate to be $1.38 with the retail price off $1.73 that they expected sales volume is 17,000 units per month. Given these assumptions, the rate of return per year is expected to be 18% with break even in year three. We should also point out to Sami that the input costs is the biggest risk to the rate of return and that should the input costs me 20% higher than $1.38 the reader return will drop to 11%. Instead of just telling Sami the hard numbers, tell him the results in a story through a sequence of events that led to the result. For example, you could tell Sammy how investigated both the volume as well as the input caused to decide that input costs carries the most risk to him. Also, you can throwing anecdotes such as David Do No good, having a good grasp of the input costs, but were oblivious to the calculation errors he made with the sales data. Often, business leaders are looking for qualitative information in addition to these hard numbers . And quite often there will be things that you do no one recorded on paper. So you should rely on telling thes anecdotes to relay useful information for your client. Since this is the first time you analyze the chocolate factory, you should have no past presentations to tie back to. What you can do is try to be consistent with generally accepted forms off business analysis so people can focus on the content and result of the analysis and do not have to waste effort getting used to the way you're presenting the information. And finally, you should always print out the results page to ensure that it looks nice and has no typos . The next video were introduced habits relating to iterative refinements of your analyses
22. Analysis refinements: Finally, a great analytic model tends to mature over time. We know he's done with the current iteration of the analysis. Be prepared to its relatively refine the model to meet new requirements with new insights. Vision control at this point is important when such attractive work takes place. Keep adequate information in the documentation fall to record interest of changes and the rationale for each change. So go for it. Apply the steps that you have learned in this course in your work and see how the quality of your work improves.
Edd Jay, Expert in financial and data analysis
