Break-even spreadsheet for a village concert
A local council in Gozo is organising a concert in a hall with a strict capacity of 400. Tickets are €15 for adults and €8 for students. Fixed costs are €2,500 for the band and hall, and variable costs are €3 per attendee for insurance and stewarding. The treasurer has built a spreadsheet with ticket prices in cells B2 (adult) and B3 (student), attendee numbers in B5 and B6, fixed cost in B8 and variable cost per attendee in B9. Cell B11 is meant to show total revenue and contains the formula =B5*B2+B6*B3. When copied down to test several scenarios, the totals are wrong. Cell B12 is meant to show profit and contains =B11-B8-B9*(B5+B6). A committee member has typed the adult price as text, '15, in one scenario column. The treasurer wants to know how many tickets must be sold to break even and what happens if the hall sells out.
1. Diagnose why copying the revenue formula produces wrong totals, and state the corrected formula. Then explain what the text entry `'15` does to the calculation.
Read the worked answer
Why copying breaks it. The formula =B5*B2+B6*B3 uses relative references. When copied one column to the right, Excel or LibreOffice shifts every reference one column: =C5*C2+C6*C3. That is correct if the scenario really has moved one column. But when the formula is copied down to compare scenarios stacked vertically, it becomes =B6*B3+B7*B4, which now multiplies attendee numbers by the wrong cells - often the student count or a blank. The result is a plausible-looking but meaningless total.
The deeper design fault is that the layout mixes two different kinds of cell: input values that change per scenario, and parameters such as the price that should stay fixed. There is no single correct fix until the layout is decided. If scenarios run across columns and each column has its own prices, relative references are right. If prices are shared parameters in B2 and B3 and scenarios run down rows, the formula must anchor the price cells with absolute references: =$B$5*$B$2+$B$6*$B$3 is not right either, because B5 and B6 are the attendees. A cleaner separation is: prices in B2 and B3, attendees for the current scenario in B5 and B6, revenue =B5*$B$2+B6*$B$3, then put each scenario in its own row with its own attendee cells and copy across with mixed references such as =$B5*$B$2.
The '15 entry. The leading apostrophe forces the cell to be stored as text. Text in an arithmetic formula is treated as zero in Excel, so revenue silently drops by 15 × the adult count, and profit is understated. A safer approach is data validation restricting B2 and B3 to decimals greater than zero, plus a check cell such as =ISNUMBER(B2) that flags the problem.
2. Using the figures given, calculate the break-even point for a scenario where the adult-to-student ratio is 3:1, and for a sell-out. Show your reasoning.
Read the worked answer
Let A = adult tickets and S = student tickets.
Revenue = 15A + 8S. Costs = 2,500 + 3(A + S). Profit = 15A + 8S − 2,500 − 3(A + S) = 12A + 5S − 2,500.
This simplifies neatly: each adult contributes €12 towards fixed costs and each student €5, because the €3 variable cost is deducted from each ticket price.
Break-even with a 3:1 ratio. Let S = A/3. Then profit = 12A + 5(A/3) − 2,500 = 0.
12A + 1.6667A = 2,500, so 13.6667A = 2,500, giving A ≈ 182.9.
So A = 183 adults and S = 61 students (rounding up, because you cannot sell a fraction of a ticket).
Check: revenue = 183 × 15 + 61 × 8 = 2,745 + 488 = €3,233. Costs = 2,500 + 3 × 244 = 2,500 + 732 = €3,232. Profit ≈ €1. A is just past break-even; 182 adults and 61 students gives revenue €3,218 and costs €3,229, a small loss.
Sell-out. 400 attendees at the same 3:1 ratio means A = 300, S = 100. Revenue = 4,500 + 800 = €5,300. Costs = 2,500 + 1,200 = €3,700. Profit = €1,600.
Sensitivity. If all 400 were students, profit = 5 × 400 − 2,500 = −€500, a loss. If all 400 were adults, profit = 12 × 400 − 2,500 = €2,300. The mix matters more than the headcount, which is the key message for the committee.
3. The treasurer wants to know whether a spreadsheet or a database is the right tool for this ticketing task. Advise, considering the committee's needs during the event.
Read the worked answer
For the planning decision, the spreadsheet is the right tool. The problem is a small what-if model: a handful of inputs (prices, attendee mix, costs) and an output (profit). Spreadsheets are designed for exactly this. A database would require tables, forms and queries to answer a question that a single formula answers in seconds.
For selling and tracking actual tickets, the spreadsheet is the wrong tool. Consider what happens at the door:
- Concurrency. Two committee members cannot safely edit the same spreadsheet at once. A database with transactions can handle simultaneous sales without double-selling seat 217.
- Integrity. A spreadsheet will happily record ticket number 0042 twice, or accept a student price for an adult ticket. A database enforces a unique ticket number and a valid price.
- Capacity. The 400 limit must be enforced, not merely observed. A database can refuse the 401st sale; a spreadsheet will display it.
- Audit. Who sold which ticket, when, and whether it was refunded? A database stores this; a spreadsheet relies on discipline.
Practical recommendation. Keep the spreadsheet for the committee's break-even modelling and scenario comparison, because it is fast and transparent. Use a small ticketing system - even a hosted low-cost service - for actual sales and door scanning. Export the final sales figures into the spreadsheet afterwards for reconciliation. If the council insists on one tool, use a database with a simple form for sales and a saved query that reproduces the break-even model, but accept the extra setup time. The trade-off is flexibility versus control, and control matters more once money and a hard capacity limit are involved.