1. A half-baked idea
You have added to your toolkit for understanding how error and uncertainty affect decision making. Now let's put your skills to the test with a case study.
2. Data overview
The examples and exercises in this chapter will use sales data from the Risky Business Bakery. The fundamental question we will try to answer is whether changing our prices will significantly affect demand and increase our sales.
Our dataset contains the following columns or variables:
Transaction ID is the order number in the point-of-sale system.
The "Day" column refers to the day of the week on which the sale occurred.
The "Date" and "Time" columns indicate when the item or items were sold.
"Item" refers to the food or beverages the customer purchased, and
"Price" is how much they paid for it.
3. It's all adding up
Before we jump into the exercises, let's review a few useful functions and concepts.
Recall that the SUMIF() function allows us to add up cell ranges if cells meet certain conditions. This is sort of like filtering the data. The SUMIF() function takes three arguments:
The range, or the cells to which we want to apply our "filter",
The criterion, which is the filter to apply, and the
sum_range, which is the range of cells to add up.
4. It's about time
In this chapter, we will also analyze data based on dates and times. For example, we might want to know what time of day our bakery is the busiest to see how we might increase our sales.
In Spreadsheets, data that are stored as times range from 0 to 1, with
0 corresponding to midnight and
1 corresponding to the last second of the day. Each time can be stored as a decimal between 0 and 1,
for example, 11:04 and 6 seconds (in the morning), corresponds to around 0.46, and
noon corresponds to exactly 0.5, which makes sense if we think of a day being represented by 1. We can use this information to help understand when sales are most frequent.
5. A UNIQUE() challenge
Lastly, we can use the
UNIQUE() and
SORT() functions to get a list of the unique values in a given range. You might use these functions for extracting the days of the week, or the list of items that were sold during a given period.
One final note, we will calculate percentages in these exercises to see what proportions of our sales come on different days or for different items.
To do this, recall that we divide the individual amount by the
sum of all included amounts.
6. Let's practice!
Now that we've updated and refreshed our toolkits and oriented ourselves to the data, let's jump into the exercises to cement our skills.