1 / 25

Cost estimation - Using excel

Cost estimation - Using excel. ACCT 5302 MBA – Cohort FALL 2014 Professor Dr. Martin Taylor Presented by Ahalya Vikram. MIXED COST. Total Mixed Cost = Fixed Cost + Variable Cost Fixed Cost e.g., Hospital – buildings, empty beds & equipments , Factory rent

Download Presentation

Cost estimation - Using excel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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.

  13. 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

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

  15. REGRESSION DATA 1 - Diagnostics

  16. 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

  17. REGRESSION DATA 2 - Diagnostics

  18. 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

  19. REGRESSION DATA 3 - Diagnostics

  20. 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

  21. 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

  22. THANK YOU!

More Related