Budget, actuals and goals in one sheet

Note: general recommendations only, not professional advice. You are responsible for checking before use. For money, VAT and tax matters, consult a certified accountant or tax adviser. The full text is at the end.

Who this is for

Business owners, CEOs and CFOs of small and mid-sized companies, and nonprofit directors who need to show the board and donors where the budget stands. If your budget gets built once a year in Excel and then sits in a drawer, or tracking only happens when the accountant sends a trial balance, this kit is for you.

It doesn't replace your bookkeeping. It's the tool where management sees every month where you are against the plan and where the year is heading.

What you'll have at the end of the hour

• A "Budget" tab: every income and expense line, an annual budget, a split across 12 months and an owner for each line.
• An "Actuals" tab: the real transactions, each assigned to a line from a closed list.
• A "Tracking" tab: budget to date against actuals to date, the gap in money and in percent, a year-end forecast, and a color on every line that's off.
• A "Goals" tab: the year's business goals, with percent achieved and a status.
• Three prompts: a budget draft from last year, assigning transactions to lines, and a monthly management report.

What you need

• A Google account (for Sheets).
• Last year by line: from the P&L, a trial balance or your bookkeeping. A rough estimate is enough to start.
• An export of the last few months' transactions, from your accounting software or your bank.
• A free AI tool: ChatGPT, Claude or Gemini. Optional: every formula works without AI.

One rule you don't bend: the AI doesn't decide numbers. It organizes, assigns and writes. Every number in the budget comes from your data or from an assumption you wrote, and any explanation for a gap that you didn't give comes out in the report as [to check].

The kit, step by step (60 minutes)

Step 1 (10 minutes): Open a sheet and list the budget lines

What to do: Create a new sheet called "Budget [year]" with four tabs: Budget, Actuals, Tracking, Goals. Paste the header row into the "Budget" tab.

How: Click cell A1 in the "Budget" tab and paste. Each header lands in its own column.

Line	Type	Category	Owner	Annual budget	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Check

Below it, one line per row. In "Type" write only Income or Expense. In "Category" group lines, for example: payroll, marketing, operations, client revenue, donations.

Common mistake: too many lines. 15 to 30 lines is plenty to manage by. Leave the hundreds of ledger accounts to your bookkeeping.

Step 2 (10 minutes): A budget draft from last year

What to do: Give the AI last year's numbers and your assumptions, and get a table ready to paste, including the monthly split.

You're helping build an annual budget. Here are two inputs.

Last year by line (line | type | category | annual amount):
[paste]

My assumptions for next year:
[for example: salaries +4%, one more hire from April costing 24,000 a month, rent unchanged, client revenue +10% with seasonality: August weak, December strong]

Binding rules:
1. Every number is calculated only from last year and the assumptions I wrote. If a line has no assumption, keep the same amount.
2. If an assumption is unclear, write [to decide: ...] on that line and don't guess.
3. Monthly split: equal across 12 months, unless I wrote seasonality or a start date. The 12 months must add up to the annual budget.
4. Don't add lines that weren't there, except what clearly follows from the assumptions (for example a new hire).

Return:
a. A table inside a code block, tab between columns, in this order: line | type | category | owner (empty) | annual budget | Jan ... Dec. Numbers with no thousands separators and no currency sign.
b. Below it, a short list: each assumption and where it had an effect, and every [to decide] that's left.

How: Paste the table into A2. In the last column, "Check", paste this formula into R2 and drag it down:

=IF(ROUND(SUM(F2:Q2)-E2,0)=0,"OK","Doesn't add up")

If you see "Doesn't add up", fix the months by hand. For a line with no seasonality you can simply write =$E2/12 in F2 and drag it to Q2.

Step 3 (10 minutes): The Actuals tab and assigning transactions

What to do: Bring in the real transactions, and give each one a line from a closed list.

How:

