Regression, cost behavior, predictions, trends, budgeting

(file is attached for better format and use in computations)

Cost functions, choosing cost drivers using Excel regression analysis, and scatter plot

You have been hired by Barb's Cool Cars, Inc., to help them analyze their cost accounting function. Barb opened her business in the early 1980s, making reproductions of classic cool cars including "Bathtub Porsches", 1965 T-Birds, 1965 Mustangs, and 1920's Bentleys. In the early days, Barb's used old Volkswagen bugs and readily available modification kits which were really only aesthetic changes. However, over time, Barb's became famous for their willingness to customize the appearance of kit cars far more than other reproduction car fabricators and also to modify performance . Of course, Barb's employs mechanics and auto body specialists. However, in addition, Barb employs or has on retainer,
? an artist
? a local paint manufacturer to match unusual color requests
? an engineer who designs and installs convertible tops for cars that were manufactured as sedans
? a tanner who will prepare leather to a client's specifications.

About 10 years ago, Barb's went international based on a simple idea. A large variety of automobiles are available in other countries but not in the U. S. Of course, some U. S citizens would like to buy these cars. There are two common reasons these cars are not available in the U. S.

? First, the cars may be so expensive that the manufacturers are not willing to crash test the cars as required for U. S. distribution. These autos can be brought into the U. S. from abroad by those who already own them, but may not be sold here. To meet demand for these autos, Barb's opened an international division. Barb's uses an unusual marketing plan to bring these autos to the American market. Buyers fly to one of Barb's international locations, purchase the car, and drive it during an extended vacation. During the visit, Barb's took care of all the arrangements for the tourist to import the car to the U. S. At the end of their trip, Barb's ships the car to the U. S. for the tourist to pick up at the nearest international port.
? Second, the automobiles may not pass U. S. pollution standards. These cars must be modified before they can be imported. Barb's purchases the cars, makes the necessary modifications, and then either imports the cars for sale within the U. S. or sells them to tourists as described above.

Last year, Barb added "faux" 2009 Buggatti Veyrons to the sales mix. .Though the Veyron-at 252 mph the fastest production automobile in the world--is too new to be a classic, it is well on its way. The car amazed a series of professional test drivers when it was introduced in 2006. For example, Jeremy Clark (New York Times, Sunday November 27, 2005) declared the Veyron to be "Utterly, stunningly, jaw dropping brilliant." Of course, few can afford its approximate $1.13 million price tag. Barb saw an opportunity. A lot of people might like a car that looked like a Veyron, even if it wasn't really one. Barb's fabricated and patented a Veyron kit car.

So, at present, Barb's has two distinct departments with four product lines.
? They purchase the Volkswagens or Miatas, and then customize them to a smaller or larger degree, with kits they purchase or produce.
? They also sell cars which are unavailable in the U. S. because of safety or pollution issues through an international division.

Assignment:

Barb has asked for your help in recommending a "best practice" costing system. She thinks the current cost system, which is a job order system with overhead application based on direct materials cost, no longer is providing the information the company needs to know how to make bids or price completed orders.

At your request, Barb's has provided monthly information about costs and several potential cost drivers for the past three. Brief descriptions of the accounts are as follows. "Overhead costs" is the total overhead cost in that month. "Units sold" is the number of units sold that month. "Labor hours" and "machine hours" represent the number of each activity in that month. "Materials" are the total raw material costs incurred in a given month. "Modified" is the number of autos sold each month that were modified to meet US emissions and safety regulations. "Veyron" is the number of Veyron replicas sold that month. "Consultant charges" is the total amount paid to all consultants for sales that month. "Truly custom" is the number of cars sold that month that are considered highly customized.

This is an individual assignment. You may talk with other students about the assignment. However, the items that you hand in must be your own original work. Evidence that you shared files or diskettes with others will result in a grade of zero.

