Instructions: Use the Data Analysis functionality of Excel.

Travis Country owns $21$ specialty farm supply stores throughout much of the country. Their fiscal year runs July $1$ to June $30.$ As purchasing department costs continue to increase, Travis Country$$s Controller, Kim Sanchez, has decided to investigate the relationship of three different potential drivers of this cost to determine which factor has the strongest influence on purchasing department costs. She can then focus the corporate manager of purchasing operations on controlling or streamlining those activities to control the subsequent costs. Your task is to determine what data should be included, calculate the relationships with each of the three drivers $($put output on different tabs$)$ and determine the best relationship $($using our methods of evaluation$).$ On the tab with the output of your preferred model, in a cell below the output, write the mathematical cost function you have derived $($show how that driver impacts the costs$),$ substituting in the name of your chosen driver for the X and putting in the appropriate name for the Y$.$ Name the tab$/$worksheet containing your preferred model with your first and last name.

FY: $2024$

$$Store Location

$$Purchasing Dept. Cost $($$$)$

$$Merchandise Purchased $($$$)$

# of Purchase Orders

# of Suppliers

$1$

Sheridan, WY

$575,000$

$47,239,000$

$1708$

$61$

$2$

Denver

$1,226,000$

$102,365,000$

$2519$

$95$

$3$

Salt Lake City

$1,710,000$

$100,162,000$

$2506$

$138$

$4$

Kansas City

$881,000$

$95,759,000$

$1719$

$91$

$5$

Omaha

$1,545,000$

$51,465,000$

$2882$

$154$

$6$

Milwaukee

$795,000$

$50,631,000$

$647$

$75$

$7$

Minneapolis

$1,341,000$

$84,753,000$

$2978$

$103$

$8$

Phoenix

$796,000$

$103,464,000$

$3761$

$117$

$9$

Las Vegas$*$

$2,216,000$

$96,162,000$

$2584$

$73$

$10$

Albuquerque

$2,030,000$

$62,364,000$

$5497$

$176$

$11$

Tucson

$1,338,000$

$65,635,000$

$4347$

$130$

$12$

Houston

$855,000$

$88,524,000$

$2878$

$62$

$13$

Oklahoma City

$1,122,000$

$72,645,000$

$819$

$129$

$14$

Tulsa

$863,000$

$61,638,000$

$1247$

$145$

$15$

Dallas

$1,085,000$

$105,666,000$

$2162$

$141$

$16$

San Antonio

$952,000$

$59,437,000$

$2822$

$105$

$17$

Austin

$1,133,000$

$38,542,000$

$5115$

$51$

$18$

El Paso

$1,043,000$

$33,030,000$

$383$

$130$

$19$

Nashville

$1,634,000$

$36,322,000$

$5293$

$172$

$20$

Memphis

$699,000$

$34,121,000$

$967$

$34$

$21$

Indianapolis

$875,000$

$31,920,000$

$2425$

$48$

$$

$*$The Las Vegas property figures are influenced by a fire in mid$-$November $2023$ that caused the store to close for six months and required a total restocking.