Cost estimation - Using excel

1. Cost estimation - Using excel ACCT 5302 MBA – Cohort FALL 2014 Professor Dr. Martin Taylor Presented by Ahalya Vikram

2. MIXED COST • Total Mixed Cost = Fixed Cost + Variable Cost • Fixed Cost e.g., Hospital – buildings, empty beds & equipments, Factory rent • Variable Cost e.g., Supplies , medicines , patient services , raw materials

3. Mixed cost Y = Total mixed cost – Dependent Variable a = Total fixed cost (Intercept) b = Variable cost per unit (Slope) X = the level of activity – Independent Variable Total mixed cost = Total fixed cost + total variable cost Y = a + b* X

4. Cost estimation • Predict Future Cost using past data • WAYS TO ESTIMATE COST • Account Analysis • Engineering Approach • High – Low • Regression

5. Procedure: High - low • Max of units / Activity & past cost • Min of units / Activity & past cost • b (Slope) = = = • Fixed Cost (Intercept) = Total mixed cost – units * b

6. procedure: Regression • Statistical technique for estimating the relationship between the independent variable(s) and the dependent variable. • Simple regression: Y = a + bX + ε Where, Y = dependent variable (the variable that we are trying to predict)X = independent variable (the variable that we are using to predict Y)a = intercept b = slope ε= error term.

7. Excel Setup: Analysis ToolPak Installation • Analysis Toolpak is NOT pre-installed with Excel 2010 or 2013 • We need to install and load it before we can use the statistical functions. • Mainly four steps to install Analysis ToolPak. • File/Options/Add-Ins/Manage/Analysis ToolPak

8. Analysis ToolPak Installation • Step 1 • File/Options

9. Analysis ToolPak Installation • Step 2 • File/Options/Add-Ins

10. Analysis ToolPak Installation • Step 3 • File/Options/Add-Ins/Manage • Click “Go”

11. Analysis ToolPak Installation • Step 4: • File/Options/Add-Ins/Manage/Analysis ToolPak

12. Analysis ToolPak Installation • The “Data Analysis” button is added to the Analysis group under Data tab.

16. alternative • Fixed Cost =INTERCEPT (‘Y COLUMN’, ‘X COLUMN) • Variable Cost = SLOPE (‘Y COLUMN’, ‘X COLUMN) • Error = RSQ(‘Y COLUMN’, ‘X COLUMN)  % of Variation • Total Mixed Cost = Fixed Cost + Slope X Activity

17. Scatter plot • Diagnostics • Relevance between Units & Cost • Relevant Range

18. REGRESSION DATA 1 - Diagnostics

19. Regression Data 1 - Estimate cost High-Low Method Y= 3,355+ 2.7*X Regression Method Y= 2,398 + 2.8*X **Y=Total cost X= number of meals High Low

20. REGRESSION DATA 2 - Diagnostics

21. Regression Data 2 High-Low Method Y=3,400 + 0.8*X Regression Method Y=3,100 + 0.8*X **Y=Total maintenance cost X= number of patient days Low High

22. REGRESSION DATA 3 - Diagnostics

23. Regression Data 3 High-Low Method Y=27,556 + 9.44*X Regression Method Y=31,180 + 8.5*X **Y=Total billing cost X= number of orders Low High

24. High-Low v. Regression • Accuracy with big data • Considers all outliers • Determines the best fit line • Error can be determined - RSQ • Highest and lowest number • Greatest possible variation in activity – Margin of error

25. THANK YOU!