Event budget tracker in Excel: which cost line is over
An event budget tracker is one row per cost line on an event you already have. You type the event, the line, the supplier, the planned amount, and the amount spent. The gap is spent minus planned while the row is still open. A flag says over when spent is higher than planned, and within when it is not. The first screen is a sheet named Lines. Row 1 is the header. Row 2 is one line you can check by hand: an event name, a line word, a supplier, planned 400, spent 450, status open. The gap is 50. The flag is over, because 450 is higher than 400. There is no guest list and no task list on that screen. This sheet is not a project budget across months, and it is not a marketing campaign spend list. Those lists follow a project or a campaign. This list follows the cost lines of an event. It is not an event planner of tasks, dates, and attendees, and it is not a registration sheet. The line is a word you type so you can filter. The sheet does not say what a line should cost. You type both amounts. The question is which open row spent more than the planned amount you typed. Closed rows drop out of the flag. They stay on the sheet. Elyx can write the gap and the flag. You still type the planned amount and the amount spent. The trial lasts 7 days, with no card.
The Problem
The budget fails when it lives in a thread of messages. A line says catering went over. Nobody can sort by the gap, and nobody can see which row is already above the planned amount you typed. A sentence without two amounts is not a tracker. A second failure is mixing event lines with other budgets. A project budget answers a project across months. A campaign spend list answers which campaign spent more than the amount you typed for that campaign. An event planner answers tasks, dates, and attendees. If you paste project months, campaign names, tasks, or guests here, the filter no longer means which event cost line is over. Keep this sheet to events, lines, suppliers, and the two amounts you typed. A third failure is deleting a line when the event is over. Set the status to closed and leave the row. The flag goes blank. Deleting the row removes the only record of the amounts you already typed. The sheet still does not say whether the price was fair.
Benefits
Each cost line is one row with a planned amount and an amount spent you typed.
The gap is spent minus planned while the row is open.
Over means spent is higher than planned on that row.
Closed rows stay on the sheet and drop out of the over count.
The line name is a filter. It is not a price.
Step-by-Step Tutorial
Create the list and the header
Name the sheet Lines. In row 1 type: Event, Line, Supplier, Planned, Spent, Status, Gap, Flag, Note. Event is the name you use. Line is a word you type, such as room or catering. Supplier is the name on the invoice you already have. Planned and Spent are numbers you typed, in the same currency, with no currency sign in the cell. Gap and Flag are formulas. Status is a word from a list. Note is optional. Freeze row 1. Do not merge cells.
One row per cost line. Two suppliers on the same event are two rows. Do not add their amounts in one cell.
Lock the status words
Select Status from row 2 down. Data, Data Validation, List. The source on a sheet named Lists is open and closed. Open means you still want the line in the over count. Closed means you are done watching that line. Only open stays in the flag. People must pick a word. A synonym such as done or paid will not match the formulas. The list is a filter. It is not a payment status.
Planned and Spent are numbers. Do not type the word over in those columns.
Enter one line you can check by hand
On row 2 type an event, a line word, a supplier, planned 400, and spent 450. Set status to open. You will predict the gap as 50 and the flag as over, because 450 is higher than 400. On row 3 use planned 400 and spent 300, status open. That flag should be within, and the gap should be -100. On row 4 use planned 400 and spent 450, and set status to closed. Gap and flag should go blank. Use amounts you can subtract on paper before you trust the formulas.
If you do not know one amount yet, leave that cell blank. The flag stays blank. Do not type 0 unless the amount really is 0.
Write the gap
The gap is blank when status is closed, or when planned or spent is blank. Otherwise it is spent minus planned. With Planned in D2, Spent in E2, and Status in F2, use =IF(OR(F2="closed",D2="",E2=""),"",E2-D2). Format as a number. A positive gap means spent is higher than planned. A negative gap means spent is lower. Zero means the two amounts match. None of those words is a judgment about the supplier.
=IF(OR(F2="closed",D2="",E2=""),"",E2-D2)Both amounts must be numbers. A currency sign typed into the cell turns the value into text, and text will not subtract.
Write the flag
The flag is blank when status is closed, or when planned or spent is blank. Otherwise it is over when spent is greater than planned, and within when spent is equal or lower. Use =IF(OR(F2="closed",D2="",E2=""),"",IF(E2>D2,"over","within")). Over means the amount spent is above the planned amount on that row. Within means it is not. The event name and the supplier are not in this formula.
=IF(OR(F2="closed",D2="",E2=""),"",IF(E2>D2,"over","within"))Equal amounts are within, because spent is not higher than planned. The gap on that row is 0.
Count and add the rows that are over
On a sheet named Morning, the over rows are =COUNTIFS(Lines!H:H,"over"). Over rows for the event named in A2 are =COUNTIFS(Lines!A:A,A2,Lines!H:H,"over"). The sum of gaps on over rows is =SUMIFS(Lines!G:G,Lines!H:H,"over"). The sum for one event is =SUMIFS(Lines!G:G,Lines!A:A,A2,Lines!H:H,"over"). Closed rows are out because their flag is blank. A blank flag is not over. Write the rule next to the count. The sum only adds rows the flag already marked over.
=COUNTIFS(Lines!H:H,"over")Do not add the visible gaps by hand after a filter and type over the formula.
Read a closed line as history, not as a deletion
If the flag is over and status is still open, spent is higher than planned and you have not closed the row. That is a row to read, not a row to delete. Either an amount is wrong, or you still want it on the list. Fix the cell that is wrong. The flag does not say the supplier overcharged. It says the two numbers you typed are in that order. A gap on a closed row should not happen, because closed blanks the cell. If you see a number on a closed row, the formula was overwritten.
Filter Flag to over for the list you act on. Filter Event when you only want one event. Filter Status to closed when you need history.
Check three rows before you share the count
Planned 400 and spent 450, status open: gap 50, flag over. Planned 400 and spent 300, status open: gap -100, flag within, because 300 is not higher than 400. The same 400 and 450 with status closed: gap blank, flag blank, and the over count ignores the row. If a check fails, fix the number format or the status word before you send the count. The count is only this sheet. It is not a task planner, and it is not a price recommendation.
Write the three checks on Morning if more than one person updates amounts.
Template Features
Gap while the line is open
Spent minus planned. Blank when status is closed or an amount is missing.
=IF(OR(F2="closed",D2="",E2=""),"",E2-D2)Flag against the planned amount
Over when spent is higher than planned. Within otherwise. Blank when closed.
=IF(OR(F2="closed",D2="",E2=""),"",IF(E2>D2,"over","within"))Over count by event
COUNTIFS reads the event and the flag word.
=COUNTIFS(Lines!A:A,A2,Lines!H:H,"over")Sum of gaps that are over
SUMIFS adds the gap only where the flag says over.
=SUMIFS(Lines!G:G,Lines!H:H,"over")History stays on the sheet
Closed rows remain. They leave the over count because the flag is blank.
Concrete Examples
Spent is above the amount you typed
You want a line on the list when spent is 450 and planned is 400.
Planned: 400. Spent: 450. Status: open.
Result: Gap is 50. Flag is over. The over count includes the row. The 400 is the number you typed, not a market price.
Spent is not above planned
Spent is 300 and planned is 400. You do not want this line on the over list.
Planned: 400. Spent: 300. Status: open.
Result: Gap is -100. Flag is within, because 300 is not greater than 400.
A closed line leaves the list
You are done watching the line. You want it out of the over count and still visible.
Status changed from open to closed. Amounts unchanged at 400 and 450.
Result: Gap becomes blank. Flag becomes blank. The over count drops the row. Both amounts are still on the sheet.
Pro Tips
Same currency in both amount columns
The subtraction does not convert currencies. If one amount is in another currency, convert it before you type it, and say so in Note.
Line is a label
room, catering, or any other word lets you filter. The word does not set a price. The flag formula never reads the line name.
Zero is an amount
Leave a cell blank when you do not know the number. Type 0 only when the amount is zero. A blank cell keeps the flag blank. A zero can make the flag over or within.
Distinct from planners and other budgets
Tasks, dates, and guests belong on an event planner or a registration sheet. Project months belong on the project budget. Campaign amounts belong on the campaign spend list. Do not paste a guest into Line and expect this count to mean the same thing.