Regression Project- Analysis
Regression Project- Analysis
Regression Project
Danielle’s Bridal Fashions: Purchasing Department Costs
Danielle’s Bridal Fashions (DBF) operates a chain of thirteen retail stores which specialize in bridal wear, for both men and women. Because each store is in a different geographic region with presumably different customer desires, top management allows each store to be responsible for making their own purchasing decisions. Recently, competition has drastically increased with the addition of low cost competitors. Danielle’s is interested in examining costs to determine if there are areas where costs can be reduced. Anne Sergeant, CFO has charged Susie Student, an assistant to the CFO, with examining the costs of the Purchasing Department, and to make cost savings recommendations.
To better understand the Purchasing Department costs and the drivers of those costs, Student read retail cost journals and found an article titled “Cost Drivers in the Retail Industry.” In this article, the number of purchase orders and number of suppliers were described as important cost drivers of Purchasing Department costs in an activity-based costing setting. She visited two of the stores and both the accountants and the purchasing department managers thought these cost drivers were potentially important in their store. In the past, DBF has allocated Purchasing Department cost to products on the basis of the dollar value of merchandise purchased. This is an industry common allocation technique which has been approved by the accounting department for use on financial statements.
In a meeting with her team, Student agreed to examine the three cost drivers proposed using regression analysis to identify the single best cost driver. Furthermore, she is to report the models derived from the analysis of each single cost driver.
Susie Student collected data for the most recent year for DBF thirteen clothing stores. This data is found in the spread sheet given on Blackboard (Project 1 Details). Most stores experiences a slight downturn in sales and adjusted their merchandise purchased. Houston Texas however, had a severe decline in sales due to a hurricane which shut the store down for three months due to damages.
*To prevent data entry errors, download data from Blackboard. File name: Project 1 details.
(This case is adapted from Horngren et al. “Cost Accounting: A Managerial Emphasis” 13th edition, problem 10-40, page 382.)
Managerial Accounting Projects: Anne M.A. Sergeant Practice in Critical Thinking and Written Communication Grand Valley State University
Regression Project Data
Danielle’s Bridal Fashions:
Purchasing Department Cost Data
Purchasing Department City Costs
Merchandise Purchased (in dollars) 119,566,000 68,315,000 33,505,000 139,312,000 128,811,000 33,456,000 38,674,000 29,854,000 121,160,000 2,523,000 86,225,000 102,875,000 130,944,000
Number of Purchase Orders 5944 4357 2793 1707 2125 2550 3617 1327 1433 877 1954 7586 4731
Number of Suppliers 190 132 23 208 42 222 117 33 11 168 168 104 201
Miami Baltimore New York Toronto Detroit Chicago
St. Louis Phoenix
Los Angeles Houston
San Francisco Seattle Vancouver
2,059,000 1,533,000 1,066,000 1,622,000
875,000 1,110,000 1,755,000
539,000
557,000 1,729,000 1,519,000 1,548,000 1,267,000
Managerial Accounting Projects: Anne M.A. Sergeant Practice in Critical Thinking and Written Communication Grand Valley State University
Regression Project- Analysis
In a work setting, the previous page would be all the guidance provided. You would have to determine what kind of analysis and write up to prepare. I have prepared the following questions to help you think about the problem. You may discuss these questions with your peer and with me. However, I expect that the analysis will be done independently. In your write-up, change the name “Susie Student” to your own. You may continue to use my name as the CFO and boss.
What are the expectations of the team (ie. What is required)?
What is the dependent variable (what you are trying to explain)?
What are the independent variables (the cost drivers)?
After having identified dependent and independent variable, what is the next step in cost behavior analysis (see page 27 of notes)?
What tools are available to accomplish the model building? Which is best? (Hint: it is NOT the high-low method. Sometimes this project is called the regression project.)
Did you have ‘good’ data? Did you have to adjust it? Why?
Suggestions by your professor:
1) To adequately analyze this data, you will need to graph each cost driver (x-axis) against the purchasing department costs (y-axis). Present these on separate graphs. Prepare them using xy- scatter charting. Note, your charts should look good with axis labels, headings, etc. Do not make your graph too small. If it is too small it can not be adequately analyzed. I suggest one graph per page.
2) You will need to use regression analysis for each of the cost drivers.
Report your findings using the cost functions for each cost driver.
Remember, a cost function is “Costs= fixed cost + variable cost/activity * activity” Names are important. You need to include the r-square for each cost driver as well.
3) You need to make a decision as to which cost driver is best AND report it.
This decision should be supported by the information you provide in your write-up, both the numerical data and graphs. Some intuition can also be expressed here. Remember, you are arguing why your suggestion for a cost driver is best.
4) Additional considerations: In all projects both in school and in work, think about doing more than is required. Use your brains to offer additional insights. This is what makes a SUPERIOR employee.
How can the model be improved?
What cost cutting recommendations can you make from this analysis? What additional recommendations can you make from this analysis?
Managerial Accounting Projects: Anne M.A. Sergeant Practice in Critical Thinking and Written Communication Grand Valley State University
Regression Project- Written Document
Write-up notes:
Start with an executive summary. In this case it does not have to be long (1 page only).
You want to provide the reader with the conclusion VERY early (if not in the first sentence). Typically, readers of business reports want the conclusions first. They do not want to have to LOOK for the conclusion. Once you state your conclusions, you can report what you did, and why you can to the conclusion you did. Keep in mind that your boss might read your entire report, but your boss’s boss might only read the executive summary. Your conclusions should JUMP out at the reader. You can use bullets, bold, tables, and any other formatting tool to enhance the readability of the report.
The charts and analysis will follow the executive summary and can be referred to in the text of the summary. For example, ‘the graphs of x vs. y shows something, as seen on page 3.’
After the conclusion, and reporting what you did and found, report WHY you came to the conclusion you did. You can assume the reader knows about regression analysis. (So, You do not have to include information from the text about how regression works.)
Finally, make sure your report looks professional. That includes STAPLED, page numbers, good spelling, typed, appropriately formatted. DO NOT have to put your report in a report cover, simply staple it on the top left corner.
Suggestion for organization:
- A) Conclusion with statement of problem
- B) Regression results and findings (refer to tables and graphs if used)
- C) Justification- why did you make these recommendations
- D) Additional considerations (C and D can be combined).This is where you use your brain and SHINE!
I expect students to go the extra step by including any additional considerations. This is how professionals get promotions by doing and thinking about more than is required.
Final comment: Some students use a memo function in Word which provides a nice looking executive summary using very little time. It is ok to use this template.
