Using Linear Trendline Equation in Excel

Excel Trendline Formula - Powerful, Simple, Confusing

Simple Excel Scenario to solve. You have been producing units, market gives you a price and you want to analyse the relationship between units/price to figure out if you should upscale production.

(in C15, you could just use =TREND(C3:C12,B3:B12,C14) but that makes it too easy)

First step is to simply create the scatter graph and draw the trendline. Right click on the trendline, “Formal Trendline” and click “Display Equation on chart”.

Basic stuff - you’ve probably put the equation on the chart before to make it look like you understand it. We’ve all been there.

Right click on the trendline equation, “Format Trendline Label”, change “Category” to “Number” and add 10 decimals. Add more if you feel it is required.

You now have y = -0.0000552668x + 11.7655267125
Copy this to an Excel cell. Double click on the equation and ctrl c to copy, click on the cell and ctrl v to paste.

It means this: Price (y axis) of a unit (x axis) =-0.0000552668*C14(your x axis)+11.7655267125

Simple as that!
You don’t even need to convert it to a number with decimals, = -5.5266776175E-05*C14 + 1.1765526713E+01 works just as well - Excel converts it for you. Go back and try it.

Now the Trend-y Excel Fun Begins

Using your snazzy new equation, you can model out exactly how much you should upscale production. I used the formula on units ranging from 1 to 200,000 and modeled out the revenue.

Approx 107,000 units is the magic number. Supporting that decision is linear trendline equation, which is a great quantitative piece of evidence.

Of course in the real world, you would need some more datapoints before making your equation.

Let me know how you can use this easy model to amaze your managers. If you are still in the mood for linear modelling, check out my Excel Linear Forecast Model Tutorial.