1.1 A Working Model for Data Analytics
Learning Objectives
Establish the relationship between data design and data analytics.
Define the DATA Analytics Framework.
Identify the role of data audits when using an existing dataset or spreadsheet.
Define the concept of data internal controls for shared spreadsheets.
Review the complete Data Analytics Model.
Highlight the limitations of data analytics.
Excel is perhaps the most widely used analytics and decision making tool for both professional and personal tasks. A critical success factor for getting the best results from Excel is to establish a working model for data analytics that can be used for any project. Data analytics is not just about crunching numbers. It is a comprehensive process that can change the way you think about decisions in both professional and personal situations. The Data Analytics Model shown in this chapter will establish a process that will be used throughout the textbook.
What Is Data Design?
Follow Along File: None
For the purposes of this textbook, data design The data that exists in a spreadsheet and the way in which the data is arranged. will address the data you have and how it is arranged in a spreadsheet. The design of a spreadsheet will have significant ramifications for completing any analytics project using Microsoft Excel. There are many benefits for storing data in an electronic format, many of which will be explored in this textbook. However, it is important to understand that the way in which this data is entered into a spreadsheet will dictate what analytical tasks you can perform. This is not to be confused with data presentation. Data presentation The way in which data is presented or visualized to an audience to explain circumstances and decisions in professional settings. is the way in which data is presented or visualized to an audience to explain circumstances and decisions in professional settings. Data presentation and visualization will be explored later in the textbook. To illustrate the importance of data design, Figure 1.1 shows a simple spreadsheet that could be used to analyze sales data for a small company. Suppose an analyst using this data has the following goals:
Evaluate at least two or more years of sales data to decide when the company might need to borrow money from a bank.
Decide if the company has sufficient inventory depending on sales trends.
Figure 1.1 Data Design Example
Show long descriptionLong Description
Picture of an Excel spreadsheet with the title in row 1 Company Sales Data Fiscal Year 2018. The spreadsheet contains the following four columns of data from left to right: Month, Unit Sales, Average Price, and Sales Dollars.
The Data Design Example shows that data and how the data is arranged can hinder the ability to accomplish the goals identified for this project. For example, the first goal states that the analyst would like to analyze at least two or more years of data. For this project, the analyst will likely accumulate data in a spreadsheet for a few years. Placing the Totals row (Row 15) at the bottom of the dataset is an inconvenient choice because blank rows will have to be added above this row when new data is added to the spreadsheet. In addition, as more data is added, you will have to scroll down to the bottom of the spreadsheet to see the totals, which is also inconvenient. Placing a Totals row above the dataset would be a better choice. Also, notice that there is no column to designate the year for each month. This column would have to be added to the spreadsheet if more than one year of data is going to be analyzed. Finally, the second goal states that the analyst will need to evaluate the inventory needs of the company. However, there is no inventory data in the spreadsheet. This data will need to be added in order to complete the project.
The DATA Analytics Framework
Follow Along File: None
When using Excel to work on a data analytics project, there are many data design choices that have to be made. Establishing an analytics framework for a project will help you make these design choices and ultimately help produce the best possible outputs in Excel. While there are many views as to what steps should be taken to conduct an effective data analytics project, this textbook will simplify the process into four steps which spell the acronym DATA. Each step of the DATA Analytics Framework is explained below.
Decisions: What decisions do you intend to make as a result of the analytical outputs? Depending on the project, whether personal or professional, certain decisions need to be made. For example, in a professional situation you may have to decide how much inventory to purchase, how many people to hire, or how much money needs to be raised to start a new company. In personal situations you might want to know how to improve your personal spending habits, or how much money is needed to buy a house. However, not every project will result in a concrete decision. For some projects, the decision may be to conduct more research or analysis based on a discovery that was revealed by the analysis.
Acquisition: What data must be acquired to inform the decisions you plan to make? Once you know what decisions you are trying to make, you will have a better idea of the data that is required to inform these decisions. For example, if you are trying to decide how to decrease personal spending, you might need to collect all the key items that you spend money on such as rent, utility bills, food bills, etc. In the Data Design Example (Figure 1.1), the analyst needed to decide if the company was maintaining the proper amount of inventory. Therefore, inventory data for the company must be acquired.
Time: Over what period of time do you intend to collect and analyze data to support your decision? The purpose of this step is to determine the amount of data that will be required for a project over a certain period of time. As in the Data Design Example, if the analyst is intending to study a few years of sales and inventory data, the spreadsheet should be designed so that it is convenient to add new rows of data. Totals and summaries of the data could be placed above the dataset or in an entirely new spreadsheet.
Analysis: How will you analyze the data? This is perhaps the most complex component of the framework. This will require additional project specific questions to be asked. For example, in the case of a personal budget you might ask “Am I spending too much money on restaurants?” or “How much money do I spend a month?” These questions can lead to a few analytical possibilities. Each month we could analyze how much money is spent on restaurants as a percent to total spending for the month. We might also look at how much money is spent on restaurants for the year and see what months make up the highest percentage of the annual spend for this category. This step of the framework helps to identify calculations and analytical processes that need to be conducted to produce the most relevant outputs that can inform the decisions being made.
The DATA Analytics Framework will serve as the central component of the overall Data Analytics Model and should be used at the very beginning of a project. A summary of the framework is shown in Table 1.1.
Table 1.1 DATA Analytics Framework Summary
| DATA Analytics Framework |
|---|
| 1. Decision: What decisions need to be made? |
| 2. Acquisition: What data must be acquired? |
| 3. Time: Over what time period, or how long will data be collected? |
| 4. Analysis: How should the data be analyzed? |
Conducting a Data Audit
Follow Along File: None
The use of the DATA Analytics Framework is relevant for projects that are started from a blank spreadsheet and when working with spreadsheets that are already created. Professionals often begin a data analytics project with a dataset that has been given to them on a spreadsheet. The DATA Analytics Framework is especially valuable when working with existing spreadsheets as it provides the necessary guidelines to conduct a data audit. A data audit The process of evaluating the design, scope, and integrity of a dataset to ensure reliable outputs can be produced to inform decisions. uses the DATA Analytics Framework to evaluate the data and the data design of an existing spreadsheet to determine if the data can be used to accomplish the goals of an analytics project. Data audits also verify the scope of the data in a spreadsheet and its integrity to produce reliable outputs. Data audits are especially critical when working with big datasets as it is not possible to visually inspect the data for errors.
Integrity Check
Data Audit
It is critical to conduct a data audit on any existing spreadsheet you did not create or on any dataset that is provided to you. The DATA Analytics Framework should be used to guide the data audit to verify the scope and validity of the dataset before conducting any analytics project. Failure to conduct a data audit may result in erroneous outputs and poor decisions.
Establishing Data Internal Controls
Follow Along File: None
It is common for professionals to create Excel spreadsheets that will be used by other coworkers. In fact, several professionals may contribute to and use the same Excel workbook using the OneDrive cloud network featured on Microsoft Excel 2016. While this creates a very efficient way to build comprehensive datasets that can provide significant decision making benefits, it can also lead to errors and possible data corruption. When you create a spreadsheet for an analytics project, you will know what can and cannot be done with the data. If you do not have a way of communicating these potential pitfalls to your coworkers, you increase the risk of your analysis producing erroneous outputs. Placing data internal controls Instructions and features added to a spreadsheet that decreases or eliminates the risk of an analysis becoming corrupt or distorted by errors incurred by other users of a shared spreadsheet. into a spreadsheet decreases or eliminates the risk of your analysis becoming corrupt or distorted when other people use your spreadsheet. The DATA Analytics Framework should be used to identify what data internal controls are needed in your spreadsheet. The fourth step of the framework helps you identify how data should be analyzed to produce the outputs required for making decisions. The conditions that exist in each project provides guidance as to what data internal controls should be in place to prevent another person from distorting or corrupting the outputs in your spreadsheet.
There are many tools that Excel provides that can serve as effective internal controls. For example, if a project contains many complex calculations that should not be tampered with, those cells can be password protected. If necessary, entire Excel worksheets or workbooks can be password protected. Some data internal controls can be as simple as typing instructions or warnings at the top of a spreadsheet. These data internal controls, as well as several others, will be explored throughout this textbook.
The Data Analytics Model
Follow Along File: None
The four components of the working model for data analytics that have been defined are the DATA Analytics Framework, data audit, data design, and data internal controls. The DATA Analytics Framework can be viewed as the engine that provides guidance as to how a data audit should be conducted, how a spreadsheet should be designed, and what data internal controls should be established. The diagram below provides a visual depiction as to how the DATA Analytics Framework informs these three components of the Data Analytics Model.
Figure 1.2 Data Analytics Model Diagram
Show long descriptionLong Description
Reading the model from left to right, the picture shows the open end of a funnel with the description DATA Analytics Framework. The side of the funnel shows the description Data Audit. The funnel is projecting onto a circular Excel spreadsheet. In the middle of the spreadsheet is the label Data Design. On the top and bottom edges of the spreadsheet is the label Data Internal Controls.
Limitations of Data Analytics
Follow Along File: None
While data analytics can be a powerful tool when making professional or personal decisions, it is not a perfect system. The analysis and corresponding decisions you make are only as good as the data you have. If there are problems with the validity or accuracy of the data used in a project, the outputs will not be effective in making good decisions. Another limitation is that data is always changing and therefore difficult to predict. For example, we don’t know what the price of a certain stock will be tomorrow, next week, next month, etc. The same goes for sales in any given company. Data analytics can help us make good guesses as to what we think the data might be in the future, but we will not know for sure until the data becomes a historical record. Most data analytics projects begin with data that occurred in the past. It is this past data that can help us predict the future. This is often referred to as predictive analytics Using data analytics to predict what might happen in the future. . If the data used for a certain project is mostly consistent over a long period of time, the results of predictive analytics can be very reliable. However, data that drastically changes over time can completely change the results of a project that are used to make decisions. It is these extreme changes that are very difficult to predict. However, we can still evaluate these potential changes by conducting “what-if” analyses. For example, someone analyzing a stock portfolio might conduct a scenario analysis to see how much money would be lost if a stock were to fall 20%, or 50%. The results of this analysis may not help to understand if a particular stock is in fact going to drop a significant amount, but could establish a risk profile that could lead to other decisions being made to minimize losses in case that scenario were to occur. Even though there are limitations as to the predictive powers of data analytics, it can still produce valuable information that can help you make good decisions.
Key Takeaways
The data you have and the way it is arranged is critical to successfully completing an analytics project.
There are four steps to the DATA Analytics Framework that should be used prior to each project: What decisions do you need to make, what data do you need, how long will you analyze the data, and how will the data be analyzed?
The DATA Analytics Framework is the central component of the data analytics working model and informs data design, data audits, and data internal controls.
You should always conduct a data audit on a spreadsheet that you are using but did not create or on a dataset that has been provided to you.
To prevent corruption to your data or analysis, data internal controls should be added to your spreadsheet if it is going to be shared with other people.
Data analytics is limited in predicting drastic changes that might happen in the future.
Exercises such as “what-if” scenarios can be used to understand risks that might occur if drastic changes occur to the data you are analyzing.