4.5 Design a Budget and Forecast model - Video Tutorials & Practice Problems

<v Instructor>For the remainder of this lesson,</v> we will use a story-based learning approach in order to design, develop, populate, and use a budget and forecast model. At a high level, we will go over the following activities during these lessons. Review the scenario and define our problem statement, identify stakeholders and gather requirements, design a layout for our solution in order to deliver on the requirements, and then based on the layout, we will identify appropriate data structures to be used, and we will determine how our formalized may be copied, moved, or filled within our model so we can include appropriate cell reference types, finally, we will populate data within our model and generate some insights and analytics. During this sub-lesson, we'll be focusing on these highlighted activities. The remaining activities will be discussed sequentially in the following sub-lessons. So with that, let's begin. For this scenario, you're an experienced data analyst who has been recently engaged by an executive you have worked with in the past. Emily is the new CFO for a well-known bike manufacturer. She has spent a couple of months in the new role and has identified a few process improvements she would like to implement. One of the first opportunities she wants to tackle is improvements related to the budget and forecasting process. Talking to her direct reports, she has come to know that a significant amount of effort is spent by one of her teams to provide weekly reports for other business units. One of the most common requests is for the revised sales and operating expense data. Managers within the business unit use this data to forecast the revenues and operating expenses for the year and to create and maintain their budgets for the next year. Providing an efficient, reliable, and sustainable solution for this repetitive request can free up considerable time for Emily's team. She also wants to consolidate the budget and forecasting process within her team for consistency and enhance reliability. Since Emily has worked with you in the past, she is confident that a relatively simple Excel-based solution can help to deliver the desired benefits. About a week ago, she had asked you to discuss this further with the stakeholders and prepare a relevant proposal. Now let's review some of the findings. Talking to Emily, you have identified the need for an efficient, reliable, and sustainable solution that includes the following: a categorical summary for the revenues and expenses for the current and two prior years, a consistent forecast for the current year's net profit, a flexible template to generate budgets for the next year by combining a variance input with historical data for each category, and finally, some visual aids to summarize the results and highlight some predefined data insights. For this opportunity, you engaged a few stakeholders over the last week, including Emily, the CFO herself, an accounting manager who's responsible for recording sales and chart of accounts, two accounting technicians who generate the weekly reports, representatives from the business unit who consume the reports generated by the technicians and also provide budget input for Emily's team, and then a couple of teams within IT to discuss database access and security approvals. Based on your discussions with these stakeholders, you were able to document the following requirements for your solution: import sales expense and budget data from the corporate data warehouse, prepare an income statement template with the revenue categories and totals at the top followed by operating expenses and then the net profit at the bottom. Next, forecast the net profit for the current year by prorating year to date actuals beyond the analysis date. This state should be set up as a manual input for the model and will also be used to limit the sales and expense data imported from the data warehouse. For budget generation, each category of revenues and expenses should have the flexibility to include a percentage or dollar amount variance with one of the following base values: average of actuals for last two years, actuals from last year only, original budget for the current year, or the forecast generated for the current year. Finally, the model should provide visual aids to highlight by category the variance between current year's budget and forecast and the current year's budget and next year's calculated budget. Other visuals requested by the stakeholders include: a category called breakdown for the current year actuals for revenues and expenses, a comparison between budget and forecast for the current year by category, a similar comparison for the actuals from last year and the forecast for the current year, and finally, a graphic comparing budget for the previous, current, and next year. So equipped with a summary of her findings, let's begin by designing the sheet layouts within our model. These layouts are a rough drafts for how our content will be structured on the worksheets within our model. They are a critical design component when complex Excel solutions as a well-designed layout will be useful both for the data analyst and the end users. While such layouts enhance productivity for the analyst, they also allow the end user of the data package to follow a clearly articulated story by presenting the data sources, transformations, and insights in an easily consumable form. So with that, let's begin. So here I've already created the worksheets that we'll be using for this model. In addition to the standard worksheets that we discussed earlier in this lesson, like info, ref, and input, I've also created some model-specific worksheets for the source data, for preparing data for analysis, and finally, report pages to summarize our results and other data insights. For this scenario, we will assume that we've already used the get and transform capabilities discussed during lesson two to import our source data into the Excel tables created on the sheets with the SRC prefix. However, I would like to highlight some calculated columns that we have added for these tables to optimize our data processing activities. For our budget, sales, and opex tables, we have calculated the year corresponding to the date column for each data set. For our sales data, we had to include an additional step to calculate the order date formatted column. This column was required since the date format in our source data used a forward slash, while our system settings included an hyphen for such format. Due to this mismatch, Excel was not able to recognize the values in these columns as dates. So in this case, we split the source date string into the year, month, and day components, and then supplied them to the date function for generating the corresponding date value. With the data input completed, we plan to combine our source data with the other inputs we have set up on the input sheet to prepare our data for analysis on the prep budget sheet. And then finally, summarize the results onto the RPT budget sheet. For our current exercise, we will use a step process to design our prep sheet and then discuss the layout for our report. For our current exercise, we will review key elements of our prep sheet design, and then discuss a proposed layout for our report worksheet. First, for our prep sheet, let's go over some design-related requirements that we identified from our stakeholders. One of the primary requests was to lay out our revenues, expenses, and that profit data similar to an income statement. As you can see, we have replicated the desired structure with this layout. Our revenues are listed first, then operating expenses, and then net profit at the bottom. We have also included the totals row for revenues and operating expenses. Now, this requirement alone determines the vertical layout for our prep sheet. For the horizontal data flow, we should probably consider which data points are now required for each of the categories listed on the left. Reviewing our requirements again, we can identify the actuals, the actuals for the current year and the two prior year, budget for the current year and the previous year, a forecast generated for the current year, which can be aggregated to get us the net profit forecast requested by the stakeholders. Next, for each category on the left, the users also wanted to have an option to specify a percentage or dollar amount variation for a predefined base value. For this requirement, we will have a dropdown selection for the users to select a base metric, which we can then use to retrieve the corresponding base value. For example, average of actuals for the last two years, the current year budget, and so on. The user can then enter their variations for this.. The users can then enter their variations for this base value in column M or N. We can then use the selected base metric and the entered variations to calculate our budget for the next year. We can also use conditional formatting to highlight the variations between the current year budget in column G and forecast in column I, and between current year budget in column G and the next year's calculated budget in column P. In addition to satisfying our user requirements, we will display some additional information related to our input data so the users have it accessible with the output generated on this page. As discussed earlier in this lesson, such practices can help to identify any input data issues that may lead to misleading results. The specific information we'll list here is: the analysis date, current year and budget the year in the top left, and the numeric year values within the header of each column that references previous years, the current year, or the budget year. We have also used some cell styles and color schemes within this layout. For example, all field titles in the top left use the cell style SCL-FieldTitle01, the level one column headers use the cell style SCL-H1, and the level two headers use the style SCL-H2. Additionally, the manual entry fields use the cell style SCL-Input, while the dropdown fields use one of the templates from the format sheet and are highlighted in light red. Next, we have added some notes to annotate the functionality of some fields. For example, the forecast column header includes a note for the proration approach for this calculated field, and the dollar change column header includes a note that specifies that this field overrides the percentage change field. As discussed earlier, these notes will be easily accessible for the end users and will thus introduce transparency for our data transformations. And finally, we have used the built-in outline feature to group similar data for budgets, forecast, and budget variances. Later on, we will apply similar outlines for the revenue and operating expense categories on the left. Now, moving on to the design for our report page, we have a total of eight visuals that are required for the requested data insights. The eight visuals can be broken down into four pairs with one each for revenue and operating expense categories. For the most part, we would want to keep the pairs together preferably in a similar layout as our source or prep data. In this case, that would mean that we should have our operating expense data below our revenue data. At the same time, we need our visuals to be legible and avoid using a white format for our page to prevent any printing issues for this report. To get around these constraints, we laid three pairs of visuals side-by-side, and then for symmetry, we split the remaining two visuals across the width of the other pairs. Since this is a budget and forecast model, we would want to maintain the focus on budget values. And as such, we display the corresponding visuals at the top of the page. For each visual on this layout, we have also evaluated the type of data being summarized and the allotted size for the specific visual to then determine the type of Excel chart we can use. Please note that although we may change the support layout or the identified charts later on, this template is extremely useful to propose a design for our stakeholders and gather appropriate feedback. So now that we have discussed the general design for our model and the sheets with them, let's move on to the next step where we identify the required data structures, functions and cell reference types.