1. In the "Actuals" tab, in A1, paste the headers: Date, Description, Amount, Line, Note (each in its own column).
2. Paste the transactions from your export. Amounts are positive, expenses too: the type is already on the line. Remove account numbers and identifying details first. If the export is messy, the kit Where the money goes shows how to clean it.
3. Select column D and choose Data > Data validation > Dropdown (from a range), with the range Budget!A2:A. From now on a transaction can't be assigned to a line that doesn't exist.
4. To assign many transactions at once, run the prompt:

Here are my budget lines (line | type | category): [paste]
And here are transactions (date | description | amount): [paste]

For each transaction, choose one line from the list only, exactly as written. If no line clearly fits, write [to check] and don't guess.
Return a code block with a tab between columns: date | description | amount | line | note. Write a note only when there's doubt, in one short sentence.
At the end, one line: how many transactions were assigned and how many are left [to check].

Common mistake: treating loans, transfers between accounts or VAT payments as expenses. They change your cash, not your budget. Give them a separate line of type "Non-budget", or leave them out of the tab.

Step 4 (10 minutes): The Tracking tab and its formulas

What to do: Build the tab that shows, for each line, where you stand.

How:

1. In the "Tracking" tab, write "Last closed month" in J1 and its number in K1 (for example 9 for September). Write "Year" in J2 and the year in K2.
2. Paste the headers into A1:

Line	Type	Budget to date	Actual to date	Gap	Gap %	Year-end forecast	Annual budget	Expected overrun

3. Paste these formulas into row 2, each in its column, then drag the whole row down for as many lines as you have:

A2: =Budget!A2
B2: =Budget!B2
C2: =SUM(OFFSET(Budget!F2,0,0,1,$K$1))
D2: =SUMIFS(Actuals!$C:$C,Actuals!$D:$D,$A2,Actuals!$A:$A,">="&DATE($K$2,1,1),Actuals!$A:$A,"<"&DATE($K$2,$K$1+1,1))
E2: =D2-C2
F2: =IF(C2=0,"",D2/C2-1)
G2: =D2+IFERROR(SUM(OFFSET(Budget!F2,0,$K$1,1,12-$K$1)),0)
H2: =Budget!E2
I2: =G2-H2

Why this way: "Budget to date" adds up only the months already closed, so the comparison is fair. The year-end forecast is actuals to date plus the budget for the months left. It's a simple, conservative assumption: what happened has happened, and from here you continue on plan.

4. Under the lines, three total rows. In C (then drag right to I):

Total income:   =SUMIF($B$2:$B$40,"Income",C$2:C$40)
Total expenses: =SUMIF($B$2:$B$40,"Expense",C$2:C$40)
Surplus / deficit: =[income cell]-[expenses cell]

Delete column F in the total rows: a total of percentages means nothing.

Step 5 (5 minutes): Colors that show where to look

What to do: Color any line that's off, automatically, so at a glance you see the three things to talk about.

How: In the "Tracking" tab, select A2:I40 and choose Format > Conditional formatting > Custom formula is. Add two rules:

Red:    =OR(AND($B2="Expense",$F2<>"",$F2>0.1),AND($B2="Income",$F2<>"",$F2<-0.1))
Yellow: =OR(AND($B2="Expense",$F2<>"",$F2>0.05),AND($B2="Income",$F2<>"",$F2<-0.05))

Put the red rule first. The 10% and 5% thresholds are a starting point. On a big line even 3% is a lot of money, so adjust.

Step 6 (10 minutes): The Goals tab

What to do: Write the year's goals, not only financial ones, and calculate percent achieved.

How: In the "Goals" tab, in A1:

Goal	Measure	Owner	Annual target	Target to date	Actual	% achieved	Status	What we do

• Target to date (column E): usually =D2*Tracking!$K$1/12. For a seasonal goal, type it by hand.
• Actual (column F): fill in every month. For a money goal, link it to the Tracking tab, for example =Tracking!D2.
• % achieved and status (columns G and H):

G2: =IF(E2=0,"",F2/E2)
H2: =IF(G2="","",IF(G2>=1,"On track",IF(G2>=0.9,"Close","Behind")))

