Using Excel to support decision making

My BizCafe workbook. A labor of love.

I must admit--I love Excel. I don't say that about many things, but since I learned how to use Excel in a college class many years ago, I find it incredibly satisfying to use Excel to work with data. Odds are you won't be as intrinsically motivated to do all this work as I was, but I wanted to make informed decisions.

In the tutorial class where we were briefed on the game, our professor recommended using a spreadsheet to help with making decisions. The reports that BizCafe offers provide all the information you'll need, but to harness that data and make projections, I found that Excel made it easier to organize my thinking.

By week 3, I had my basic decision-making process figured out. In this post, I'll walk through that process. There are lots of ways to do this, I'm sure, and I wouldn't expect anyone to put in as much work as I did, but this is what worked for me.


Competition tracking

The purpose of competition tracking, for me, was to get a handle on how each team was performing and understand what was working and not working.

I started using Excel to track the competition after week 2. I wanted to compare how each team did from week to week. I pulled data from the results page, and added it to my spreadsheet:

I took the cups sold data and the customer satisfaction data from the results page outside the simulation, and the price, hours, and employees figures from the Market section of the simulation. I like using conditional formatting to highlight the "difference" column, so I can easily see which numbers went up and which numbers went down from week to week.

(You know, eventually, I'd love to develop the programming acumen to be able to automate this process, but I did this all manually. I also enjoy data entry in a way most people don't.)

After week 2, I gave this section its own worksheet in my workbook. I continued to add each week's data so I could see the new week-over-week changes, and I expanded my tracking to cover the following metrics:

