Pro workbook: Setup, Till, Budget, Summary, Formulas. Ledger 70 theme. Sample data, real formulas.
| Setup | |
| Three things the rest of the workbook reads. Change them here, not on the other sheets. | |
| Shop name | Ledger & Loaf |
| Week starting | Mon 21 Sep |
| VAT rate | 20% |
| Budget month | October 2026 |
| Week starting should be a Monday — the Till sheet counts seven days from it. | |
| VAT rate is the standard rate where you trade. UK books are zero-rated, which is why | |
| the Till sheet asks separately how much of the day carried VAT. |
| Daily cash-up | VAT rate | 20% | ||||||
| Yellow cells are yours to fill. Dates and the VAT rate come from Setup. | ||||||||
| Day | Date | Card | Cash | Takings | Standard-rated | VAT in that | Covers | Per head |
| Monday | Mon 21 Sep | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | |
| Tuesday | Tue 22 Sep | 412.60 | 88.40 | 501.00 | 236.00 | 39.33 | 74 | 6.77 |
| Wednesday | Wed 23 Sep | 455.10 | 96.25 | 551.35 | 271.50 | 45.25 | 81 | 6.81 |
| Thursday | Thu 24 Sep | 498.75 | 74.90 | 573.65 | 289.40 | 48.23 | 88 | 6.52 |
| Friday | Fri 25 Sep | 612.30 | 131.70 | 744.00 | 402.60 | 67.10 | 109 | 6.83 |
| Saturday | Sat 26 Sep | 702.45 | 168.55 | 871.00 | 471.20 | 78.53 | 126 | 6.91 |
| Sunday | Sun 27 Sep | 388.20 | 96.80 | 485.00 | 246.10 | 41.02 | 63 | 7.70 |
| Week | 3,069.40 | 656.60 | 3,726.00 | 1,916.80 | 319.47 | 541 | 6.89 | |
| Standard-rated is the part of the day's takings that carries VAT — food eaten in, hot drinks. | ||||||||
| Books are zero-rated in the UK, so they are in Takings but not in Standard-rated. | ||||||||
| VAT in that is the tax already inside those sales, not an amount to add on top. |
| Running costs — the month | Month | October 2026 | |||
| Budget is what you planned. Actual is what went out. Status is worked out for you. | |||||
| Category | Budget | Actual | Var. | Status | |
| Wages | 14,200.00 | 14,380.00 | 180.00 | Watch | |
| Food and drink stock | 6,100.00 | 6,540.00 | 440.00 | Over | |
| Books bought in | 9,800.00 | 9,410.00 | -390.00 | On plan | |
| Rent and rates | 5,400.00 | 5,400.00 | 0.00 | On plan | |
| Utilities | 1,450.00 | 1,612.00 | 162.00 | Over | |
| Insurance | 620.00 | 620.00 | 0.00 | On plan | |
| Repairs and upkeep | 900.00 | 1,440.00 | 540.00 | Over | |
| Marketing | 450.00 | 380.00 | -70.00 | On plan | |
| Card fees | 680.00 | 702.00 | 22.00 | Watch | |
| Other | 2,300.00 | 2,156.00 | -144.00 | On plan | |
| Total | 41,900.00 | 42,640.00 | 740.00 | Watch | |
| On plan — spent the same or less than planned. | |||||
| Watch — over by up to 5 per cent of the budget line. | |||||
| Over — more than 5 per cent over. That is the one to look at. |
| The week in figures | |
| Everything here reads from the Till sheet. The two yellow cells are what only you know. | |
| Takings | 3,726.00 |
| Card share | 82.4% |
| Covers | 541 |
| Per head | 6.89 |
| Best day | Saturday |
| Busiest day's takings | 871.00 |
| VAT inside the week | 319.47 |
| Stock bought this week | 1,401.00 |
| Wages this week | 1,155.00 |
| Gross margin | 62.4% |
| Labour as a share of takings | 31.0% |
| Gross margin is takings less the stock those takings came from — not profit. | |
| Rent, wages and the rest are on the Budget sheet, which runs by the month. | |
| A week is a short read: one wet Saturday moves every number here. |
| How the formulas work | ||
| Every formula in this workbook, in plain words. No macros, nothing hidden. Formulas are shown without the leading = sign. | ||
| Where | Formula | What it does |
| Till · Takings | C5+D5 | Card plus cash. Keep them apart on the way in so the card statement can be checked against this. |
| Till · VAT in that | F5*$H$1/(1+$H$1) | The VAT already inside the standard-rated sales. Prices on the board include VAT, so the tax is a fraction of the gross, not 20 per cent on top: at 20 per cent it is one sixth. |
| Till · Per head | IF(H5=0,"",E5/H5) | Takings divided by covers. The IF keeps a closed day blank instead of showing a division error. |
| Budget · Var. | IF(B5="","",C5-B5) | Actual minus budget. Positive means you spent more than you meant to. |
| Budget · Status | IF(B5="","",IF(D5<=0,"On plan",IF(D5<=B5*0.05,"Watch","Over"))) | Three states from one number. Nested IFs read outward: on or under budget is On plan, up to five per cent over is Watch, past that is Over. |
| Summary · Best day | INDEX(Till!$A$5:$A$11,MATCH(MAX(Till!$E$5:$E$11),Till!$E$5:$E$11,0)) | MAX finds the biggest takings, MATCH says which row it is on, INDEX reads the day name off that row. The classic lookup pair — it works left as well as right, which VLOOKUP cannot. |
| Summary · Gross margin | IF(B4=0,"",(B4-B12)/B4) | Takings less the stock those takings came from, over takings. Wages and rent are not in it — that is why it is gross and not profit. |
| Summary · Card share | IF(Till!E12=0,"",Till!C12/Till!E12) | Card takings over all takings. Worth watching: card fees are a percentage, so the share moving up moves a cost with it. |