Gas station financial model in Excel: fuel and store sales

A gas station financial model in Excel separates fuel volume, fuel gross profit, convenience-store gross profit and operating expenses. Start with the approved plan and actual sales and inventory records, then use the same formulas for both columns.

This is an operating planning workbook for a US station. It compares a month with plan; station acquisition valuation, lending and tax decisions need separate work.

Start with files that describe the same month

Collect the fuel sales export by grade, the store sales export by merchandise category, inventory cost schedules, the expense ledger and the approved budget. Keep station ID and reporting month on every tab. Record whether revenue excludes sales taxes and whether discounts have already been deducted.

Use net revenue and inventory costs consistently. The IRS describes purchased merchandise cost as invoice price less appropriate discounts plus transportation and other acquisition charges. That supports a useful control: supplier cash payments alone do not establish the cost of what the station sold.

Use your finance team's established inventory policy and cost allocation. This workbook does not prescribe a tax method. For upstream production economics, the neighboring oil and gas financial model addresses a different operating business.

Enter the planning drivers

On a Comparison sheet, place the line labels in A, plan in B and actual in C. The following September example uses one station. Average prices and inventory costs are weighted by gallons, rather than averages of grade-level averages.

Actual realized price should reconcile to actual net fuel revenue divided by gallons. Actual cost per gallon should reconcile to fuel cost of goods sold divided by those gallons. The rounded inputs above are exact assumptions for this worked example; retain unrounded inputs in a working file.

Keep card fees, labor, utilities, rent and repairs in the expense schedule under your reporting policy. If you include an item in cost of goods sold, exclude it from operating expenses so it enters the comparison once.

Calculate the operating result

Put these outputs below the input section. Copy the formulas from B to C for actuals.

Operating contribution here is gross profit less the listed station operating expenses. It excludes any items outside that schedule; label it explicitly rather than calling it net income.

Fuel gross profit per gallon is:

=B12/B2

The plan returns $0.35 per gallon; actual returns $0.32. For percentage gross margin, divide fuel gross profit by fuel revenue instead. Plan fuel margin is 10.00%; actual is 9.41%, rounded to two decimal places. The gross profit margin guide explains the denominator and why percentages should not be averaged.

Bring actual transactions into the comparison

Suppose Sales has month in A, station in B, sales type in C and net revenue in D. Put the selected month in F2 and station in F3. Actual fuel revenue is:

=SUMIFS(Sales!$D$2:$D$10000,Sales!$A$2:$A$10000,$F$2,Sales!$B$2:$B$10000,$F$3,Sales!$C$2:$C$10000,"Fuel")

Microsoft documents SUMIFS as summing cells that meet multiple criteria. Use equivalent controls for store revenue, gallons and expenses. Confirm that the selected ranges include the complete export after each update. Covirage is not affiliated with Microsoft.

For a larger workbook with related tables, the existing Power Pivot guide covers that spreadsheet approach. Keep the same reconciliation requirements whichever method produces the summary.

Explain the variance with a proof

Actual fuel revenue rose to $357,000, but fuel gross profit fell by $1,400. The volume effect is additional gallons at planned gross profit per gallon. The margin effect applies the change in per-gallon gross profit to actual gallons:

=(C2-B2)*(B3-B4)
=C2*((C3-C4)-(B3-B4))

The first formula returns $1,750. The second returns −$3,150. Together they equal −$1,400, exactly the change from $35,000 to $33,600. This bridge assigns the interaction to the margin effect; keep that convention unchanged between periods.

Store gross profit rises by $50. Total gross profit therefore falls by $1,350, and operating expenses rise by $1,500. Contribution falls by $2,850, from $18,000 to $15,150. Those movements must equal =C15-B15.

Before explaining that result, tie sales totals to the source reports and cost totals to inventory schedules. The supplied store inventory schedule reconciles as follows, assuming no other inventory adjustments:

The resulting cost equals the actual store cost of goods sold in the input table.

Where it goes wrong

• Purchases used as cost of sales. Inventory movements can separate delivery spending from the cost of products sold.
• Grade averages given equal weight. Compute weighted realized price from total fuel revenue and total gallons.
• Fees deducted twice. A fee already netted from a source amount must not also appear as an expense without an offsetting adjustment.
• A stronger sales total mistaken for stronger earnings. Read gross profit and the expense bridge alongside revenue.

Compare your station files in Covirage

Bring a sample of your own fuel, store and expense workbook: Covirage maps the columns, and its tools compute and check the supplied figures. The external AI model explains actual performance against supplied assumptions with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring your export.

See FP&A reporting for the supporting planning context.