Organizing sales data - Microsoft Excel Tutorial

- [Instructor] When it comes to doing market research in the real world, anyone dealing with this area needs to be able to take data generated by the business and then clean up that data for use in market research analysis, including things like understanding and estimating the demand curve. Let's look at how that process works. I'm in the 01_03_Begin Excel file. Now, what we see here is data from a firm related to all the sales they've made over time. This particular company, a fictional firm that we call RedTech30, provides a variety of different high tech products including drone blueprints. Now, what we see is the firm has two major products that we're looking at in this particular case. Product 102 and Product 101, which are blueprints for two different drones, the Bsquare Blueprint and the All Eyes Drone Blueprint. And we see, as you'd expect from most sales reports, the level of sales on any particular day for a particular product. So, on 9/1/2017 as an example, the firm sold 10 copies of the Bsquared Robot Blueprint at 8.99. On 9/6/2017, the firm sells 21 copies of the All Eyes Drone Blueprint at 4.99. What's important here from an understanding demand perspective is that the firm varies the price that it charges over time. If you only ever charge one price for a product, if you never experiment and move that price up or down, or offer sales or discounts to your customer, then there's nothing to estimate with demand. We only know what we could sell at that particular price. Which course begs the question, are we charging the right price? Out of all the possible prices we could have charged, was 4.99 exactly the right price to charge for this particular drone blueprint? Probably not, right? As in so much of marketing, there's testing and iteration that is required when it comes to understanding what the right price is. Now over time, if we scroll down, we see that the firm has charged different prices. In order to go through and organize this data a little bit better though, we need to isolate the particular product in question and then the prices that have been charged over time. So, I'm going to go ahead and I'm going to sort all of my data by hitting Control + A to select everything, and then coming over to the Data tab and clicking Sort. And rather than sorting by Order Date, I'm going to add another level, and I'll sort first by the Product Number and then by Order Date. This way, we'll go through and separate. And now we see all of the Product101s and the dates those orders were placed. Then if we scroll down, we see all of the Product102s and the dates those orders were placed. So, this is a good start. We've gone from simply having a mass of different products all lumped together, even though they're not related to one another, to having just individual products grouped together. Next, we need a way to go through and understand what our total sales are for any given period of time. Say, for a one month period. So, I'm going to use this last column L, and I'm going to create a month indicator variable by using the month formula. So, I type =month open parenthesis, and then I'm going to go through and pick the order date in question that I'm interested in. So in this case, my month is going to be based on the order date that a particular order occurs on. When I'm done, I click Enter. And if I do this all the way down the entire column, this will let me go through and ensure that I've got the correct month for every single order. So, now we see all of the orders that occurred in month 12, in month 11, month 10, et cetera. This is nice not only for kind of grouping orders together, but also for understanding seasonality, which is a key element in understanding demand. Most businesses face some level of seasonality. For instance, you might see orders pick up in the fourth quarter as we get close to Christmas. Either way, we've now gone through and done some basic organization on our data. We've put our particular types of products together, and we've grouped them based on the month in which those orders occurred. Now, you should have a better understanding of what it takes to go through and start building a holistic data set that'll help us to estimate demand and the appropriate price for our product. Try putting one together based on your own data, just as I've shown you here.