Weekly Reporting- building weekly MI sheets.
MI stands for ‘Management Information’. Weekly MI sheets are basic spreadsheet reports that highlight your three biggest areas of focus: revenue, gross profit, and labour. It’s important to know exactly how your business is performing on a week-by-week basis as this will help you to adapt quickly and make changes whilst the performance of the previous week is still fresh in your mind.
Revenue
This should be easy enough to pull from your till system. If you’re a venue with both food and drink, then you need to know the revenue for both of those elements separately. The figure we’re looking for is the NET revenue, so excluding VAT. As we’ve mentioned before, VAT isn’t part of your revenue and shouldn’t form part of any costing or GP calculations. This is a tax that you’re collecting from your customers on behalf of HMRC- it isn’t yours. If you’re unable to find the net figure, then enter the gross (VAT inclusive) figure into your spreadsheet, and in the box next to where you’ve entered this figure type in the following =cell/1.2. Replace the word “cell” with the actual cell reference where you’ve entered the gross revenue amount e.g. A2, B5 etc. Copy the formula down to the next row if you’re calculating both food and drink separately.
Below both of these calculations you’re going to want to add it up to give you the total net revenue. This is because labour is worked out on total net revenue, where as GP is worked out based on food sales and food stock, and wet sales and wet stock. The reason that we’re not calculating the labour % for those different departments is because you’ll have front of house staff that run food as well as take drinks orders and so it becomes a little more difficult to calculate this (it’s possible to do, but it takes a lot of working out, and a lot of time spent working on the floor watching and timing your team).
Gross Profit
I’ve not covered off stock counts here, so we’re not going to use the full method of calculating GP just yet. We’re going to work out your GP based on what you’ve bought in that week vs what you’ve sold rather than basing GP on the COGS (or Cost of Goods Sold) method. For those of you that are a little more curious, the way to calculate COGS for working out your GP is: Opening stock value + purchases — closing stock value = Cost of Goods Sold. All of these figures should be the NET purchase price (the price you pay your suppliers excluding any VAT).
Use one line for food purchases and a separate line for any wet purchases.
To work out your GP, you’re going to need the following: =gross profit/ net revenue. As an example, it should look like this as a formula in your spreadsheet:
You then need to format that cell to percentage. Click the central dropdown menu on the ribbon and select percentage (%). This will then show you your percentage GP! Repeat the process again for either your food or wet revenue and stock.
The percentage figure that you’ve got in front of you won’t be a completely accurate representation of what your actual GP is, but it’ll at least give you a rough idea of where you at and a way to start monitoring your business’s performance. We’ll look at the full calculation later on once we’ve covered off stock counts.
Labour
Work out the total cost of your staff for the given period. If you’re unsure how to do this, then read our article “The actual costs of employment”.
In the cell next to the labour cost that you’ve copied over, you need to use the formula =total labour cost/ total net revenue. Replace “total labour cost” with the cell reference that the cost is in and do the same for “total net revenue”. Once you’ve done that, hit enter and then format that cell to percentage by clicking the central dropdown menu on the ribbon and selecting percentage (%).
Hey presto, you know have a rough idea of your GP and a highly accurate labour cost percentage! For now, this is all you need to know, but if you’d like to take it a step further, you can enter formulae to work out how close to/ far away from your target you actually are.
In the cell adjacent to where you’ve worked out your GP for food and/or drink, use the formula =net revenue*percentage. Replace percentage with your target gross profit percentage. Once you’ve calculated that figure, use the cell adjacent to this to work out the difference between the two figures. Note, you should work out the difference using actual £ values, or numbers. Don’t try to work this out using the percentage figures as it’ll yield wildly different results.
After you’ve done this calculation for your gross profit figures, carry it out on your labour cost.
Once you’ve done both of these calculations, you’ll be able to see how much money you’re losing from your bottom line week-on-week. It can be scary to see it laid out in front of you, but know you know what your deficits are and we can work to change them.
Comments