In our case, we can get a better fit by allowing the curve to be a 6th degree polynomial.

If we are given equations that model the processes we are interested in, then this approach works.

What happens though if we are not given equations Many important functions in business are quite often defined by data.

Examples include past sales, material costs, and consumer demand.

A straightforward approach is to assume that the data represents the output of a nice formula.

In real life applications we will often see that so-called noise can complicate the situation.

For example, if I am looking at sales at a fast food restaurant, our model will have noise from traffic jams and bad weather outside.) For the purpose of this course we will assume that the data will be reasonably nice, although some noise may be evident.

The problem of producing a best fitting curve to data can be broken into two pieces.

The models that come up repeatedly in the theoretical courses are given names and used without redoing the theoretical foundation for the model.

This is why we introduced the normal distribution and the logistic growth function, neither of which looks like a simple equation.) In this course, we will be happy with simple heuristic arguments on which model to choose.

A moment of reflection shows deciding on the correct definition of best fitting is a nontrivial task beyond the scope of this course.

We will come back to that definition later in the course, when we know more calculus, but for now we simply note that it is the standard definition, and is used by Excel.

Instead, we will focus on using Excel to produce a best fitting curve of the appropriate model.

Excel has a preprogrammed feature that will find the best fitting equation for a data set for a select number of functions.

It is only in recent times that it has also become a small computer GUI unit.).

Since we can see two entries each, for 36, 43, and 44 hours worked, there cannot be a function that hits all our data exactly.

While we expect a linear function, we are not surprised if there is random noise, as a worker may take a break, or be particularly focused on a given day.

Given that we suspect the number of widget produced will be roughly proportional to the hours worked, we want to use a linear model, so we make that choice.

Under options, we want to display the equation on the chart.

We could use that equation to plan how many hours we want our workers on the job based on the number of widgets we expect to sell.

You should notice that the equation Excel produces in the chart is written in standard mathematical notation, while the corresponding equation in cell B3 is in Excel notation.

In Excel notation we need a symbol for multiplication rather than simply putting a number and variable together.

In Excel notation, we also use a cell reference, B1, rather than a variable, x.).

However we should be careful about using it on too wide a domain.

In the other direction it predicts that a worker who worked 168 ( 7 x 24) hours a week would produce almost 970 widgets, instead of predicting a collapse from exhaustion.

An argument could easily be made for a proportional model.

A worker who works no hours produces no widgets.) We can switch to the proportional model by setting the y-intercept to 0 in options for the trend line.

Linear Regression Excel For Mac