Categories
Application Excel Training MS Office Resources

Excel – Linear Estimations for Better Decision Making

Excel is a brilliant tool for data analysis.

A useful function or two can help, as several factors can impact your data. Weather, conflicts around the world, inflation, the interest rate, currency rates, subcontractors, number of customers, competitors, special offers, market size etc.

Function

Trend Function and Regression Tool

In this blog post, we’ll be looking at the TREND function and the REGRESSION tool from the ANALYSIS TOOLPAK and how you can use them.

In this example, expected key influencers on sales numbers are marketing expenses, how many salespeople, number of sales calls, and the dollar value. Many clients are in the USA.

Function

At this point, it is  a clever idea to analyse if these key influencers are the right variables to get accurate estimates. You can do this with the REGRESSION tool from the Excel ANALYSIS TOOLPAK.

In the Regression dialog box, enter the range of your y-values (dependent variable) in the Input Y Range field. Enter the range of your x-values (independent variable) in the Input X Range field. Check the “Labels” box if your data has column labels.

Choose an output location for the results of the regression analysis. Check any additional output options you want to include, such as confidence intervals or residuals. Click OK to run the regression analysis. The results, including the slope, intercept, R-squared, and other statistics, will be displayed in the output location you chose.

The F-Test

The F-Test of overall significance in regression is a test of whether your linear regression model provides a better fit to a dataset than a model with no predictor variables. Linear regression needs the relationship between the independent and dependent variables to be linear. The significance “F” gives you is the probability that the model is wrong.

Highlighted in the below screen grab. We want the significance F or the probability of being wrong to be as small as possible. Smaller is better. Many mathematicians agree that a significance F > 0.005 indicates that the model is wrong. This would mean that the data set cannot be treated as linear.

The significance F in the output report is almost nothing. It indicates that the linear approach is correct. Next step is to identify if all the independent variables are significant for an accurate estimation. The P-Value will tell you.

Function

The P Value

A p-value < 0.05 is good. A p-value< 0.05 means there is a 5% probability that there is no relationship between the variables.

The p-value for the independent variable GBP to USD is 36% which means that there is a 36% probability that there is no relationship between the variables, and for the accuracy of the predictions it should not be taken to account.

The other 3 independent variables return a low p-value and are closely related to the sales and will be important for the estimation of the sales.

The report also shows the slopes.

Marketing Expenses 7.753195112
Sales reps 16171.55994
sales calls 94.93927718

 

Each pound spent on marketing returns in average 7.75 pounds in sales, 1 sales rep returns in average 16,171 pounds in sales, and each sales call returns in average 95 pounds in sales.

The Trend Function

After finding the independent variables which are important for accurate estimations you can use the linear regression equation to calculate your predictions, but you can also use the TREND function.

The formula for simple linear regression is Y = mX + b. Where Y is the response (dependent) variable, X is the predictor (independent) variable, m is the estimated slope, and b is the estimated intercept.

We have completed the regression report again. But this time, without the unreliable independent variable GBP to USD and the corrected slopes can now be used in the linear regression equation.

In this example, we are calculating estimated sales based on 5000, 10000, 15000 spent on marketing, and 10, 15, 20 sales reps, and 1000, 1500, 2000 sales calls.

When the right independent variables have been recognised by the regression report you will not need the report to give you the slopes.

The regression report has recognised the correct independent variables. Once this has happened, you will not need the report to give you the slopes.

The TREND function will calculate the slopes for you and return the same estimates as the equation.

Conclusion

Excel is a fantastic tool for data analysis. Here we shared an example of linear data regression. You can also find tools to analyse and create estimates for all kind of data sets in Excel.

Categories
Application Excel Training Hints & Tips Microsoft MS Office Resources Office 365

How to ‘Freeze Panes’ in Excel

How often when using Excel have you had to scroll down to find what you’re looking for in a huge spreadsheet only to find your top headings disappear off the screen? Well worry no more. The solution lies in using an incredibly handy tool called Freeze Panes. This tool ‘freezes’ the top row and left column headings making it easier to check data against the appropriate headings when scrolling. This can increase your productivity and save you so much time

What are Freeze Panes

The images below show 2 separate ways to ‘freeze’ panes:

  1. Freezing top rows, scroll down

scroll

  1. Freezing left columns, scroll right

scroll

 

When freezing panes there are 3 options:

  1. Freeze top row (i.e. row 1)
  2. Freeze first column (i.e. column A)
  3. Freeze both rows and columns

The first 2 options can only be applied separately whilst the 3rd option applies the ‘freezing’ in both directions.

How to apply Freeze Panes

 

  1. To apply Freeze Panes to the top row (row 1) go to VIEW > FREEZE PANES > FREEZE TOP ROW
  2. For the first column, go to VIEW > FREEZE PANES > FREEZE FIRST COLUMN

scroll

  1. To ‘freeze’ both rows and columns select the cell B6 (in this example). This means when you go to VIEW > FREEZE PANES > FREEZE PANES, any rows above and to the left of cell B6 will be ‘frozen’ when scrolling in either direction

  1. After ‘Freeze Panes’ has been applied, you may wish to turn off this feature. This may be because you might want to reset the areas to be frozen or you just want to take it back to normal. To do this, go to VIEW > FREEZE PANES > UNFREEZE PANES
  2. If you add more columns and/or rows to your spreadsheet, you will need to ‘refreeze’ the panes. For example, adding 1 column to the left would mean selecting cell C6 and then going to VIEW > FREEZE PANES > FREEZE PANES

Conclusion

‘Freeze Panes’ is a great time saving tool that will help you organise data in large worksheets and will therefore make you more efficient in viewing your data.