EXCEL DECISION MAKING TOOLS BASIC FORMULAE - REGRESSION - GOAL SEEK - SOLVER.
Embed Size (px)
Transcript of EXCEL DECISION MAKING TOOLS BASIC FORMULAE - REGRESSION - GOAL SEEK - SOLVER.
Excel Decision Making Tools
Excel Decision Making ToolsBasic Formulae - Regression - Goal Seek - Solver Excel Basic FormulaeThe AutoSum Feature in Excel ()The AutoSum button quickly inserts Excel functions that summarize all the values in a column or row using a single statisticSum of the values in a Column or RowAverage of the values in Column or RowTotal Count of Numeric Values in a Column or RowMax Value of the Column or RowMin Value of the Column or Row
Excel Basic Formulae. .
Excel Basic FormulaeThe 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 numberAs simple and straight forward it is, the formula for this function in excel is straight forward too=POWER() i.e. =POWER(number, power) . .Ref NumberPowerFormulaResult23=POWER(2,3)8C33=POWER(C3,3)8C3D3=POWER(C3,D3)82D3=POWER(2,D3)8 Regression Analysis regressionSo 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?Lets 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 prediction7regressionA Day has 76F and sales that day were 2,600Another Day has 88F and sales that day were 3,000Some other day has 70F and sales were 2,600So on. . for 20 other data samples
How can you present this data?
SCATTER GRAPH8 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.
TemperatureSales5570 76268830 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)Dependent variableScatter Graph using Regressions Independent variable (x)Dependent variable (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-interceptB1= 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 + ErrorZeroPrediction error: Prediction: y ^ Observation: y Explaining the Coefficients in an Equation Y = a + bX ; for a 2 Variable simple RegressionThis 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 formY = a + b1X1 + b2X2 + . . . , known as a multiple-variable RegressionThe X variables explain Y sales, we have several X variablessuch 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-inInstallation: Home Excel options> Add-inSelect Analysis ToolpakTo Use, Data> Analysis. In pop-up, Choose Regression
Statistic Terminologies and Indicators T-StatisticDenotes 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 IndicatorThe 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.5Used when Having more than 2 Variables..GOAL SEEK Loan Amount$100,000Interest Rate6%Repayment120 MonthsMonthly Payment1,110Goal Seek But our budget is just $600 Monthly PaymentHow 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 achievedGoal Seek Single Variable Set up properly, Select the whole tableData > What-If Analysis > Goal SeekSet Cell: Formula CellTo Value: Target/ GoalBy Changing: Variable Required to be changed( First Field Usually)
Goal Seek Loan Amount $100,000Interest Rate 6%Repayment 120 MonthsMonthly Payment $ 1,110
Monthly Payment CellDesired ValueLoan Amount CellLoan Amount $59,963Interest Rate 6%Repayment 120 MonthsEMI $ 600
Data Table Multiple Variable Set up properly, Select the whole tableData > What-If Analysis > Data TableRow 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)MonthsMonthsMonthsLoan AmountMonthly PaymentMonthly PaymentMonthly PaymentLoan AmountMonthly PaymentMonthly PaymentMonthly Payment
Months From Original DataLoan Amount From Original DataResult= Monthly Payment; Combination of Loan Amount and MonthsSolver 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 SolverIts a basic tool which just solves problems for usIts like Goal Seek but Better and Awesomer ! Solver Solver: its an Add-inInstallation: Home Excel options> Add-inSelect Analysis Toolpak> Select Data Analysis & Solver, both.To Use, Data> Solver
Solver It has 3 CellsSet Target Cell: Should be formula cell you want to changeBy Changing Cell: Multiple Cells can be selectedSubject to constraints: Can select multiple constraints
Cell to ChangeTo 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 int etc. )32