Please help complete this problem. Please show each step on how it should be entered into excel.

Athol Furniture, Inc. $($AFI$),$ is a growing regional chain of discount furniture and large$-$appliance stores. Management has targeted the small city of Bluff Lake as the next location for a retail outlet. Although the total population is currently $21,000,$ Bluff Lake is expected to grow during the next decade because of increased mining in the surrounding hills.

AFI$$s marketing department did a general analysis of the potential of market expansion into Bluff Lake, but the task of locating the best site for a store has been given to Mr$.$ Carlos Gutierrez. After obtaining the market data on Bluff Lake, Mr$.$ Gutierrez decides it would be very appropriate to utilize the Huff location model in developing a recommendation for the company$$s management. This is because there are existing competitors and several potential sites under consideration.

Figure $8\mathrm{.}10$ is a map of Bluff Lake showing major streets and highways, the railway $($AFI will ship its merchandise into the city by rail from a regional warehouse $800$ miles away$),$ Crystal River, Bluff Lake, and the census block groups $($numbered $1$ through $12).$ Table $8\mathrm{.}14$ gives the number of households, average annual income per household, and average annual furniture$/$large$-$appliance expenditure per household for each census block group.

TABLE $8\mathrm{.}14$ Market Data

In Figure $8\mathrm{.}10,$ the letters A and B show the locations of AFI$$s existing competitors, and Table $8\mathrm{.}15$ indicates the sizes of these existing stores to the nearest $5,000$ square feet of sales area. The letters X$,$ Y$,$ and Z in Figure $8\mathrm{.}10$ show the possible sites that Mr$.$ Gutierrez feels AFI could use for a retail store. The maximum size limit $($i$.$e$.,$ sales area$)$ of each potential location is given in Table $8\mathrm{.}16.$

page $237$

FIGURE $8\mathrm{.}10$ Bluff Lake

Description

TABLE $8\mathrm{.}15$ Competitors$$ Store Sizes

Store Sales Area, Sq$.$ Ft$.$

A $10,000$

B $15,000$

TABLE $8\mathrm{.}16$ Maximum Size Limit of AFI Sites

Site Maximum Sales Area, Sq$.$ Ft$.$

X $15,000$

Y $20,000$

Z $10,000$

page $238$

On the basis of average speeds for the main streets and highways obtained from the city$$s planning department, Mr$.$ Gutierrez has developed a matrix of travel times between the existing and potential retail sites and the center of each census block group. These travel times can be found in Table $8\mathrm{.}17.$

TABLE $8\mathrm{.}17$ Minimum Travel Time between Potential and Existing Sites and Block Groups in Minutes

From experience with other AFI locations, Mr$.$ Gutierrez has developed a fairly accurate portrayal of the relationship between store size $($i$.$e$.,$ sales area$)$ and margin on sales, expenses, and net operating profit before taxes. This information is shown in Table $8\mathrm{.}18.$

TABLE $8\mathrm{.}18$ Relationship of Size of Store to Margin on Sales, Expenses, and Net Operating Profit as a Percent of Sales

Questions

Utilizing a spreadsheet version of the Huff location model $($with $=1\mathrm{.}0),$ recommend a store size and location for AFI. Assuming that AFI does not wish to consider a store that is smaller than $10,000$ square feet, assess the store sizes $($based on $5,000-$square$-$foot increments$)$ up to the maximum allowable sales area for each potential site.

What is the expected annual net operating profit before taxes and expected market share for the outlet you have recommended? Defend your recommendation.

Try two other values of $($e$.$g$.,0\mathrm{.}5$ and $5\mathrm{.}0)$ to measure the sensitivity of customer travel propensity on your recommended location.

Briefly state any shortcomings you may perceive in this model.