Most people need 3 to 6 hours to complete the assignment. If you did not understand or attend the classroom presentation about regression analysis using Excel, or do not know how to do graphing with Excel, allow more time. There is a section in your textbook to refer to, and a practice file is available on Blackboard.

When the work is complete, print out all sections. Follow the submission guidelines below.

Data

Month and year
overhead costs (thousands of dollars)
units sold
labor hours
machine hours
materials
(thousands of dollars)
Modified
Veyron
Consultant charges (thousands of dollars)
Apr1 305 11 975 404 386 2 0 68
May1 239 17 757 222 559 5 0 40
June1 567 14 299 100 897 4 0 53
July1 843 30 312 257 499 6 0 37
Aug1 869 18 800 201 175 2 0 43
Sept1 557 32 400 87 125 12 0 29
Oct1 290 32 197 34 848 15 0 56
Nov1 376 22 289 56 488 12 0 84
Dec1 976 48 678 121 376 5 0 22
Jan2 590 71 850 230 234 7 0 46
Feb2 125 32 76 75 57 1 0 67
Mar2 674 33 750 89 776 3 0 56
Apr3 399 51 835 23 567 2 0 34
May2 865 24 218 56 234 5 0 45
June2 587 27 400 78 90 4 0 48
July2 945 31 980 900 876 5 0 67
Aug2 643 37 665 125 453 6 0 45
Spet2 389 2 320 30 45 3 0 66
Oct2 758 45 235 45 48 8 0 42
Nov2 127 31 710 80 37 2 0 68
Dec2 1200 32 879 176 488 10 0 56
Jan3 876 3 100 75 350 2 0 47
Feb3 1190 34 187 69 450 10 0 53
Mar3 879 29 289 83 827 12 2 37
Apr3 569 12 578 440 347 2 3 43
May3 390 3 850 1225 546 1 1 22
June3 898 125 76 100 2897 3 2 58
July3 1609 24 780 257 350 5 2 83
Aug3 3207 3 800 1201 450 10 1 18
Sept3 2378 22 218 87 827 5 3 46
Oct3 1356 15 459 167 245 2 1 37
Nov3 2567 30 387 219 459 4 2 43
Dec3 1345 12 599 570 788 1 1 22
Jan4 2314 9 475 457 234 2 2 58
Feb4 2211 10 785 389 657 2 3 83
Mar4 2340 15 233 450 856 2 3 18

Required: Use Excel.

1. For each of the seven potential cost drivers (units through consultant charges) do each of the following:

a) Prepare a scatter plot. The y axes should be overhead costs, and the x axes will be one of the potential cost drivers. Drop in a trend line. Please make a separate charts for each cost driver, because putting them together makes it too hard for me to read.

b) Using regression commands, find the slope, Rsquared, and intercept based upon the entire 36 months of data. Do the same work, but using only the most recent 12 months of data. Display these results in the spreadsheet in an organized manner.

c) Find the overhead application rate that would be used for each potential cost driver, based upon the entire 36 months of date, and again for the past 12 months only. Display these results in the spreadsheet as well.

2) Prepare a word document answering the following questions

a) Based solely on the scatterplot appearance, without reference to the numbers you developed with the regression analysis, comment on the appropriateness of each potential cost driver.

b) Based on the scatterplots, the regression information, and the information in the background materials, choose the best cost driver. Explain your choice, including a discussion of why other cost drivers were not chosen.

c) Assume that the original cost driver was appropriate when the business was opened back in the mid-1980s. Have you chosen the same cost driver now? If so, why do you believe that this is still the best cost driver? If not, why do you think the best cost driver has changed over time?

d) Using the model you have chosen, and the appropriate activity level as given below, estimate total overhead costs using the regression equation.

Activity Level Information:

Units 2000
Labor Hours 3000
Machine Hours 800
Materials $257,000
Number of veyrons 5
Modified 12
Consultant charges $53,000

d) Describe in a good level of detail a cost accounting system that would fit Barb's needs well. Explain why you made the choices you did.