The Lemonade Stand: An Elementary Case for Introducing Data Analytics

Often times in business, one of the accountant's primary roles is to provide information to answer business questions or assist management in making decisions. Twenty years ago Borthick (1996) stated that database concepts should be part of the accounting curriculum. More recently, Norman, Rose, and Rose (2006) argued database concepts are critical skills needed by accountants and auditors. Professionals have also acknowledged that database concepts are important skills for accountants. For example, McCollum (2002) stated, “All auditors are expected to be [users of] a variety of SQL tools.” Fordham (2005) developed an AIS concentration at his school in response to firms which recruited at his school. The firms had expressed interest in students with “knowledge and training” in system skills.

More recently, AACSB and the Pathways Commission encouraged accounting education to increase the business knowledge, analytical skills, and data analytic skills of accounting students. This case blends these areas in a single case. While it is important for students to possess the technical ability to extract data from databases, integrate multiple data stores and use sophisticated software tools (e.g., Tableau, R, etc.), it is equally important that accounting professionals in training learn to assess the business needs driving the use of integrated data stores to produce quality information for decision making.

A lemonade stand business is a context that many students in the United States can relate to and understand with minimal discussion in class. The scenario in this case makes it relatively easy for students to join a class discussion about what information is important to run the business as well as provide examples of business/accounting reports. Schafer and Hurt (2006) use a lemonade stand business context for designing and creating a revenue process system. While an elementary business plan, a lemonade stand business as presented in this case provides a solid foundation of learning and applying technical skills for data analysis before advancing to more complex business scenarios.

This case is not intended to serve as a comprehensive case at the conclusion of a semester, but rather to serve as an introduction to data analytics. By integrating discussion of business reporting needs, querying and graphical presentation, this case is intended to serve as a foundation for developing greater skills in data extraction. The users of this case can expand on the basic skills introduced in this case with more advanced business scenarios, greater complexity of data repositories, and more advanced use of data extraction and analytic software tools.

Data analytics can be used for fraud detection of many types, but this case focuses on utilizing data analytics on improving the business. Likewise, a carefully designed graph can help a business understand why there are tremendous fluctuations in daily sales. The learning objectives are stated next, followed by the student case materials, and case efficacy.

Upon completion of the case students should be able to:

• 1) understand the business from a narrative description

• 2) understand the underlying data structure

• 3) develop queries to answer business questions

• 4) import external data (weather data) and integrate it with the business data to better assess business operations

Before beginning, obtain a basic understanding of MS Access and either QBE or SQL queries. As mentioned in the Business Scenario, the accounting records of the lemonade stand are housed in a relational database. MS Access knowledge is necessary to view the data and create queries and reports as output for decision making and report generation. Individuals new to MS Access should consider using Taylor (2015) as a starting point to learn the basics of MS Access software. In addition to reference books available at a local or online bookstore (e.g., Barnes and Noble or Amazon), some academic resources are available on professors' websites or in academic journals.

Two children started a lemonade stand to earn some extra money at a local country club/golf course over the summer (June, July, and August). The golf course is open every day (weather permitting) except Mondays. The golf course built a lemonade stand structure with an ice making machine and secure storing of inventory. Then, the golf course signed an agreement allowing the children to operate the lemonade stand on the golf course for a rental fee of 5% of sales. In the agreement, the golf course provides electricity and water (for their computer and ice machine).

The children were excited to offer 19 flavors of lemonade. They purchase the lemonade in individual containers (12 ounce cans) from the local grocery store. For weekdays (Tuesday through Friday), one child works for a fixed wage ($75 per day). On Saturday and Sunday two children are scheduled to work each earning a fixed wage ($75 each per day).

One of the children has a parent who had recently gone back to school at the time the children were starting the Lemonade Stand and was taking a database course. One of the assignments for the course was to design a database. In order to complete the assignment and help the children, she offered to design a database so they could keep track of inventory and sales. To track sales by employee(s), she set up three shift categories: one for each child when working alone and a third shift for the weekend when both children work. She also designed an invoice for the children to enter sales (see Figure 1 below).

