SEGRO Courses

Home | Excel | Power BI | Copilot | Visualising Data

Coffee Cart Case Study - Instructions

There are three exercises.

1. Scope a spreadsheet model based on the case study background.
2. Specify a spreadsheet model based on the scope.
3. Design and build the spreadsheet based on the specification.

Case Study Briefing

Mark is thinking of starting a coffee cart business. He wants to decide if this is a good idea. He has asked you to build a model that estimates the revenue by month. At this time, he does not want the model to consider anything else e.g., costs or profitability. He has provided you with a briefing about his plans – note that not all of it is relevant to the scope of the model.

He can buy a used coffee cart for £15,000 and expects that it will last about 5 years. The seller states that energy / heating costs last year were £3,000.

Wandsworth Council have a pitch available at the local Hildreth Street market for £1,000 per month but the license only allows the sale of freshly made hot drinks. The market is in an outdoor trendy pedestrianised area next to the local Balham high street. There are lots of sit-down cafes here and they always seem to be full, so he has a good feeling about the business doing well.

Mark would like to earn the equivalent of a £50,000 salary in the first year rising by 5% each year. He is happy to work five days a week (closed on Mondays and Tuesdays but working bank holidays) and eight hours per day. However, he wants 12 full weeks of holiday a year. There is only room for one person to work inside the coffee cart. He has £20,000 of savings for any working capital.

Mark’s research suggests that the cost of the ingredients for the drinks, coffee beans, tea bags, milk etc is likely to be about £1.20 per cup. This does not include energy costs.

If Mark is to start the business, he must start on Jan 1st 2026.

The business will need public liability insurance cover.

Here is a photo of the Hildreth Street market. Mark’s coffee cart would occupy centre stage!

Scope Exercise – Instructions

Scope a spreadsheet model based on the case study background. Consider:

• What the model exists to do, i.e., what business need it is helping to meet
• What the model needs to produce, i.e., what outputs are needed to help meet the need
• How the model goes about producing it (but not, yet, the mathematical logic), for example who will be using the model Hint: be clear about what the model won’t do, as well as what it will do.

Use the template provided. This has three sections:

• What the model exists to do
• What the model needs to produce
• How the model goes about producing it

For each section there is a table with three columns

• Area e.g,.Time Horizon
• What is In Scope e.g., 5 years from 01/01/2025
• What is Out of Scope e.g., Any longer than 5 years

At the end of this exercise, teams make a short presentation to the class.

Specification Exercise – Instructions

Specify a spreadsheet model based on the example scope (do not use your own scope, to ensure everyone in the class is working to the same scope).

Part 1: Create a bubble diagram

Hint: at each step/bubble in the logic, ask yourself the following overlapping questions:

• What drives this variable?
• Can I reasonably estimate at this level?
• Is there benefit in going back another level in the drivers?

Part 2: Create an assumptions list based on the inputs in the bubble diagram

On the assumption list, fill in the template provided. Note there are 4 columns:

• Required Input
• Source
• Current Assumption
• Noted Simplifications

At the end of this exercise, teams make a short presentation to the class.

Design / Build Exercise - Instructions

Design and build a spreadsheet based on the example specification and following the best practice guidelines (do not use your own specification, to ensure everyone in the class is working to the same specification).

At the end of this exercise, some attendees and /or teams will demo and explain their spreadsheet to the class.