excel – Page 3 – STL

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.

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.

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.

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.

Excel has some powerful tools that you can use to carry out a What-if analysis.

We sat down with one of our Excel Experts, Jens, to ask him about three particular data analysis tools and to see whether he would demonstrate how to use them for us.

Good morning Jens, you talk a lot about “what-if analysis”. To the beginner like me, could you explain what that means?

Excel offers three What-If Analysis tools that allow users to discover different scenarios and evaluate the effect of changes on their data. These tools help in analysing the possible outcomes based on different input variables values.

What if

Thank you, that’s clear enough. Would you mind starting by telling us what those three tools are?

The Scenario Manager

The first one, the Scenario Manager, allows users to create and save different sets of input values that can be applied to a worksheet. By defining multiple scenarios, users can quickly switch between different sets of data and observe the resultant changes in calculations, formulas, and charts.

Great, and thank you for the detail

Goal Seek

Next, Goal Seek is a modest sensitivity analysis tool in Excel. It allows users to determine the input value needed to succeed a desired result. The Goal Seek tool can work with changes in one variable. By specifying a target value, Goal Seek will find the input value that will produce the desired outcome based on a formula or calculation.

I can certainly see the value in that too!

Data Tables

The final tool, the Data Table, is also a sensitivity analysis tool in Excel enable users to perform multiple calculations simultaneously by changing one or two input variables. By creating a data table, users can explore different combinations of input values and observe the resulting calculations. Data Tables are specifically useful for analysing the effect of changing variables on complex models or large data sets.

So we can start to see a difference between the three tools from your explanation there. Particularly around when each one would be the most useful. Could I ask you to run through the three tools in a bit more detail, perhaps you could demonstrate how to use them in an example context?

What if Example 1 – The Scenario Manager

Of course. For the Scenario Manager, imagine that you have projected how many projects our company is going to run next year. Also, you have estimated the profit. But there are many uncertainties you want to consider. Your sales department has provided you with both good news and bad news. One of your VIP clients may be going to one of your competitors, but good prospects of getting some huge new clients can also affect your projections.

A planned marketing campaign end of this year can also have an impact on your projections.

What if

In this example the projections are 100 projects, with estimated average revenue for each project of £25,000 which results in £2,500,000 estimated total revenue – if you look at row 4 here on my spreadsheet.

20 employees are needed for the 100 projects with an average salary of £50,000 a total of £1,000,000 – this data is in row 7.

There is a fixed cost of £500,000 in row 10.

All projects are overseas, and it is not decided yet if the employees are going first class or economy class so both options are in the model. The profit is calculated to £510,000 if the employees are going first class and £750,000 economy class, as you can see from the £2.5m total revenue – the salary cost – fixed project costs – travel/ accommodation costs.

To build What if scenarios based on this model, you need to open the Scenario Manager.

Click Add and name the scenario. You will then need to tell the Scenario Manager which cells you would like to be able to change.

In this example you would like to change number of projects, average revenue, number of employees, average salary, ticket cost, hotel cost, and food cost.

The numbers in the model are the best guess scenario and it is very important that you keep the numbers you have in the model unchanged in a scenario otherwise you cannot get back to the scenario again later. When you click OK, you get the Scenario Values dialog box in which you change the changeable cells in your model.

What if

To create a new scenario just click Add:

Once you’re here, name the scenario click OK and enter the new values.

You can create any number of scenarios. In this example a Best Guess, a Best Case, and a Worst Case scenario have been built. To show the different scenarios select the scenario and click Show, and the model will show the values entered in your model.

What if

That’s great! Thank you for breaking it down as you have for us. It makes perfect sense. And thank you for demonstrating it in a way which aligns with how I would use this tool. Shall we move on to Goal Seek?

what if Example 2 – Goal Seek

Okay. So using the same scenario, you have decided to use the economy class ticket option because your target profit next year is £2m.

The Goal seek tool can help you to understand what you will require to reach this target.

First, how many projects are needed. If I open the Goal Seek tool. The Set cell is the cell reference of the cell with the formula calculation the profit. Here it is B20. Enter the target (£2,000,000) in the “To value box”. In the “By changing cell” box, you can enter the cell reference of the cell you want goal seek to change. Here it is A4 where the number of projected projects is entered.

Now I just click OK, and the model shows in this example, that 156 projects are needed to reach the target.

What if

Instead of looking at number of projects required, you could also find out how much the average revenue needs to increase to reach the target. You will just have to change the “By changing cell” reference.

And the model shows that an average revenue of £37,500 will get you to the target.

What if

Amazing how much you are able to affect the data with such small changes, I can really see the value of this one too! Can we look at the third and final tool?

What if Example 3 – Data Tables

That would be the Data Tables. These can work with either one or two variables. We will be using 2 variables in this example.

We want to see how changes in the number of projects and changes in the average revenue will affect the profit.

To create a Data Table, you will have to enter the values you want to investigate in the cell next to the formula and across (here it is number of projects), and in the cell just under the formula and down (here it is average revenue). Select the whole range and open the Data Tables tool.

The Row input cell here is A4 and Column input cell B4.

Click OK and the Data Table tool will now show the profit based on all the values entered.

What if

Conclusion

It sounds to me Jens, that these are tools which many people will use on a daily, weekly, monthly, quarterly and/ or annual basis depending on industry and scale. I can certainly see the benefit of them when we are looking at data and trying to think holistically about our impact on the business!

Yes, the What-if analysis tools are very useful. Especially in forecasting models. Every time you have a calculation or a model in Excel and you have thoughts or questions like:

What if we raise our prices by 5%?

And what if we were to employ more people?

What if the fuel prices increase?

Whatever question which should popup when you look at your Excel models, the What-if analysis tools can help you gaining clarity of how changes can affect your models.