• Cups sold
• Price
• # of employees
• Hours open
• Staffing level (calculating this necessitated tracking # of employees and hours)
• Customer satisfaction
• Brand awareness
• Net income
• Cumulative net income
• Revenue
• Revenue per employee

You should know--I didn't actually "use" all of this data. But I wanted to have it handy just in case I needed it for whatever reason later. And the act of copying it down forced me to take note of what was happening in the game. Here are two things that I found particularly useful from this table:

1. Industry total of cup sales

This was useful for making sales projections from week to week. From studying presentations I'd found online, I knew that total cup sales increased at first, but then levelled off.

• Week 2--increase of 3,909 as the shops' brand awareness grows
• Week 3--increase of 4,568 as shops open for the weekend
• Week 4--even though brand awareness continues to rise, total cup sales only grow by 910
• Weeks 5 and 6--increases of 1,116 and 264--more levelling
• Week 7--increase of 1,088 as shops offer small and large sizes for the first time
• Week 8--contraction for the first time (there's a particular reason that happened)

I tracked this because I wanted to see if there is some point where market saturation occurs, after which your success becomes a matter of "stealing" business from your competitors. I have a feeling that this is more likely in a longer game.

One very useful bit of information I gleaned from this was the impact of brand awareness. I'll discuss this in another post, where I explain my advertising and promotion strategy.

2. Staffing levels for each shop

I explained this in the previous post, "Lessons from weeks 1 and 2: Capacity utilization and price". This is where I pulled that information from. You get the number of employees from the Market > Local Labor report. Hours data come from the Market > Industry Menus.

"Hours" was kind of a pain in the ass. For the shops that were open 7 days a week, 7 to 11, that was easy--112 hours. For the others, I used a formula that looked like this:

=(22-7)*5+(23-8)+(21-8)

(22-7) represents 10pm minus 7am, the shop's weekday hours, which are multiplied by 5. The other two parentheses represent Saturday and Sunday hours.

This is where copying cells comes in handy. Once I created this formula, I would copy it to the next cell and tweak the numbers. When the next week came around, I would copy the previous week's cells, paste them in the new week's column, and check the menus to see if anything needed to be changed. I found that there were usually only a few changes to hours each week.

Factors affecting sales volume (optional, maybe read this, but don't bother doing it unless you're really ambitious)

This part takes more work than I'd expect your average college student to put in. At the end of this post, I'll explain some Excel tricks that make work like this manageable. The value of this probably isn't worth the effort for most people. I wanted to see if there were any discernible patterns among these factors.

After week 4, I think it was, we were given an in-class assignment where we needed to do pricing/revenue projections based on the game. Part of the assignment was to hand in a price/sales/revenue projection model, where you would make revenue projections based on projected sales levels at a range of price points. It was a simple economic model--lower price, more sales; higher price, lower sales; from this, find the combination that yields the most revenue.

But I knew it wasn't that simple. Your sales weren't simply a function of your price. By that point, I knew there was an interplay of four main factors that influenced your sales volume:

• Price
• Brand awareness
• Servers/hour
• Customer satisfaction

Furthermore, I had a suspicion that the relative values of these factors could be as significant as the absolute value of these factors. So I was inspired to do the following:

1. Calculate the industry average of each of these factors, plus sales volume, for each week.
2. Create a table for each shop where each absolute value for each of these factors is converted into a relative, indexed value, where 100 represents the industry average.
• A value higher than 100 means your shop was above average for that metric.
• A value lower than 100 means your shop was below average for that metric.
• For example, during week 1, our price was $5.30. The average price was $4.32. 
• 5.30 divided by 4.32 times 100 equals an indexed price of 123.
3. Between each week, I included a condition-formatted "weekly difference" column, to display where increases and decreases were happening.
4. I constructed a line chart that overlaid the four factors plus sales volume, and put it side-by-side with a graph showing revenue and net income.

This is what my "index table" looked like after eight weeks:

And these were our final charts:

The meaning of what you see in these charts would be different in every game, because they represent the unique industry conditions of your game. For example, our relative "Servers/Hour" metric was so low because other shops went overboard on staffing. In that case, indexing below the average was the better strategy.

I did this for our shop, and for all the shops in the game. Again, most of the data didn't turn out to be all that interesting. But it all told a story.

All in all, though--this step is optional. I did it out of curiosity.


My decision-making process

I did the competition analysis described above so I could get an idea of who was doing what, and why some shops were successful and some weren't. Most importantly, after the three weeks when Derosa Coffee House out-earned us, or the weeks where our sales results didn't meet our projections, I wanted to find out how and why that happened.

Once I had a picture of what happened and what needed to be done to fix it, I started work on my decisions. I worked out this process after week 2. It's not a straight, linear process; there's some back-and-forth and a lot of tweaking involved.

1. Daily sales records and forecast

I created a separate worksheet within the workbook called "DailySales".

Under Company, I opened Daily Receipts. From this table, I would enter daily capacity and cups served for the day. I opened each day's report and pulled Long Wait, Served After Hours, and Left or Outside Hours. I compiled them into daily tables like this:

After week 2, I was able to plot the Week to Week Cups+ column:

Bottom status bar displays average, count, and sum of
selected data.

With that column, I got a sense of how much sales were increasing on a day to day basis. Then, based on these numbers, I would make projections for the coming week.

Here's a handy Excel trick: if you select a group of cells, the status bar at the bottom of the window shows the average, count, and sum of the data in those cells.

I would select the five weekdays and find the average increase from the week before. Then, for the following week, I would make a conservative estimate of what kind of increase I would expect to see. So in the upper block of numbers in the image above and to the right, Week to Week Cups+ is a calculation. In the lower block of numbers, the Week to Week Cups+ values (the 30s and 10s) are entered into the cell, and the values in the Cups Served column are a calculation: last week's sales plus the estimated increase for that day.

Note that it is necessary to do separate calculations and projections for Saturdays and Sundays. Saturday sales are less than weekdays, and Sunday sales are less than that.

For the capacity numbers, I would start out with capacity numbers based on the same number of servers as the week before. Later in the process, when I would have to decide how many servers to employ, I would come back and tweak these numbers.

Another good thing to do at this stage is to look at how many customers were lost the previous week because of long lines or inadequate hours:

Lost revenue is the number of customers who left times the revenue they would have generated.

• At first, that's pretty simple: Left or Outside Hours x Price
• After you add the baked goods: Left or Outside Hours x Price + Left or Outside Hours x 0.3 x Baked Goods Profit Margin ($1.50 or $1.00 if you didn't buy the oven)
• After you add small and large sizes: Left or Outside Hours x Small % of Sales x Small Price + Left or Outside Hours x Medium % of Sales x Medium Price + Left or Outside Hours x Large % of Sales x Large Price + Left or Outside Hours x 0.3 x Baked Goods Profit Margin

It's important to note the sum of these daily values--the total revenue lost for the week. This needs to be taken into consideration when determining how many servers to hire for the following week. The incremental cost of an additional server can be worth it if it reduces your lost revenue significantly.

Notice here that the capacity utilization numbers were higher during week 8 than they were during week 7, when we had more customers leave the shop due to long lines. There were more customers complaining about a long wait during week 8, but they stuck around instead of leaving.

2. Set wages

I set wages for managers and servers based on the industry averages. The higher your managers' and servers' pay is compared to the industry average, the more satisfied they'll be, and the more satisfied your customers will be.

I found it necessary to keep the difference at least the same, or greater than previous weeks. There was one week where I tried leaving wages the same while the industry average went up, and I had a server quit. That was the first time that happened. The relative raises returned the following week.

3. Set number of servers/capacity

Next, I would figure out the cost of the various staffing levels I could set. These next two images are all the same rows; I split them in two because it's too long for the screen otherwise.

In this example, I wanted to consider the cost and capacity of 21 through 25 servers, and figure out the additional cost involved in each staffing level compared to the previous week. I would factor in the rise in wages in the "manager cost" and "server cost" columns--simply the number of each times the wages of each. I would use the Decisions > Staffing & Hours window to get the capacity and servers per hour numbers. Payroll taxes, at least in my simulation, I figured out to be 7.5% of the staff cost.

I would look at the sales increase I hoped to see (as calculated in "DailySales") and make sure that the capacities of the staffing levels I was considering were adequate to cover it. Next up--plug the staffing costs into net income projections.

4. Net income projections

Here's the framework I used to project net income for the week:

This one was from a week after we had added baked goods, but before we offered medium and large sizes. I would use relative cell references in the formulas so that I could copy the whole thing; I would create one of these for each combination of staffing level and price that I was considering. Here's another one from the same week:

The green highlight indicates that this was the decision I went with--25 servers, $4.85 for a medium cup.

Here's what the revenue projections looked like after we had the option of offering small, medium and large sizes:

The columns to the right of the box on the left represent the different levels of profitability I could expect from the different possible sales volumes that could happen. I used conditional formatting again--the green volumes are those which were above the projection I calculated in the "DailySales" worksheet; the red volumes are below the projection.

Week 2: Inhibited sales growth, but improved profitability

After the first two rounds, I decided it was useless to try to assume I knew what the sales would be for the coming week, even though you need to make a guess so you can buy an appropriate amount of coffee. I wanted to see the range of potential incomes that were possible--if I was satisfied with the potential incomes we could see at that combination of servers and price, that's what mattered. During week 2, cup sales were way under our sales estimate, by 227 cups. But we still turned a bigger profit than the other shops. I had accounted for that sales level in the range of net income projections.

5. Settling on decisions

There weren't any hard-and-fast rules I used to pick a combination of servers and price. Usually it was a response to the analysis from the week before. If we dropped the price, we could expect more customers wanting to buy the coffee, but that would mean we needed to add more servers to handle the increased demand.

After we bought the oven, we didn't add enough servers to accommodate the increased demand, and that hurt our sales growth that week. That showed up as an increase in the number of customers who left the shop due to a long wait, and a relatively small increase in sales compared to the competition.

So there's an element of guesswork involved that you can't avoid. The general guidelines I would try to abide by were:

• Find the problems and make decisions that solve them
• Err on the side of profitability

So we'd skimp on servers, or leave our price high, and then make corrections when we saw that we were holding back potential sales gains (like after week 2).

Cash budget analysis from the week we bought the oven.
It was the same week we had to pay taxes. Actual decrease in cash
wasn't as bad as this; turned out to be $2,495.53.

Note the box at the bottom of the income projection--that's the change in cash that the simulation projected. I would enter the potential decisions in the Staffing & Hours and Marketing pages in the simulation, and then check the Cash Budget Analysis page to see what the projected change in cash would be. That's important to keep an eye on. Taxes are paid on the fourth week of every month, and the oven purchase shows up here, but not on the income statement, because it's a capital expense, and not an operating expense.

One thing to note about that cash projection--it doesn't include revenue from baked goods. I had to add that calculation myself.


Organizing my workbook

So far, I've discussed the three main worksheets I would use to make decisions: "Competition", "DailySales", and the decision-making worksheet, which I called "MainWeek2toWeek3". I created a new decision-making worksheet each week, e.g. "MainWeek3toWeek4", etc., by duplicating the previous week's sheet and updating the values.

I also had a separate worksheet called "BrandAwareness", which I used to make decisions about advertising spend; one called "OvenDecision", which I used to figure out whether or not to buy the oven; and one called "SmMdLg", where I analyzed the small-medium-large pricing decisions. I will be writing separate posts for each of these topics.


Excel techniques that helped

A big one--copying and pasting formulas. Practically every screenshot in this post shows a table where I would plot out one row of data, copy and paste that row, and then change the necessary values in the new row.

This table used all the techniques I've mentioned

A necessary concept to have mastered to facilitate this technique is using absolute and relative cell references: Switch between relative, absolute, and mixed references

Another one: Use formulas with conditional formatting

I'm also a big fan of customized number formatting: Create or delete a custom number format

Also useful to know how to freeze panes: Freeze panes to lock rows and columns