Common mistake: a goal with no owner and nothing under "What we do". A goal marked "Behind" two months in a row with an empty column I is exactly what belongs on the next management meeting's agenda.

Step 7 (5 minutes): A monthly report draft for management or the board

What to do: At the end of each month, update K1, copy the "Tracking" and "Goals" tabs, and run the prompt.

You're writing a draft monthly report for management. Here is the tracking tab (line | type | budget to date | actual to date | gap | gap % | year-end forecast | annual budget | expected overrun):
[paste]
And here is the goals tab:
[paste]
My notes on the month (optional):
[for example: a large client paid late, a campaign was brought forward]

Binding rules:
1. Every number in the report is copied from the tables. Don't recalculate and don't round differently.
2. Only give a reason for a gap if it appears in my notes. Otherwise write "Reason: [to check]".
3. Don't recommend cuts or investments. Instead, write questions for a decision.

The structure, one page at most:
a. Three lines: where we are against budget, the year-end forecast, and the one thing that needs attention.
b. Up to five lines with the largest gap in money, each with its reason or [to check].
c. Goals: what's on track, what's close, what's behind.
d. Up to three questions for a decision at the meeting.

A monthly routine (20 minutes a month)

1. Import the month's transactions into the "Actuals" tab and assign them to lines.
2. Update the last closed month in K1 on the "Tracking" tab.
3. Fill in the "Actual" column on the "Goals" tab.
4. Run the report prompt, fill in every [to check], and send.

Don't change the original budget during the year. If there's an update, add a "Revised budget" column, so you can always compare against the plan that was approved.

Full example: Balance Advisory (a fictional business)

Everything in this example is fictional: the firm, the lines and the amounts. The numbers are only for illustration.

The assumptions written in Step 2: retainer revenue +8%, one-off projects the same as last year, payroll +4%, one more consultant from April costing 24,000 a month, rent unchanged.

The Tracking tab after September (K1 = 9, shortened):

Checking one row: in marketing, the annual budget of 120,000 is split evenly, 10,000 a month. Through September that's 90,000. The actual is 112,000, a 24% gap. The forecast: 112,000 plus three months of 10,000, which is 142,000.

What the monthly report said (shortened): "Results to date are below budget, mainly because of one-off projects (17% under plan, reason: [to check]). Marketing is 24% over to date, reason: the Q4 campaign was brought forward to September. Question for a decision: do we cut Q4 marketing by 22,000 to get back to the annual budget?"

Notice: no reason was invented for the projects gap, because there wasn't one in the notes. The marketing reason came from the note written before the run.

Checklist before every report

• Every row on the "Budget" tab says "OK" in the Check column
• No transaction on the "Actuals" tab is without a line, and no [to check] is left
• Loans, transfers between accounts and VAT weren't counted as expenses
• K1 is set to the last closed month
• The month's actuals are close to what your accountant sees, or the gap is explained
• Every [to check] in the report is filled in or kept as an open question
• The original budget wasn't overwritten

Disclaimer

This content is general recommendations only, not professional, legal, accounting or tax advice. Every business and organization is different. You are responsible for checking that it fits your situation, and for checking the data, prompts and formulas before you use them. The sheet is a management and tracking tool and doesn't replace your financial statements or your bookkeeping. Outside tools, prices and terms of service change, so check them at the source. For anything involving money, VAT, tax or reporting, consult a certified accountant or tax adviser before you rely on the result.

The content, prompts, templates and code are provided as is, with no warranty that they fit a particular purpose or are free of errors. Gindi Innovation and Aviad Gindi are not liable for any damage, direct or indirect, arising from using them or relying on them, including output from AI tools. Use is subject to the site terms.

What's next

This kit works with a manual monthly import. The same sheet can be connected so transactions come in on their own from your bookkeeping, assignment to lines happens automatically and waits for your approval, and the monthly report reaches you ready at the start of each month. The decisions stay yours.