The sales are recorded at the time of purchase. For each sale, the employee enters the member number, flavor(s), and the quantity of each flavor for the sale. A paper sales receipt is signed by the member to acknowledge receipt of the goods and kept on file. The country club/golf course has members who have a membership card that works similar to a credit card for purchases at the club house, pro shop, and at the lemonade stand. The members are billed at the end of the month for purchases that month. Therefore, the lemonade stand must submit a monthly report detailing sales to each member to the club. The members remit a check directly to the lemonade stand under terms net/30.

Cash receipts are recorded by an employee when the payments are received. The record of receipt includes the date, employee ID, amount of the check, which bank account the check will be deposited in, the golf course id (only one location at the current time, but they hope to grow their business), and the member ID.

Every Monday, the children's parent takes them to the grocery store to purchase their inventory. Currently, the children mentally estimate both the items and quantity of inventory needed (create a grocery list). As needed, they buy cups and paper towels. The weekly grocery bill is paid by the parent. The trip to the store causes some frustration for the parent as the trip typically includes a long wait while the children decide what and how much to buy. At the end of the month, the children pay their parent for the sum of the month's purchases of inventory and provide a list of total sales and cash receipts recorded/deposited for the month.

Reflecting on the above business scenario, demonstrate an understanding of the business processes and reporting needs of the business both internally and externally by answering the following thought questions.

• 1.1 What are the reports specifically required of the system?

• 1.2 What reports would management want to have from this system?

Review the structure of the lemonade stand accounting data (Figure 2) and answer questions about where and how we can extract data/information from the system.

Where (which tables) would you find the data needed to answer the following questions? For example, in 2.1 you would need the “Member” table (within the table a count of records provides the count of members).

• 2.1 How many members/customers does the lemonade stand serve?

• 2.2 How many flavors of inventory are there? What are the names of the inventory items?

• 2.3 Based on the tables, do you think all lemonade sells for the same price? Why?

• 2.4 Does the system allow for discounts? For example, do high volume customers receive discounts?

• 2.5 Will the system allow the business to expand to multiple locations?

Open the access database “LemonadeStand Student.mdb” and use the database Tables indicated to answer the following questions:

• 2.6 Member – How many customers/members are in the database?

• 2.7 Sales – How many sales have occurred (recorded in the database)? Include date range

• 2.8 SalesLine – What information (fields) are in this table? Why is this table in the database?

• 2.9 Inventory – How many types (flavors) of inventory are listed?

For the following questions, save the queries in the format “question #_Your_Name” for example, the first query would be saved as “3_1_Your_Name”.

Basic Queries (single table queries) – use MS Access to answer the following questions:

• 3.1 The children would like to be able to look up and quickly find a member's number. List all members' first and last names, member ID number, and order them alphabetically by last name (Lname).

• 3.2 How many (count of) cash receipts were received in August 2013?

• 3.3 What was the total cash received in August 2013?

• 3.4 What was the total cash received for the entire year of 2013?

• 3.5 How many flavors of inventory are in stock?

• 3.6 List inventory flavors on-hand from highest to lowest.

Intermediate/Advanced Queries (multiple table queries) – use MS Access to answer the following questions:

• 3.7 What are the total sales in dollars for the company for the month of June 2013?

• 3.8 What are the total sales in dollars for the company for the entire year of 2013? Hint: A query can be built using both tables and other queries. A query calculating extended price (price*quantity) for each sale line item is provided to assist in this and other queries. The query is named “ext price”.

• 3.9 What amount was sold to each member for the month of June 2013 (list from the highest to lowest amount)?

• 3.10 What were the cash receipts from Members in July 2013 (presumably for June sales) (list from the highest to lowest amount)?

• 3.11 What is the balance owed by each Member (Accounts Receivable balance) at July 31, 2013 (before billing for July) (list from the highest to lowest balance)?

• 3.12 What is the 2015 ending Acct. Rec. balance for every member listing the highest first?

• 3.13 Who is the best customer by volume (display only the best customer)?

• 3.14 How much did we sell (qty and $) of each product in June 2013?

• 3.15 What were the top 3 flavors in units sold for the month of June 2013?

• 3.16 What were the 3 lowest flavors in units sold (and list those not sold)?

• 3.17 Are there any members who have not purchased any lemonade? If so, list their name(s).

• 3.18 List the total sales units and dollars by shift (employee) (for all 3 years).

• 3.19 List the daily sales units and dollars (for all 3 years).

• 3.20 List the minimum, maximum, and average daily sales by employee.

