1 1 1 1 MicrosoftInternetExplorer4 0 2 Document not specified 7.8 磅 Normal 0 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

top of page

Search

## Recent Posts

See All1. Estimate the costs for the four tires using an activity-based approach. (Hint: You will need to consider how the new information should be used to revise the way overhead is attached to each produc

860

Background: Clinton and Jennifer Andrews live in Sydney with their two school-age children. They bought their home 15 years ago. With the rise in its value over time they have generated substantial eq

180

Budget profit statement for sales and overheads and information for the preparation of a cash budget

Task 1: INDIVIDUAL ACTIVITY Included in the file are: a summary budget profit statement for sales and overheads and information for the preparation of a cash budget plus other financial data. Summary

180

bottom of page

## Comments