EXCEL DECISION MAKING TOOLS BASIC FORMULAE - REGRESSION - GOAL SEEK - SOLVER.
-
Upload
katrina-bond -
Category
Documents
-
view
227 -
download
0
Transcript of EXCEL DECISION MAKING TOOLS BASIC FORMULAE - REGRESSION - GOAL SEEK - SOLVER.
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. )