Part 3 Learning Checkpoint:

• 1) Revisit Part 1 (understand the business from a narrative description) and consider if the queries answered your questions. Also, consider whether the queries included all questions, and what the queries would require to answer questions not included in Parts 2 and 3.

• 2) Business thought questions:

  • Is the company likely to have cash flow issues?

  • What happens if a member does not pay? Is that an issue?

  • Why might sales differ from day-to-day?

Table 1 shows some descriptive statistics of the childrens' performance. EID 1 represents one child working Tuesday/Thursday alone, EID 2 represents one child working Wednesday/Friday alone, and EID3 represents both children working together on the weekends. Answer the following thought questions.

• 4.1 What possible explanations might exist that lead to the variability in sales for each shift?

• 4.2 How might daily events/tournaments/weather impact sales?

Suppose we find general consensus that weather might impact daily sales. Many items like temperature, rain, or even cloud cover could impact sales. You can use weather data to confirm or disprove this hypothesis. However, what should be done if it is determined that weather does impact sales? As a valued consultant, you can provide useful information in the form of identifying the weather components that help to predict low sales.

To complete Part 4, 1) acquire external data (weather), 2) perform a query to match like data (i.e., weather file has all days of the week while the company is closed on Mondays), 3) extract the query from MS Access to a spreadsheet file (typically, data is extracted from the accounting system into a file format readable by the receiving application), and 4) use Tableau to graphically evaluate various factors that may affect sales. Specifically, the steps are as follows:

• 4-1 – Acquire external data (weather). In practice, similar to what was pulled from the National Weather Service, data typically requires making sure we understand what is provided and that it is in a format usable for our purpose.

  • Open the Excel “weather” file and become familiar with the data (See Table 2 below).

  • Determine which fields to import (all or delete the unnecessary fields/columns).

  • In practice, data files typically need to be cleaned before use. Become familiar with both data files to note if the data is compatible. In this case, days of weather include days the company is closed. Additionally, the weather file has several issues that need to be addressed before the data can be used. First, the date column is in numeric format. Second, rainfall has a “T” for a trace of rain.

    1. Date column issue: the number (ex. 10613 = 1^st day of June, 2013) will not be read by Excel or Access as the desired date. If we use the Excel cell format to change from numeric (general) to a date format, it will incorrectly change the date to 01-29-1929. To fix the date column, we suggest inserting a column. Title the column heading in row 1 (“date”), and then set the format of rows 2, 3, and 4 to date (ex. 3/14/2013). Then, in rows 2, 3, and 4 of that column, type 06/01/2013, 06/02/2013, and 06/03/2013 respectively. Highlight those three cells and place your cursor at the bottom right of row 4 cell. Then, click and hold the left mouse button and drag down slowly to the end of the August 2013. Repeat for 2014 and 2015 summer dates.

    2. For the rainfall (WTR) column, scroll down the column and replace all “T” values with .01. (The “replace” command can be used for this step. Alternatively, a student could write a formula to convert.)

    3. Note the WX column identifies special weather events. See coding in Figure 4. In the data the most common are 1, 2, 3 and 8. If a day has a 138, the weather service noted 1 = fog, 3 = thunder, and 8 = haze. If a person wants to compare weather features to sales, the coding would need to be split into multiple columns. (This is not needed for our analysis.)

    4. Save the spreadsheet for importing into MS-Access, and close the file.

  • In MS Access, import the external data file. Hint:

    1. In Access, choose the “External Data” tab, and click on the Excel Icon on the import side of the menu. Carefully follow the importing menu options to ensure your columns are in the correct format.

    2. Select a name for their importing table (i.e., WEATHER XX where XX is your initials) and allow Access to assign the primary key.

• 4-2 – Perform a query to match like data. Hints:

  • Create a query that includes total daily sales ($ and units) Note: similar to the query for 3.19 in part 3 without a date parameter.

  • Create a query using a “daily sales query” and the “weather table” joined on date. The date join needs to only select rows when the date matches.

  • The query should have sales data and weather data for all days the business was open for all three years. Name this query “DailySalesWeather XX” where xx is your initials.

• 4-3 – Extract the query from MS Access to a spreadsheet file (SalesWeatherXX.xlsx).

  • From the External Data tab, select the Excel icon on the export side of the menu.

  • Select the Browse icon to choose the location to which you want the file saved for importing into Tableau.

  • In the options area, select “export data with formatting and layout”.

