Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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 Fabricss conclusions also applied to their Purchasing Department.

Barry Lee collects the following data for the most recent year for Fashion Flairs 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 Fabricss presentation about the Purchasing Departments cost drivers?

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Fundamentals Of Financial Institutions Management

Authors: Marcia Cornett, Anthony Saunders

1st Edition

0256253676, 9780256253672

More Books

Students also viewed these Finance questions