EXCEL DECISION MAKING TOOLS

BASIC FORMULAE - REGRESSION - GOAL SEEK - SOLVER

EXCEL BASIC FORMULAE

• The AutoSum Feature in Excel (Σ)• The AutoSum button quickly inserts Excel functions that summarize all the

values in a column or row using a single statistic• Sum of the values in a Column or Row• Average of the values in Column or Row• Total Count of Numeric Values in a Column or Row• Max Value of the Column or Row• Min Value of the Column or Row

EXCEL BASIC FORMULAE. .

EXCEL BASIC FORMULAE

• The Relative Formula Reference Feature in Excel ($$)• Multiplication of B2=A2*B1 becomes C2=B2*C1, this is called relative formula

reference.• Can be avoided by using the F4 Key and locking the reference cell.• $ locks the reference term.• $A1 tells Excel you always want to refer to column A.• B$1 tells Excel you always want to refer to row 1.• $B$1 tells Excel you always want to refer to cell B1.• New Formula in B2=$A2*B$1 . .

EXCEL BASIC FORMULAE

• How to Calculate Exponents in Microsoft Excel • We Simply want to calculate a number to power of a number• As simple and straight forward it is, the formula for this function in

excel is straight forward too• =POWER() i.e. =POWER(number, power) . .

Ref Number Power Formula Result

2 3 =POWER(2,3) 8

C3 3 =POWER(C3,3) 8

C3 D3 =POWER(C3,D3) 8

2 D3 =POWER(2,D3) 8

REGRESSION ANALYSIS

REGRESSION

• So you Have a ice cream shop and you want to buy raw materials for your business. Ice creams, cones etc.

• How can you be sure that your inventory is optimized. Not surplus nor scarce.

• How can you predict the sales of the shop on a certain day?• Let’s record the data for 20 days, we record the temperature,

respective sale, holidays, season etc.• This gives us a 20 points of data for our prediction

REGRESSION

• A Day has 76F and sales that day were 2,600• Another Day has 88F and sales that day were 3,000• Some other day has 70F and sales were 2,600• So on. . for 20 other data samples

• How can you present this data?

SCATTER GRAPH

Scatter Graphs

Scatter graphs are similar to line graphs in that they use horizontal and vertical axes to plot data points. Scatter plots show how much one variable is affected by another. The relationship between two variables is called their correlation.

Scatter Graphs usually consist of a large body of data. The closer the data points come when plotted to making a straight line, the higher the correlation between the two variables, or the stronger the relationship.

• Temperature

Sale

s

55 70 76

26

88

30

• Regression is the attempt to explain the variation in a dependent variable using the variation in independent variables.

• Regression is thus an explanation of causation.

• If the independent variable(s) sufficiently explain the variation in the dependent variable, the model can be used for prediction.

Independent variable (x)D

epen

dent

var

iabl

e

Scatter Graph using Regressions

Independent variable (x)

Dep

ende

nt v

aria

ble

(y)

The output of a regression is a function that predicts the dependent variable based upon values of the independent variables.

B0, is the point where the line always touches the y –axis, hence y-intercept

B1= Slope i.e. =SLOPE(Sales, Temp), B0=Intercept i.e. =INTERCEPT(Sales, Temp)

Simple regression fits a straight line to the data.

y’ = b0 + b1X ± є

b0 (y intercept)

B1 = slope= ∆y/ ∆x

є

Simple Linear Regression

Simple Linear Regression

For each observation, the variation can be described as:

y = y^ + ε

Actual = Explained + Error

Zero

Prediction error: ε

Prediction: y ̂ Observation: y

Explaining the Coefficients in an Equation

Y = a + bX ; for a 2 Variable simple Regression

This equation explains how and why one “dependent” variable is determined by an “independent” or “explanatory” variable.

On the left side is Y, our dependent variable, Sales. On the right side are a, our constant (or intercept), and b, our coefficient (or slope), multiplied by X, our independent (or explanatory) variable, temperature.

Y(Sales) = -8.43 + 0.43*TEMP.

But what if we think that the world is much more complicated and that a variety of factors might explain the impact of temperature on sales?

