Building Crop Gross Margins With AI

Most cropping plans are decided in the kitchen in October, and most of them look a lot like last year’s. Wheat goes where wheat always goes, rape goes in when the rotation says so, and the spring beans happen because “we need a break.” Sometimes that’s right. Often nobody has checked.

This post shows you how to check. You’ll use a chat assistant (ChatGPT, Claude or Microsoft Copilot all work) and an ordinary spreadsheet to build a crop gross margin calculator that works at field level, under three price scenarios. It takes about an evening. After that, each cropping argument comes down to a number you can point at.

Why field level, and why three prices

Farm-average gross margins are tidy, but they hide the field that drags everything down. Your best block of first wheat and your blackgrass-ridden clay are both averaged into one “wheat” figure, and the clay gets carried by the good ground.

One price also isn’t enough. Nobody knows what feed wheat will make next harvest, and a margin calculated at a single guessed price is a guess. Calculate at low, mid and high prices and you can see which crop holds up when markets turn against you and which only works when they’re kind.

What you need before you start

Gather these first. The AI can’t invent them, and if you let it fill the gaps it will produce confident-looking numbers with nothing behind them.

• Field list with hectares. Export it from Gatekeeper, Muddy Boots, Farmplan or your RPA land parcels, or type it in.
• Yields by field for the last three to five years. Combine yield maps help, but weighbridge tickets divided by field area will do.
• Variable costs per hectare: seed, fertiliser, sprays, and anything else that varies with the crop (drying, haulage, contract spraying if you pay per hectare).
• Price ranges. Use AHDB’s ex-farm price data and current forward prices for a starting point, then widen them to cover the swings of the last few seasons.

If you lack your own cost data for a crop you haven’t grown, the John Nix Pocketbook or the ABC Agricultural Budgeting and Costing Book will give you sensible figures to start from. Mark those cells in a different colour so you remember they’re borrowed.

Step 1: Get the AI to build the sheet, not do the sums

People go wrong here. Chat assistants are good at structure and poor at reliable arithmetic across a big table. So ask for formulas, then let Excel or Google Sheets do the maths.

Here’s a prompt that works:

I farm in the UK and want to build a crop gross margin calculator in Excel.
Each row is one field and one crop option. Columns I need:
Field, Hectares, Crop, Yield (t/ha), Seed £/ha, Fertiliser £/ha,
Sprays £/ha, Other variable costs £/ha, Price Low £/t, Price Mid £/t,
Price High £/t, then Gross Margin £/ha at each price, then whole-field
Gross Margin £ at the mid price.
Give me the column letters and the exact formulas for row 2.
Do not calculate any numbers yourself.

Ask Claude or ChatGPT for this and you’ll get back something like:

A Field | B Hectares | C Crop | D Yield | E Seed | F Fert | G Sprays
H Other VC | I Price Low | J Price Mid | K Price High
L GM Low £/ha   =D2*I2-SUM(E2:H2)
M GM Mid £/ha   =D2*J2-SUM(E2:H2)
N GM High £/ha  =D2*K2-SUM(E2:H2)
O Field GM Mid £ =M2*B2

Copy those formulas down. If you use Excel with Copilot switched on, you can type the same request into the Copilot pane and it will write the formulas into the sheet for you. Google Sheets with Gemini can do the same. A free ChatGPT or Claude account is plenty for this job; the paid tiers (around £20 a month) are useful later, when you upload a full spreadsheet and ask questions about it.

Step 2: Put real numbers in

Here’s a worked example for a mixed arable holding, with three fields. Church Piece gets two rows because it’s the field in question: second wheat, or spring beans?

The second wheat on Church Piece carries an extra £100/ha over Top Barn: a seed treatment for take-all, and a heavier herbicide programme because the blackgrass is getting away. Its yield is the five-year average for that field as a second wheat, not a figure from a textbook.

When the formulas run, the sheet gives you this:

