Written by: Bill Jelen, HFTP 2019 Annual Convention speaker
I was recently having lunch with a club CFO. She explained to me that it was difficult to do year-over-year comparisons because each of their two golf courses had been closed for renovation. One was closed last year, and the other was closed the prior year. The last two “normal” operating years were in 2016 and 2015.
“However,” she said, “we have had a shift in the age of our members since then.”
I said, “Wait… Do you mean that your members are four years older today than in 2015?”
“No,” she clarified, “There was a big push to bring in younger members. The initiative worked, but we are finding that the younger members have different habits. They might only golf on evenings and weekends. And, their food and beverage (F&B) spending seems to be lower.”
How to Calculate Average F&B Spending by Age Group
I knew we could quantify spending by age category using Microsoft Excel and a pivot table.
Bill Jelen will be presenting three sessions on Microsoft Excel at the HFTP 2019 Annual Convention this October: for beginners, intermediate and advanced Excel users. The host of MrExcel.com and author of 60 books about Microsoft Excel, Power Pivot and Power View, Bill is the consummate Excel expert.
Do you have any specific questions you want answered during the session?
Be sure to submit them in advance to hftp@MrExcel.com.
The starting data set has three columns: Member ID, date of birth and F&B spending.
Finding Age from Date of Birth Using DATEDIF Function
There is an undocumented function in Excel for expressing the difference between two dates. Pronounced “Date Dif,” the DATEDIF function asks for a starting date, an ending date, and then a code for how you want the information returned.
To get the age of someone in years, you would use:
=DATEDIF(B2,TODAY(),”Y”). The “Y” code means years.
We do not need age in months or days for this particular problem, but Figure 2 (below) shows the various codes you might use to get months or days.
Nore that while “y” returns 89 years, the “m” code returns 1,077 months. This seems to be rarely what you would want. In order to get the number of months in excess of full years, use the “ym” code. Similarly, the “md” code gives you days in excess of full months.
Note: I refer to DATEDIF as mysterious because the usual Excel tooltips to guide you through a formula will not appear for the DATEDIF function. Microsoft says it includes DATEDIF for backwards compatibility.
Adding Age to the Data in Excel
Add a new column D to your member data with current age. The formula in D2 is =DATEDIF(B2,TODAY(),”y”). Copy that formula down to all of the rows of your data.
Creating a Pivot Table That Shows Spending by Age
Select one cell in your data set. From the Insert tab in Excel, choose Pivot Table. Click OK to create a blank pivot table.
Excel will insert a new worksheet to the left of your data worksheet. A PivotTable Fields pane will appear on the right side of the screen.
- Drag the Age field from the top of the PivotTable Fields and drop it in the Rows area.
- Drag F&B Spend from the top and drop it in the Values area.
- The Fields pane should look like Figure 5 (below).
The pivot table will initially show every age and the total spending of those members.
This report is not yet helpful. You need to know how many members there are in each age and their average spend.
To get the number of members, drag any text field to the Values area. In this case, Member ID will work.
The heading in A3 starts out with the meaningless “Row Labels.” Go to the Design tab, open the Report Layout drop-down and choose Show In Tabular Form. This will replace the A3 heading with the word Age.
Adding an Average Calculation to a Pivot Table
The F&B Spend data is already in use in the pivot table, but you can add a second column based on the same data. Drag the F&B Spend from the top of the Fields pane and drop it in the Values area. This creates a Sum of F&B Spend2 column that is identical to column B.
Double-click on the heading in D2 to open the Field Settings dialog box. In the dialog box, change the calculation from Sum to Average. The heading will now say Average of F&B Spend2. Edit that to remove the 2 from the end.
You now have all of the columns defined. The last step is to group the ages into ranges.
Grouping Ages into Age Ranges
Select one of the Age cells, such as cell A4. From the Analyze tab of the ribbon, choose Group Field. The Grouping dialog shown in Figure 11 (below) starts out by showing groups of 10 years, starting at age 24. This is because the youngest member in the data is 24 years old.
I always edit the Starting At to be a round number, such as 10. You can choose to group by 5, 10, 20 or any number. You could use 40 to create a group from 20-59 and a second group from 60-99. But for this example, I am grouping from 20 to 103 by 10.
The last steps involve final formatting. Right-click each cell in B4, C4 and D4 and choose Number Format. I went with Currency & 0 decimal places for B & D with Number and a thousands separator for B.
The final report is shown in Figure 13 (below).
At this particular fictitious club, the F&B spending seems to peak with members in the 50-69 year age groups. Given the large number of members in the 80-89 age group, it makes sense to try to bring in younger members. However, the spending habits of those younger members will change the average spending per member.
Bill Jelen is the host of MrExcel.com and will present three Excel seminars at the HFTP 2019 Annual Convention in Orlando, Florida USA this October. Register to attend today.
Do you have any specific questions you want answered during his sessions at Annual Convention? Be sure to submit them in advance to hftp@MrExcel.com.
