How to build a driver-based 2027 budget in Excel with Copilot or ChatGPT
A driver-based budget calculates every line of the 2027 plan from a small set of operating assumptions: units sold, average price, unit cost, headcount, cost per employee and a few rates tied to revenue. Change one driver and revenue, gross margin, operating expenses and EBITDA move together, which is why controllers and FP&A teams prefer it to a budget typed in line by line. Copilot in Excel and ChatGPT can both draft the structure, write the formulas and run scenarios, but neither replaces the finance team's judgement on the drivers themselves or the check of the numbers they produce.
The practical split between the two tools is simple. Copilot in Excel works inside the workbook and, in edit mode, changes it directly; Microsoft lists a built-in budget-and-forecast skill that builds budgets and forecasts from actuals and departmental inputs and models scenarios. ChatGPT works on an uploaded copy of the file, writes and runs Python to calculate the plan, and returns tables, charts and files that the analyst then brings back into the workbook. Both vendors state that their output can contain mistakes, so the worked example below comes with its correct result, computed independently, for checking whatever the assistant produces.
··········
WHAT A DRIVER-BASED BUDGET IN EXCEL CONTAINS.
A driver-based 2027 budget has three layers. The first is an inputs block that holds every assumption in one place, with a label, a unit and a source: volume by quarter from the sales plan, the price increase approved by management, the purchase cost from supplier quotes, the hiring plan from department heads and the fixed costs from signed contracts. The second is a calculation block where every cell is a formula pointing back to the inputs, with no hardcoded numbers. The third is an output block that presents the budgeted income statement by quarter and for the year, together with the margin percentages that management will review.
The discipline that makes the model useful is the separation of inputs from formulas. When a reviewer asks what happens if volume turns out ten percent lower, the analyst changes one input and the whole plan recalculates. When the same reviewer asks where a number comes from, the formula trail leads back to a named assumption. This structure also prepares the ground for the monthly comparison of actuals with plan, described step by step in our guide on running a budget vs actual variance analysis with Copilot in Excel.
The budget is different from a rolling forecast, which is updated during the year to reflect the latest expectations. The distinction, and why most companies need both, is covered in our article on budgeting vs forecasting. The method described here produces the annual budget that is approved before the year starts and then held fixed for performance measurement.
··········
COPILOT IN EXCEL AND CHATGPT: DOCUMENTED REQUIREMENTS AND LIMITS.
The requirements below come from Microsoft Support and the OpenAI Help Center, as verified on 1 November 2026. Availability can vary by license, region and, for enterprise users, by tenant configuration, so the IT administrator's settings decide what a given user actually sees.
........
Item | Copilot in Excel | ChatGPT |
Where it works | Inside the workbook | On an uploaded copy of the file |
Eligible plans | Microsoft 365 Premium, commercial Copilot, Copilot Chat-eligible business plans, Personal or Family with AI credits | File uploads limited to 3 per day on Free; full uploads on Plus and Enterprise |
Budget-specific help | Built-in budget-and-forecast skill, called with @ | Data analysis runs Python on the uploaded data |
Editing | Edit mode changes the workbook directly; plan and chat modes also available | Returns tables, charts and files to download |
File limits | Calculation Options must be Automatic for editing; Strict Open XML files must be saved as .xlsx | 512 MB per file; spreadsheets about 50 MB depending on row size; 10 files per conversation |
Vendor caution | Review, edit and verify anything Copilot creates | Check the code through View Analysis |
........
Microsoft's documentation adds a few points that affect budgeting work. Edit, chat and plan modes are available on Windows, Mac and the web, while the mobile apps have a narrower set. For SharePoint sites that require check-out before editing, Copilot in Excel on Windows or Mac does not work on those files. Saved Copilot changes are visible to anyone with access to the file, including coauthors, and individual Copilot conversations cannot currently be deleted. Users with a commercial Copilot or Microsoft 365 Premium subscription can switch models, and Claude becomes available only after an enterprise administrator enables Anthropic as a sub-processor.
Microsoft also describes the accounting skills, which include budget-and-forecast and variance-analysis, as rolling out through the Microsoft Frontier program, so some tenants will not see them yet. Without the skill, Copilot can still be asked in plain language to build the structure; the skill only packages the instructions for this type of task. Microsoft states that Copilot does not provide accounting, tax, audit or financial advice and that all output must be verified against source records and company policy.
On the OpenAI side, the Help Center describes data analysis as ChatGPT writing Python code and running it in an isolated execution environment that cannot make outbound network requests. That environment is destroyed within 13 hours of the conversation becoming inactive. Files uploaded to a conversation are kept for the retention period of the chat and removed within 30 days of deletion, subject to the security and legal exceptions OpenAI lists.
··········
THE WORKED EXAMPLE: A QUARTERLY 2027 BUDGET FROM SIX DRIVERS.
The example uses an illustrative company that sells one product line. The figures are invented for teaching purposes and are kept small enough to check by hand. The 2026 price is $120.00 per unit and the 2026 unit cost is $54.00. For 2027, management approves a 5% price increase, purchasing expects a 3% rise in unit cost, sales commissions stay at 4% of revenue, marketing is budgeted at 6% of revenue, the fully loaded cost per employee is $96,000 a year, and rent and IT are fixed at $45,000 a quarter.
........
Driver (illustrative) | Q1 2027 | Q2 2027 | Q3 2027 | Q4 2027 |
Units sold | 11,500 | 12,400 | 12,900 | 14,200 |
Price per unit | $126.00 | $126.00 | $126.00 | $126.00 |
Unit cost | $55.62 | $55.62 | $55.62 | $55.62 |
Headcount (FTE) | 22 | 24 | 25 | 26 |
Cost per FTE per quarter | $24,000 | $24,000 | $24,000 | $24,000 |
Rent and IT | $45,000 | $45,000 | $45,000 | $45,000 |
........
The price of $126.00 is $120.00 increased by 5%, and the unit cost of $55.62 is $54.00 increased by 3%. The quarterly cost per employee is the annual $96,000 divided by four. Commissions and marketing are not listed as amounts because they are rates applied to revenue, which is the core of the driver-based approach.
In the workbook, the inputs sit on an Inputs sheet with defined names, and the calculation sheet holds one formula per line that refers to them. The formulas below are standard Excel syntax, shown for quarter 1 in column C, with units in row 5 and headcount in row 6; the other quarters copy across.
Revenue =C5*Price_2027
Cost of goods sold =C5*UnitCost_2027
Gross profit =C8-C9
Commissions =C8*Commission_Rate
Marketing =C8*Marketing_Rate
Payroll =C6*Cost_per_FTE/4
Rent and IT =Fixed_Quarter
Operating expenses =SUM(C11:C14)
EBITDA =C10-C15With Copilot in Excel, the prompt should name the sheets and ranges it has to use, which is Microsoft's own advice for effective prompts. Plan mode is a sensible first step because it shows the intended changes before anything is written into the workbook.
@budget-and-forecast Build a 2027 quarterly budget on a new sheet called Budget_2027.
Use only the defined names on the Inputs sheet (Price_2027, UnitCost_2027,
Commission_Rate, Marketing_Rate, Cost_per_FTE, Fixed_Quarter) and the units and
headcount in Inputs!C5:F6. Every cell must be a formula; do not type numbers.
Show revenue, COGS, gross profit, commissions, marketing, payroll, rent and IT,
operating expenses and EBITDA by quarter, with a full-year total and gross margin
and EBITDA margin percentages.With ChatGPT, the analyst uploads the inputs workbook and asks for both the calculation and the formula layout, so the result can be rebuilt in Excel and compared.
The attached file contains my 2027 budget drivers on the Inputs sheet.
1. Calculate a quarterly 2027 budget: revenue, COGS, gross profit, commissions (4% of
revenue), marketing (6% of revenue), payroll (FTE x 96,000 / 4), rent and IT
(45,000 per quarter), operating expenses and EBITDA, plus full-year totals.
2. Show the Python you used and list every assumption you read from the file.
3. Give me the Excel formula for each line, referring to the input cells, so I can
rebuild the model in my workbook.··········
THE CORRECT RESULT TO CHECK THE AI OUTPUT AGAINST.
The table below is the expected result of the example, calculated independently in Python from the drivers above. Any version produced by Copilot or ChatGPT should match it to the dollar. A difference usually points to a formula that refers to the wrong cell, a rate applied to gross profit instead of revenue, or payroll calculated on the annual cost without dividing by four.
........
2027 budget (illustrative) | Q1 | Q2 | Q3 | Q4 | Full year |
Revenue | 1,449,000 | 1,562,400 | 1,625,400 | 1,789,200 | 6,426,000 |
Cost of goods sold | 639,630 | 689,688 | 717,498 | 789,804 | 2,836,620 |
Gross profit | 809,370 | 872,712 | 907,902 | 999,396 | 3,589,380 |
Commissions | 57,960 | 62,496 | 65,016 | 71,568 | 257,040 |
Marketing | 86,940 | 93,744 | 97,524 | 107,352 | 385,560 |
Payroll | 528,000 | 576,000 | 600,000 | 624,000 | 2,328,000 |
Rent and IT | 45,000 | 45,000 | 45,000 | 45,000 | 180,000 |
Operating expenses | 717,900 | 777,240 | 807,540 | 847,920 | 3,150,600 |
EBITDA | 91,470 | 95,472 | 100,362 | 151,476 | 438,780 |
........
On 51,000 units for the year, the budget gives a gross margin of 55.9% and an EBITDA margin of 6.8%. The thin EBITDA margin is the first thing a CFO will question, and it explains why the scenario step that follows carries so much weight in this model.
The scenario check uses the same drivers with one change at a time. A sensitivity test should always be run from the inputs block, never by overwriting results, and the expected figures are again computed in Python.
........
Scenario (illustrative) | Revenue | EBITDA | Change in EBITDA |
Base budget | 6,426,000 | 438,780 | 0 |
Volume 10% lower in every quarter | 5,783,400 | 144,102 | -294,678 |
Price 3% lower ($122.22 per unit) | 6,233,220 | 265,278 | -173,502 |
........
The volume scenario cuts EBITDA by about two thirds because payroll and rent do not fall with units sold. The price scenario costs $173,502 even though volume is unchanged, because the lost revenue falls almost entirely to EBITDA after the small saving in commissions and marketing. Results like these give management a concrete basis for deciding whether the hiring plan should be phased or tied to volume milestones. Our guide on using financial projections for budgeting and valuation covers how to document scenarios and the assumptions behind them.
··········
REVIEWING AN AI-BUILT 2027 BUDGET BEFORE APPROVAL.
The most frequent problems in an AI-drafted budget are structural. Hardcoded numbers appear inside formulas, for example a 1.05 typed into the revenue line instead of a reference to the price input, which breaks the model the first time the price assumption changes. Totals sometimes sum the wrong range, or the full-year column adds quarterly percentages instead of recalculating them from annual totals. Payroll is a common source of error when the assistant applies an annual cost per employee to a quarterly column. Microsoft's built-in check-model skill is documented as auditing financial models for broken formulas, circular references, hardcoded overrides and imbalances, and it is a useful second pass after the budget is built.
A control routine for the finance team can stay short. The reviewer recalculates two or three lines by hand for one quarter and compares them with the workbook, then uses Trace Precedents on revenue, payroll and EBITDA to confirm that every formula leads back to the Inputs sheet. Changing one driver, such as the price, should move every dependent line while the totals still add up. The full-year revenue, payroll and EBITDA are then compared with the prior-year actuals, and every large change is explained in writing. A copy of the workbook from before Copilot's edits should be kept, since saved changes are shared with everyone who has access to the file.
Some decisions must stay with people. The volume plan, the price increase, the hiring plan and the cost assumptions are commitments made by department heads and approved by management; the assistant only turns them into arithmetic. Where an assistant proposes a driver value on its own, such as a growth rate it inferred from history, that value should be marked as a proposal and replaced by an approved figure before the budget goes to the board. The same budget then becomes the cash plan: the 2027 revenue and cost lines feed the receipts and payments in a 13-week cash flow forecast in Excel, which tests whether the business can fund the hiring planned for the second half.
On data handling, the budget workbook usually contains salaries and supplier prices, so the choice of tool should follow company policy. Copilot in Excel works on the file where it is stored, under the Microsoft 365 security and privacy commitments that apply to the user's account type. ChatGPT works on an uploaded copy, and the retention rules quoted above apply to that copy. Payroll can also be budgeted at the department level rather than by named employee, which keeps personal data out of the file altogether.
··········
DRIVER-BASED BUDGETING WITH COPILOT AND CHATGPT IN PRACTICE.
Both tools can produce a correct 2027 driver-based budget when the inputs are clean, named and complete, and both can produce a wrong one that looks finished. Copilot in Excel has the advantage of working in the live workbook, with a dedicated budget-and-forecast skill where the tenant has it and a model-checking skill for review. ChatGPT is useful for calculating the plan independently in Python and for explaining the formula structure, but its output has to be rebuilt or imported into Excel, which adds a reconciliation step.
The parts that depend on the company cannot be delegated: the approved volume and price assumptions, the hiring plan, the treatment of costs that are partly fixed and partly variable, and the internal approval route. The illustrative model above fits on one sheet; a real budget adds departments, monthly phasing, capital expenditure and a balance sheet, and every one of those additions needs the same separation of inputs from formulas and the same independent check of the result before the plan is approved.
··········
FOLLOW US FOR MORE.
·····
DATA STUDIOS
·····
datastudios.org