Field         Crop          GM Low   GM Mid   GM High   (£/ha)
Top Barn      1st wheat       879    1,094     1,309
Long Meadow   Winter OSR      652      817       982
Church Piece  2nd wheat       499      671       844
Church Piece  Spring beans    520      646       772

Top Barn is no contest, so put wheat there. The rape on Long Meadow earns its place at mid prices. Church Piece is where habit and arithmetic part company.

Step 3: Read Church Piece properly

At the mid price, second wheat beats beans by £25/ha. Across 21 hectares that’s £525 for the whole field. At the low price, beans win by £21/ha. At the high price, wheat wins by £72/ha, or £1,512 across the field.

So on gross margin alone the two crops are close to level, and the result depends on a wheat price nobody can forecast. That’s useful to know already. Now ask the chat assistant a follow-up:

Using the formulas above, what wheat price makes second wheat on
Church Piece equal to spring beans at the mid bean price?
Show the formula so I can put it in the sheet.

The answer is (646 + 640) ÷ 6.9, which comes to about £186/t. Below that, beans are the better crop on Church Piece. Put the formula in a cell next to the table. When forward prices move in February, you’ll see straight away which side of the line you’re on.

Step 4: Add what the gross margin leaves out

A gross margin only covers one crop for one year. The beans do something the table doesn’t show: the wheat that follows them is a first wheat, not a third.

Take conservative figures from your own records: suppose the wheat after beans yields 0.5 t/ha more than a continuing wheat would, and saves about 30 kg/ha of nitrogen. At £190/t and roughly 80p per kg of N, that’s £95 plus £24, or £119/ha the following year. On 21 hectares that’s £2,499, which is far more than the £525 the wheat led by this year.

Add this as its own clearly labelled row in the sheet (“Rotational benefit to following crop”) so it doesn’t hide inside the bean yield. Keep any scheme payments, such as SFI actions linked to a field, on separate lines too. Mixing subsidy into crop output is the quickest way to fool yourself about which crop actually pays. For a fuller look at sequencing crops over several seasons, the AI for Crop Planning and Rotations guide covers multi-year rotation modelling with the same tools.

Fixed costs stay out of this sheet on purpose. Machinery, labour and rent don’t change much whether Church Piece grows beans or wheat, so they don’t help you choose between them. If going into beans means hiring a contractor to combine them or buying a new header, put that cost in “Other variable costs” for the beans row.

Where the AI earns its keep, and where it doesn’t

Typing up the table is the least useful part. Once the sheet exists, upload it (ChatGPT, Claude and Copilot in Excel all read .xlsx files) and use the assistant as a sceptical farm business consultant:

• “Which field has the widest gap between low and high price margins, and what does that say about risk?”
• “My OSR yield on Long Meadow is 3.3 t/ha. Flea beetle took out two of the last five crops. Recalculate using the five-year average including the failures.”
• “List every assumption in this sheet that I haven’t backed with my own data.”

The last prompt matters most. In testing, assistants will flag the borrowed Nix costs and the guessed price ranges, and that’s precisely what you need to tighten before the plan is finalised.

The AI should never be the one you trust on totals. If the chat window says the field margin is £14,091, check that the spreadsheet cell says the same. Large language models sometimes slip a digit in long calculations, and they give no sign that they’ve done it. The formulas don’t make that mistake.

Last, don’t hand over more than the job needs. Field names and hectares are fine. Your bank details, buyer contracts and anything with personal data belong nowhere near a chat window. On a business Microsoft 365 or Google Workspace account, the built-in Copilot or Gemini works inside your own tenancy, which suits the cautious.

Running it again in spring

Save the sheet. When the March price update comes, change three cells per crop and read the new margins. If wheat has fallen below that £186 break-even, you’ll know before the seed order is fixed rather than after harvest. If it’s risen to £215, you can go with the second wheat knowing what you’re giving up on the rotation side.

So if Church Piece goes into wheat again next year, it’ll be because of the numbers and not because it always has.