Build the Income Statement with AI-Written Formulas

Now you start building. The income statement is the easiest statement to forecast, because almost every line comes straight from an assumption. It is a good place to learn the habit you will use for the rest of the model: describe your layout to AI, get a formula, paste it in, and check the result against a number you already know.

What You'll Learn

• How to describe a spreadsheet layout so AI writes formulas that fit
• The formulas for every income statement line in Year 1
• Why interest is calculated on last year's debt
• The exact numbers your income statement should show

Describe the layout, then ask

AI writes much better formulas when it knows exactly where things are. Vague prompts like "give me a revenue formula" get vague answers. Give it rows, columns, and what each cell holds.

The last sentence matters. "Must work when copied right" pushes AI to use relative references correctly, so you write Year 1 once and fill across.

The formulas you should end up with

Compare the AI's answer with this table. Small differences are fine (for example =C17-C18 versus =SUM(C17,-C18)), as long as the logic matches.

Cell	Line	Formula	Logic
C17	Revenue	=C3	Pull the forecast
C18	Cost of goods sold	=C17*C4	Revenue times COGS %
C19	Gross profit	=C17-C18	Revenue minus COGS
C20	Operating expenses	=C17*C5	Revenue times opex %
C21	EBITDA	=C19-C20	Gross profit minus opex
C22	Depreciation	=C6	Pull the input
C23	Operating profit (EBIT)	=C21-C22	EBITDA minus depreciation
C24	Interest	=B36*C8	Last year's debt times rate
C25	Profit before tax	=C23-C24	EBIT minus interest
C26	Tax	=C25*C10	Profit before tax times rate
C27	Net income	=C25-C26	Profit after tax

Type or paste these into C17:C27, then select C17:C27 and fill right to column E.

Why interest uses last year's debt

Look at C24: =B36*C8. It multiplies the Year 0 closing debt (cell B36), which is the debt Brightcup owes going into Year 1, by the Year 1 rate. This is a simple, common convention. It is the one this course uses, so your numbers match the tables.

It is not the only valid choice. Because Brightcup repays a fixed 50 each year, you could also charge interest on the closing debt (=C36*C8) or on the average of opening and closing debt. You would get different interest numbers, and the model would still balance. These are conventions, not errors.

When circular references do appear

A circular reference is a formula that depends on its own result. In a 3-statement model it shows up when debt depends on cash. Two common examples:

• A cash sweep: the company repays as much debt as its spare cash allows.
• A credit line: the company borrows automatically when cash runs low.

In both cases interest affects net income, which affects cash, which affects debt, which affects interest again. Interest earned on the cash balance creates the same loop. Full models handle this with special settings, and charging interest on opening debt is a common way to avoid the loop altogether. Brightcup has none of these features, so it has no loop either way.

If AI gives you =C36*C8, it is not wrong. Ask it which convention it is using and why, then switch to =B36*C8 so your results match the course.

Check your numbers

Once the whole model is built, your income statement should match this exactly:

Line	Year 1	Year 2	Year 3
Revenue	1,100	1,200	1,300
Cost of goods sold	660	720	780
Gross profit	440	480	520
Operating expenses	275	300	325
EBITDA	165	180	195
Depreciation	50	50	50
Operating profit (EBIT)	115	130	145
Interest	12	9	6
Profit before tax	103	121	139
Tax	20.6	24.2	27.8
Net income	82.4	96.8	111.2

Interest in Years 2 and 3 will show 0 until you build the debt line in the next lesson, because D24 and E24 point to C36 and D36, which are still empty. That makes everything below interest too high in Years 2 and 3 for now. It is expected. Year 1 should already match.

Let the spreadsheet do the math

You might be tempted to paste the whole table into AI and ask it to "calculate the income statement." Avoid that. Language models can make arithmetic slips, especially across many linked lines. The right split is:

• AI writes and explains formulas.
• The spreadsheet calculates.
• You check results against known numbers.

This split also means your model stays live. Change revenue in row 3 and every line updates. A table of numbers typed from an AI answer cannot do that.

Read the income statement like an analyst

Once the numbers match, ask AI to interpret them.

Then check the margins in your sheet with =C27/C17. You should get about 7.5%, 8.1%, and 8.6%. The reason net income grows faster: COGS and operating expenses grow with revenue, but two costs do not. Depreciation stays at 50 each year, so operating margin (=C23/C17) already rises, from about 10.5% to 10.8% to 11.2%. Interest falls as debt is repaid, which lifts net margin further.

Key Takeaways

• Give AI exact rows and columns, and ask for formulas that work when copied right.
• Most income statement lines are an assumption times revenue or a subtraction of the line above.
• This course calculates interest on opening debt (=B36*C8), a simple, common convention. Circular references only appear when debt or interest depends on cash.
• Check every line against known numbers. Year 1 net income should be 82.4.
• AI writes and explains formulas; the spreadsheet does the math.