mgt 2400 Managerial Accounting

1. Reinforce what we took in class our cost behavior class

2. Help you improve your general excel skills as well as how to use the regression and the scatterplot functions in excel. You need to be comfortable and able at excel.

3. Provide you with guidance on how to structure a useful report that a reader will appreciate

Johnson Company

The general manager of Johnson Company was attempting to prepare a budget for fiscal year 2018.  Her accounting team pulled together some historical volume data, accessed the general ledger to pull financial data associated with the period covered by the volume data, and summarized some key elements of the cost structure.

Panel A in the attached excel file contains financial data on various cost categories (more than what you need to do this problem).  The general manager would like you to do some statistical analysis and develop a cost model to predict two OH costs: utilities and maintenance.  Two variables are being considered as potential cost drivers for these costs: KWH (kilowatt hours) and units sold.  Panel B in the attached excel file provides data on these two variables.  Note: each cost equation should only use ONE predictor variable.

Required:

You will find the reading entitled “How do managers determine cost behavior?” located in the Cost Behavior module in WileyPlus to be very helpful on how to use excel to do this question.  There is also plenty of help in excel or simply google your question, e.g., how do I label my scatterplot in excel or how do I run a regression in excel

Note: you will likely have to add the Data Analysis Toolpack kit if it is not already on your toolbar. The reading in the section “Technology makes it simple” near the end of the reading (2^nd last page) shows you how.

In word document (which needs to be saved in PDF format after you are finished), write your general manager a brief (to the point) report indicating which cost estimation model should be used in the form of Y = a + b(x) along with your rationale. This recommendation should be based on deriving cost models that separately predict utilities and maintenance costs using the two potential cost drivers.  Thus, you will develop a total of 4 cost models (2 for each OH cost you are attempting to estimate). In developing your written response, consider the following items and strongly consider writing your report in this sequence or a variation thereof:

a. Separate your report into two parts: one for predicting utilities and one for predicting maintenance costs.

b. Use your common sense to suggest your belief on what should cause each of these costs to vary, common sense that you will back up with analysis.

c. Using the scatter plot function in excel, determine:

i. If the relationship for each model being estimated is linear

ii. Check whether any outliers exist in the data that should be eliminated

iii. Which cost driver appears to have the strongest relationship with the dependent variable (i.e., either utilities or maintenance) using an “eyeballing” process

iv. Be sure to briefly explain to the GM why you are doing this.

d. Using the regression function in excel,

i. Calculate a regression for each cost driver.  If you find any outliers in the data based on your scatterplot, calculate a separate regression with and without the outliers removed to see if eliminating them makes a difference to the model in terms of goodness of fit (r squared).

ii. Provide your cost model for each separate regression in the form of Y = a + b(x)

e. In a concluding section for each part of the report (i.e., each dependent variable), briefly provide your final recommendation along with your reasoning supporting your recommendation in terms of the logic supporting the relationship, its goodness of fit, and meeting the linearity assumption