Consumer Data Analysis

In these steps, you will work with a variety of data to complete several different types of charts. The data needed for these charts are contained in the Excel workbook named A3Starter.xlsx. This workbook contains detailed purchasing data broken out by age and gender for the following: footwear, camping and fitness. You’ll use this data to create charts that illustrate the relationship between purchase of your customers broken down by age and comparing those sales numbers against the US population in general. You will then create a version of a personal budget that tracks your cashflow over the course of a month. You will also summarize several different stock pricing histories using a few of the stock chart types available within Excel. The goal for all of these charting exercises is to apply Tufte’s principles to effectively present this quantitative information.

Complete the following:

Download the workbook named A3 Starter.xlsx downloadable from above and then save it as A3-LastName FirstName.xlsx (where LastName is YOUR last name and FirstName is YOUR first name).

Step 1 (8 pts): The Pie of Pie worksheet contains the consumer purchase data organized by sport. Use this data to create a Pie Of Pie chart for the 2022, 2021, 2020, & 2019 data. Place this chart on the same worksheet as the source data. I realize that your pie chart can only display 1 year at a time but if you build it according to the videos you can allow the chart access to all 4 years and then select one year at time from the Chart Filters attached to the chart. Use the filters to put the chart in a mode of only showing the 2021 data.

In cell F3 create a sparkline line chart that displays the yearly sales totals or Exercise equipment. Make sure that you set the sparkline line chart to render the line in such a way that the 2019 year is on left and the 2022 is on the right. After you get this looking the way you want it, copy the sparkline down to the bottom row of your data (to cell F25). Hint: you can make the chart read the underlying date right to left by clicking on the Sparkline Tools/Design ribbon and looking in the Axis pulldown menu.
Going back to your Pie of Pie chart:
The upper left corner of the chart should be in cell G1 and the lower right corner should be in cell S29.
Format the chart with Chart Style 1 and delete the Legend.
Add Data Labels that display the Category Name and Value with the labels in a Outside End position. If any of the data labels are overlapping each other, move them around so that all values are legible. If you need to make the chart larger to better show the data labels, feel free to do so without penalty.
Add a chart title of “Consumer Purchases by Sport” on your chart.
To accentuate the largest piece of the pie you want to have that the “Exercise” pie slice to be filled with an picture of a dumbbell. That image can be downloaded from the top of this assignment immediately below the starter file.
The chart should look something like the image below. Your chart labels will undoubtedly look slightly different as they look different on everyone’s machine depending on screen resolution, Excel zooming, monitor size etc. We will check to see if they are set to “Outside End”. Bottom line, let us see all of the labels and you’re good to go.
Pie Chart of Consumer Equipment Purchases by sport

Step 2 (8 pts): The Footwear Purchases worksheet contains percentage data for footwear purchases organized by age groups. The data includes a breakout of the general U.S. population age population in column B by percentage.

Create 3 charts to compare the purchases by different age groups in 3 different ways for each year and compare those values with that of the U.S. population in Column B. Create the following:

a clustered column chart starting in cell I1 and going down to M17 with the chart title of Footwear Clustered Column
a stacked column chart starting in cell I18 and going down to M34 with the chart title of Footwear Stacked Column
a 100% stacked column starting in cell I35 and going down to M53 with the chart title of Footwear 100% Stacked Column
In all of your charts, make sure to include the US Population data in column B. Make sure to include a legend at the bottom of your chart and have the words US Population appear in the legend instead of the default value of 2022.

Hint: By default your US Population chart series might name itself as 2022 (the value in cell B4). You want that series to appear in the legend as US Population and not 2022. I show you how to do this in the videos but basically you need to change the cell reference for the Legend from B4 (cell containing 2022) to B3(cell containing US Population). There are a couple of ways to do this but here is how I do it…
Create the chart. If you use the wizard it will have 2 series that have a series name of 2022… booooooooo. You need to change the first one to US Population. To do this do the following:
With your chart selected, on your top tool bar click on design and then “Select Data”. (or just right click on the chart and click “select data”).
Select your first series that has 2022 for the series name and click edit. Change the series name from the cell containing 2022 to the cell containing “US Population”.

