Question

Excel Task 1 & 2 (due week 6): Please submit both tasks together on one pdf document. Do not forget to highlight your answers.

Q. Purchasing Department cost drivers, simple regression analysis

Fashion Flair operates a chain of 10 retail department stores. Each department store makes its own purchasing decisions. Barry Lee, assistant to the president of Fashion Flair, is interested in better understanding the drivers of Purchasing Department costs. For many years, Fashion Flair has allocated Purchasing Department costs to products on the basis of the dollar value of inventory purchased. A $100 item is allocated 10 times as many overhead costs associated with the Purchasing Department as a $10 item.

Barry recently attended a seminar titled ‘Cost drivers in the retail industry’. In a presentation at the seminar, Couture Fabrics, a leading competitor, reported number of purchase orders and number of suppliers to be the two most important cost drivers of Purchasing Department costs. The dollar value of inventory purchased in each purchase order was not found to be a significant cost driver. Barry interviewed several members of the Purchasing Department at the Fashion Flair store on the Gold Coast. They believed that Couture Fabrics’s conclusions also applied to their Purchasing Department.

Barry Lee collects the following data for the most recent year for Fashion Flair’s 10 retail department stores:

Department store	Purchasing department costs (PDCs)	Dollar value of inventory purchased (IP$)	Number of purchase orders (no. of POs)	Number of suppliers (no. of Ss)
Sydney	$1523000	$68315000	4357	132
Bondi	1100000	33456000	2550	222
Canberra	547000	121160000	1433	11
Gold Coast	2049000	119566000	5944	190
Perth	1056000	33505000	2793	23
Hobart	529000	29854000	1327	33
Brisbane	1538000	102875000	7586	104
Melbourne	1754000	38674000	3617	119
Adelaide	1612000	139312000	1707	208
Double Bay	1257000	130944000	4731	201

Barry decides to use simple regression analysis to examine whether one or more of three variables (the last three columns in the table) are cost drivers of Purchasing Department costs.

Refer to the question above and

Excel task 1: Run the below regression models to estimate a and b

1. Regression 1: PDCs = a + (b × IP$)

2. Regression 2: PDCs = a + (b × No. of POs)

3. Regression 3: PDCs = a + (b × No. of Ss)

Excel task 2:

1. Compare and evaluate the three simple regression models estimated by Barry Lee.

2. Do the regression results support Couture Fabrics’s presentation about the Purchasing Department’s cost drivers?

Answer 1