• 4-4 – Use Tableau to graphically evaluate various factors that may affect sales.

  • Open Tableau and Import the file “SalesWeatherXX.xlsx” that was exported out of MS Access

    1. In the initial Tableau screen, choose “Excel” in the “connect to a file” menu on the left of the screen.

    2. Locate and open the Excel file “SalesWeatherXX.xlsx” exported from Access (from step 4-3).

    3. The opened weather file should look similar to figure 3.

    4. Click on the tab “sheet1” in the lower left of the window. This opens a screen that allows graphically displaying the sales and weather data. Note: While this case presents one way to analyze the data, all students are encouraged to “play” with all of the features of Tableau as you will find multiple ways to accomplish tasks, and learn shortcuts to many features of the data analytics software. See Figure 4.

    5. Example for graphically viewing data: Hint: Do not be afraid to try different techniques; at any point, you may hold the CTRL and tap the Z key to “undo” the last command. Drag the Date from the dimension area to the column space as shown in Figure 4. Also, drag both the “sum of quantity” and “sum of ext price” to the “rows shelf” as shown in figure 4. To drill down to a daily view, move your mouse over the “Year” icon (in the column section) and click the “+” sign. This should now show (drill down to) quarterly data. Now click on the “+“ sign on the “Quarter” icon (referred to as pills) and then click on the “Month” pill to get it to daily information. Changing from continuous to discrete (eliminates days Lemonade Stand is closed). Right click on the “SumofExtPrice” to view options for viewing data and select “Dual Axis” (Figure 5) which combines the graphs to view the data together. You can see that quantity very closely matches with sales. Now click undo (or right click the SumofExtPrice and uncheck “Dual Axis”) to where there are two graphs. Single click on the SumofExtPrice axis to highlight it in blue and then right click to edit the axis. Edit Axis allows changing the scale (i.e., quantity can start at 0 or at any integer desired). The scale can also be reversed – try this to see what happens and then uncheck or undo it when finished. This is important if weather conditions are inversely related to sales. To move on to comparing weather to sales, remove the “SumOfextprice” by clicking the down arrow and selecting “remove” or just drag it off the rows back to the measures.

  • Determine if daily sales volume is related to weather components. Specifically, use Tableau to graphically compare sales to weather components of rain (WTR), Temperature (MaxTemp), and amount of Sunshine (S-S). Hint drag each weather variable to the “rows shelf” and repeat steps in “a” above to view the data together. Note: this is a key step. It is important to look at each graph and compare how similar each line's slope is to each other. Try reversing the axis and adjusting the scales to see if the lines become more or less similar.

  • Present results by saving your Tableau file with your final graph, and explain your conclusion in a one-page report.

The case was used in its current form at three universities. Two classes were undergraduate AIS courses and one was a graduate Auditing course. The two AIS courses included course objectives related to relational databases. The instructors each teach database querying, and added the data analytic introduction for the first time in the course. The graduate class utilized Part 1 and 4 to provide an introduction to data Analytic software (Tableau) for future use in the student's graduate program and in the graduate auditing course.

For each of the classes, the three different instructors solicited student feedback. All instructors used the same survey instrument. All students were asked to voluntarily answer an anonymous short survey about the case. The mean responses to the student perceptions are presented below in Table 3. The results indicate that the majority of the students valued the case. The students found the “lemonade stand” to be interesting, even at the graduate level. The undergraduates, less familiar with MS Access, found it helpful to build their database skills, and strongly recommended it for future use.

In addition to the above student perceptions, the students were also asked in an open-ended question to provide their thoughts about the case. Table 4 below includes all the comments (both good and bad) received from the students. As can been seen, overall the students enjoyed the case and thought it increased their knowledge of Access and Tableau.

We thank attendees of the 2016 AIS Educator Conference and 2017 AAA AIS Midyear Conference for their comments on the case. We also thank David Hayes and the conference and journal reviewers for their comments to improve the case. We thank the AAA-AIS Section and the IMA for sponsoring the educational sessions at the AAA AIS Midyear Conference, and the related awards. We are humbled and proud of this case being awarded “2017 Best Educational Case” at the AAA-AIS midyear conference.