Hit OK

Quick verification (how we will grade it): If you change the value in cell B3, does your chart automatically capture/render this change? If so… you did it right!

In cell N1, explain which of the 3 best shows how sales for each age group compares to the number of people in the US are in that age group. Which age group are you doing the best in?
Please see the following for how the 1st of these 3 charts should look:

Footwear Clustered Column graph

Step 3 (7 pts): The Camping Purchases worksheet contains percentage data for camping purchases organized by age groups.
Create 3 different Area charts using Style 8 to compare the purchases by different age groups in 3 different ways for each year and compare those values with that of the U.S. population in Column B. Please note that when you insert an area chart there may be 2 or 3 choices. Please select the ones that show the % on the left axis and the age groupings along the horizontal axis.
Create the following 3 charts:

an area chart starting in cell I1 and going down to M17 with the chart title of Camping Area Chart
a stacked area chart starting in cell I18 and going down to M34 with the chart title of Camping Stacked Area Chart
a 100% stacked area starting in cell I35 and going down to M53 with the chart title of Camping 100% Stacked Area Chart
By default, the chart will show all of the data for all of the age groups. Use the new chart filtering tool to exclude the 65 & Older demographic range from all 3 of these charts.
Also, as you did in the previous Footwear step make that your US Population series is set to show the correct cell that holds the words US Population as opposed to the redundant and confusing 2022. If you do this correctly your legend along the bottom will not have 2 2022 series in it!
In cell N1, explain which chart tells the best story. Hint: Are any of the 3 confusing or hide data? If so, they aren’t the best!

Step 4 (7 pts): Upon graduation you will hopefully be gainfully employed and making bank! Let’s say that your annual salary is $48,000. After taxes that will look a lot like $36,000. If you are paid twice a month that will mean that each month you get two paychecks. For this problem you will get paid on the 1st and the 15th of each month. One important way of looking at your personal finances is to analyze your cashflow over the course of the month to make sure that you always have more money in your bank account to cover all applicable expenses.

On the Personal Monthly Cashflow worksheet I have added a typical month of income and expenses from one of my financial coaching customers.

