Challenge Scenario
In restaurant point-of-sale (POS) systems and dining hospitality management, service staff calculate gratuity tips, determine total bill charges, and evenly split the check among party guests.
Automating these calculations in a spreadsheet guarantees that tips are accurately accounted for and every guest pays their exact equal share without awkward rounding discrepancies.
As a Restaurant Cashier, you are balancing four dining table receipts at closing:
- Tip (Column C): Calculate a standard 10% tip on Subtotal (
Subtotal * 0.10).
- Total Bill (Column D): Add the food Subtotal and Tip together (
Subtotal + Tip).
- Per Person (Column F): Divide Total Bill by the number of Guests seated at the table (
Total Bill / Guests).
- Total Shift Revenue (Cell D7): Sum all Total Bills across all tables using
SUM(D2:D5).
Your Sheet Layout
Understanding how the restaurant billing sheet is organized:
- Table Orders (Columns A & B): Identifies the table number (
Table 1 to Table 4) and food/drink order subtotal ($60 to $220).
- Gratuity & Total Charges (Columns C & D): Calculates 10% tip and total check amount.
- Party Size & Bill Splitting (Columns E & F): Shows guest headcounts (2 to 4 guests) and calculates the individual share per person.
- Shift Total Revenue (Cell D7): Grand total dining revenue across all tables ($561).
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:B5 |
Table ID, Subtotal |
Table Orders |
Table IDs and food subtotal amounts |
Original dining bill order subtotals. |
C2:C5 |
Tip (10%) |
Calculation |
=B2 * 0.1 |
Calculates 10% gratuity tip on subtotal. |
D2:D5 |
Total Bill |
Calculation |
=B2 + C2 |
Adds food subtotal and gratuity tip together. |
E2:E5 |
Guests |
Party Size |
Number of seated diners |
Number of guests splitting the check. |
F2:F5 |
Per Person |
Calculation |
=D2 / E2 |
Divides total bill evenly by number of guests. |
D7 |
TOTAL BILLS REVENUE |
Summary |
=SUM(D2:D5) |
Calculates total shift revenue across all tables ($561). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column C (C2:C5), calculate 10% Tip using =B2*0.1.
2
In Column D (D2:D5), calculate Total Bill by adding Subtotal and Tip (=B2+C2).
3
In Column F (F2:F5), calculate Per Person amount by dividing Total Bill by Guests (=D2/E2).
4
In cell D7, calculate total bills revenue using SUM(D2:D5).
Solution & Formula Breakdown
Let's examine how each arithmetic step is calculated row by row:
1. Calculating 10% Tip (C2:C5)
=B2 * 0.1
Multiply the Subtotal in Column B by 0.10:
- Table 1:
80 * 0.10 = 8 → $8.00
- Table 2:
150 * 0.10 = 15 → $15.00
- Table 3:
220 * 0.10 = 22 → $22.00
- Table 4:
60 * 0.10 = 6 → $6.00
2. Calculating Total Bill (D2:D5)
=B2 + C2
Add Subtotal and Tip together:
- Table 1:
80 + 8 = 88 → $88.00
- Table 2:
150 + 15 = 165 → $165.00
- Table 3:
220 + 22 = 242 → $242.00
- Table 4:
60 + 6 = 66 → $66.00
3. Splitting Evenly Per Person (F2:F5)
=D2 / E2
Divide Total Bill by the number of guests:
- Table 1 (2 guests):
88 / 2 = 44 → $44.00 per person
- Table 2 (3 guests):
165 / 3 = 55 → $55.00 per person
- Table 3 (4 guests):
242 / 4 = 60.5 → $60.50 per person
- Table 4 (2 guests):
66 / 2 = 33 → $33.00 per person
4. Shift Total Revenue Summary (Cell D7)
=SUM(D2:D5)
Adding all four table checks (88 + 165 + 242 + 66) gives the grand shift revenue of $561.00.
You can also calculate Total Bill directly in one formula using multiplication: =B2 * 1.10. Multiplying by 1.10 automatically includes 100% of the subtotal plus the 10% tip!