Simple Linear Regression

• We can expand the previous equation to form• Y = a + b1X1 + b2X2 + . . . , known as a multiple-variable Regression• The X variables explain Y sales, we have several X variables—such

as season, time of day, local conditions, holidays etc. • For Example, Y = -8.43 + 0.43*TEMP + 0.001*Season ...

Simple Linear Regression

• Summary:• So, regression is a procedure to predict the outcome using a

combination of Data, Scatter Graph and the resulting indicator

Simple Linear Regression

• Regression: its an Add-in• Installation: Home Excel options> Add-in• Select Analysis Toolpak• To Use, Data> Analysis. In pop-up, Choose Regression

Statistic Terminologies and Indicators

T-Statistic

Denotes if the coefficients are statistically dependent.

If your regression is based on what statisticians call a "large" sample (30 or more observations), a t-statistic greater than 2 (or less than -2) indicates the coefficient is significant with >95% confidence. A t-statistic greater than 1.68 (or less than -1.68) indicates the coefficient is significant with >90% confidence. The confidence thresholds for t-statistics are higher for small sample sizes.

Statistic Terminologies and Indicators

P-values:

These are the probabilities if the coefficients are statistically significant.

Should be around 0.05, higher the value, lower the probability of our null statement being true.

The P-value of 0.05 for an coefficient implies 1 - 0.05 = 95% confidence that statement we mentioned(also called the null hypothesis) is "true”. The very low P-values for the Intercept and coefficients indicate they are very strongly significant, so their 95% confidence intervals are relatively narrower.

Statistic Terminologies and Indicators

R-Square Indicator• The R-Square statistic near the top of the output represents the

percent of the total variation in the dependent variable that is explained by the independent variables, i.e., the model's overall “goodness of fit.”

• =POWER(Multiple R, 2)• Should be more than +- 0.5• Used when Having more than 2 Variables..

GOAL SEEK

Loan Amount $100,000

Interest Rate 6%

Repayment 120 Months

Monthly Payment 1,110

Goal Seek

• But our budget is just $600 Monthly Payment• How much amount can we borrow?• Lets Use The Goal Seek to determine.• Goal Seek adjusts the value of the input such that the desired

output is achieved

Goal Seek

• Single Variable • Set up properly, Select the whole table• Data > What-If Analysis > Goal Seek• Set Cell: Formula Cell• To Value: Target/ Goal• By Changing: Variable Required to be changed( First Field Usually)

Goal Seek

• Loan Amount $100,000• Interest Rate 6%• Repayment 120 Months• Monthly Payment $ 1,110

Monthly Payment Cell

Desired Value

Loan Amount Cell

• Loan Amount $59,963• Interest Rate 6%• Repayment 120 Months• EMI $ 600

Data Table

• Multiple Variable • Set up properly, Select the whole table• Data > What-If Analysis > Data Table• Row input Cell: original value of the Values kept in table rows • Column Input Cell: original value of the Values kept in table column

Data table

Formula Cell(EMI Here) Months Months Months

Loan Amount Monthly Payment Monthly Payment Monthly Payment

Loan Amount Monthly Payment Monthly Payment Monthly Payment

Months From Original Data

Loan Amount From Original Data

Result= Monthly Payment; Combination of Loan Amount and Months

SOLVER

Solver

Do you ever think about questions like this?• What is the maximum profits we can make?• What is the best way to schedule employees in shifts?• What the best combination of tasks we can finish in a given time?

Here Comes the MS Excel Solver

Its a basic tool which just solves problems for usIts like Goal Seek but Better and Awesomer !

Solver

• Solver: its an Add-in• Installation: Home Excel options> Add-in• Select Analysis Toolpak> Select Data Analysis & Solver, both.• To Use, Data> Solver

Solver

• It has 3 Cells• Set Target Cell: Should be formula cell you want to change• By Changing Cell: Multiple Cells can be selected• Subject to constraints: Can select multiple constraints

Solver

Cell to Change

To what value? Min? Max? or Set Value?

By Changing the Values of Cells(Can Select multiple cells)

You can add or change rules for every cell

( <= or >= or = or int etc. )