Format column C to be a Number with 0 decimal places and use the 1000 comma separator. For the negative numbers, format them so that they are red surrounded by parentheses like this where a negative 150 looks like: (150)
In cell C44 create a formula that sums all of the income and expenses in the cells above in the same column.
Create a Waterfall chart with its upper left corner located in E3 that provides a visualization that tracks your cashflow over the month. Size the chart so that you can see the details in visualization clearly.
Add a chart title of My Cashflow
Change the Increase bars to green, the Decrease bars to red, and the Total bar to blue. Set the bar that represents your month-end total to be your totals cell to be the “Total” value as opposed to just another green income bar.
After you analyze your spending over the month you see that you went into a negative position shortly before your 2nd paycheck of the month. What most likely caused this? Put your answer into cell E1
At the end of the month you should have some money left over (a positive # in the Total cell). In cell E2, tell me what would be a good use of that surplus in the following month? This question is worth 0 points… I’m just curious what you think!
STOCK CHARTS:
Step 5 (6pts): As an analyst at Zeus’ Awesome Financial Advisers (ZAFA) company, you regularly monitor the performance of your clients’ investments. You are preparing for a meeting with a group of your investors to discuss how their stocks have performed year to date and changes they should make to their portfolio. Your clients have asked for information on two financial vehicles: a mutual fund that mimics the S & P 500 and Apple. You will find daily stock price and volume information for the last 12 months contained in the starter file on the S & P 500 and Apple worksheets.

For S & P 500, starting in cell H1 and ending in cell S25, create an Open-High-Low-Close chart using only the last 52 days going back to the beginning of July i.e. just the dates from 7/3/2022 – 9/14/2023.
Do not place a legend on any of these stock charts (Why??? …temporarily put one on and see what it adds. I’m guessing it is redundant and therefore it just clutters up the page). The chart creation wizard may automatically create it so if it does, remove it.
As demonstrated in the videos, there is lots of whitespace that hinders the visualization of the subtle changes in the daily prices. Alter the formatting of the vertical axis from the default values to the minimum boundary of 4,000 and the maximum boundary value to 4,700.
Format the down bars to have a Red fill and the up bars to have a green fill as demonstrated in the YouTube videos for this module.
Hopefully by now you know how I LOVE commas in large numbers over 1,000. Change the formatting of the numbers on the vertical (left) axis to Number with 0 decimal places and use the commas as the 1000 separator.
Adjust the font size of the vertical and horizontal axes labels to 12 pt.
Edit the chart title to S&P500 OHLC
My final chart looked “something” like the following:

S&P 500 OHLC graph

Step 6 (7 pts): For Apple (AAPL) create a Volume-Open-High-Low-Close chart using the same dates as you did for the S&P500 in the previous step. Please note that you will have to rearrange the columns of data for this chart since the data needs to be in the order of the name of the chart (Volume then Open, then High, etc.)

Place the chart between Cells H1 and Z40.
Axes adjustments: In order for the volume part of the compound chart to not overlap the stock pricing part of this compound chart you will need to adjust the volume (left) vertical axis boundaries to a minimum of zero and a maximum of 250,000,000.
Adjust the Stock Price (right) vertical axis to have a minimum of 140, a maximum of 200 and edit the major units to be 10.
Change the formatting of the numbers on both of the vertical axes to Number with 0 decimal places and Use the commas as the 1,000 separator.
Edit the chart title to AAPL VOHLC.
Remove the legend
Mine looked like this:

AAPL VOHLC Graph

Step 7 (7 pts): Finally we want to compare the performance of these two stocks over the last year.

On the S & P 500 page starting in cell H27 and ending in cell P50 create a Combination line chart to compare the change in closing stock price for each of these two securities for the dates during the entire last 12 months (9/15/2022 – 9/14/2023). The reason that we need this chart to be a combination line chart is because the two ranges of closing prices of the stocks are considerably different. Over the past year S & P 500 varied from ~$3,600 – $4,600 while Apple varied between ~125 – 200. If we plot those using the same scale the chart will not show the entire picture/comparison since we want to overlay them on top of each other. 
For full credit your chart should refer to the data on the existing S&P500 and Apple sheets containing all of the original data (the entire year’s worth) for each of the two stocks. For some reason some students will take the incorrect approach of copying the closing prices of the 2 securities onto the same sheet and then creating the needed chart from this data. This is less than ideal since they would be making copies of the data and this redundancy can lead to incorrect data/charts if the original data is updated but this copied data is not!
Since the price of S & P 500 mutual fund varies 3,600 – 4,600 your left hand vertical axis values should be from 3,300 – 4,800.  To have the Apple stock prices lay approximately accurately (so that their two % increases are accurately reflected on the chart) on top of the S & P 500 prices, alter the values on the right hand axis of your combo chart to a minimum of 100 and a maximum of 240. Format the numbers in both of the vertical axes as you did in the previous 2 steps with comma separators for the thousands and 12 pt font.
Which stock performed better over the past year? To find the answer to this question, add linear trendlines to both the S & P 500 & Apple series. In a cell immediately to the right of this chart tell us which stock has the better trendline as far as which one is more positive of a slope than the other?
Add the title of Stock Comparison to the top of the chart.
Add a legend to the bottom of the chart.
Make sure each Series Name is set to cell $A$1 (on the appropriate S & P 500/Apple worksheet) as opposed to “hard-coding” the name of the series into the chart. In case you didn’t watch the video titled “Charting Across Multiple Sheets” on the Module 3 Overview sheet you can get access to this in the Select Data icon on the Chart Design ribbon.