pls help with excel. Prepare From the case questions, we$$ll only consider $1$a$-1$c and $2$a and $2$b Some direction$/$suggestion:

$1$a$.$ Put Purchasing Dept Cost on the Y axis for clarity.

$1$b$.$ Consider the visualizations. Do you notice any outliers and can you explain them with case data? Should you do anything about them $($i$.$e$.$ retain, adjust, or exclude$)?$ Do any potential drivers appear more promising?

$1$c$.$ Run the possible regression combinations $($i$.$e$.$ all potential drivers at once and then each of the possible pairs$).$ Focus on the coefficients and consider what they are telling you. Which is best?

$2$a$.$ What would you tell Joe about cost behaviors at the stores?

$2$b$.$ What recommendations would make?

To Turn In Two pages max. Write a brief memo to Joe, focusing on what you$$ve learned and your recommendations. You will also turn in your Excel file, but be sure your memo includes all necessary data, exhibits, and analysis for Joe and team to fully understand your approach and the implications $($i$.$e$.$ it should stand alone$).$Table $1$: CWWR Purchasing Department Costs and Cost Drivers Store Location

Purchasing

Dept. Cost $($US$$)$

Merchandise

Purchased $($US$$)$

Number of

Purchase Orders

Number of

Suppliers

Sheridan $$575,000$ $$47,239,0001,70861$

Denver $1,226,000102,364,0002,51995$

Salt Lake City $1,710,000100,162,0002,506139$

Kansas City $881,00095,760,0001,71991$

Omaha $1,544,00051,466,0002,883155$

Milwaukee $794,00050,631,00064775$

Minneapolis $1,341,00084,753,0002,978103$

Phoenix $794,000103,464,0003,761117$

Las Vegas $2,216,00096,162,0002,58473$

Albuquerque $2,030,00062,364,0005,497176$

Tucson $1,338,00065,635,0004,347130$

Houston $856,00088,524,0002,87862$

Oklahoma City $1,122,00072,645,000819129$

Tulsa $863,00061,638,0001,247145$

Dallas $1,085,000105,666,0002,162141$

San Antonio $952,00059,437,0002,822105$

Austin $1,134,00038,542,0005,11551$

El Paso $1,042,00033,020,000382131$

Nashville $1,634,00036,322,0005,293172$

Memphis $699,00034,121,00096734$

Indianapolis $875,00031,920,0002,42548$Dan$$s Western Wear began its humble roots on Main Street

in Sheridan, Wyoming, where Thomas George, a Greek

immigrant, settled his family in $1919$ and began a cobbler

business that served ranchers, cowboys, and coal miners. Dan

George, Thomas$$ son, was nine years old when they arrived

in the United States and was soon active in his father$$s

cobbler business. Ultimately, he took over the business and

began to increase the store$$s inventory, creating the business

that is known today as Dan$$s Western Wear, which sells

quality boots, work clothes, western apparel, and jewelry to

just about anybody. Dan believes that if clothes can hold up

to a dusty hard day$$s work, and look great at the same time,

all the better. The company prides itself on quality clothes

with good old$-$fashioned service. Customers are greeted at

the door and helped throughout their experience. A visit to

Dan$$s Western Wear is like a visit to an old friend.

Dan$$s Western Wear is typical of western wear retail

outlets. The industry is characterized by many independent

regional shops. Frequently, these retail shops have been

in operation for many years and have a multigenerational,

loyal customer base. Recently, the online western wear

business has shown some growth, but customers are

primarily interested in face$-$to$-$face shopping where they

can feel the products, try them on$,$ and visit with locals who

share their interests. Because these specialty retail outlets

have relatively little competition from online and discount

retailers, western wear stores have enjoyed relatively high

profit margins for retail businesses. If managed well, these

can be profitable stores.

Joe West, a young entrepreneur who had worked at

Dan$$s Western Wear prior to going to graduate school for a

master$$s in business administration, saw an opportunity to

consolidate western wear retail outlets to gain efficiencies.

Thus, he founded Consolidated Western Wear Retailers

$($CWWR$)$ and began purchasing profitable western wear

stores, starting with Dan$$s Western Wear. He also assembled

a management team to help him grow his business. As a

new store is purchased, Joe and the management team study

the store to learn how it is successful. Initially, they do not

make major changes, keeping the same store name and

encouraging the store to operate as before. Over time, Joe

and the management team implement changes to improve

profitability. Currently, CWWR owns $21$ retail stores, and

Joe believes this is large enough to aggressively pursue

efficiencies from consolidation.

IMA EDUCATIONAL CASE JOURNAL VOL. $10,$ NO$.4,$ ART. $3,$ DECEMBER $20171$

ISSN $1940-204$X

Consolidated Western Wear Retailers:

Regression Analysis to Understand Cost Drivers

in a Purchasing Department

Anne M$.$ A Sergeant, PhD$,$ CMA

School of Accounting $$ Seidman College of Business

Grand Valley State University

$2017$ I M A

THE PROBLEM

Each year Joe reviews the financial in