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
I knew we could quantify spending by age category using
Microsoft Excel and a pivot table.
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.
Finding Age from Date of Birth Using DATEDIF
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
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
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
- 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
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.
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.
Source: FS – All-Hotels-Blogs
From the Expert: Learn How to Excel at Microsoft Excel