70.2K

Verified Solution

Link Copied!

Preparing Excel Visualizations of Gross Profit Data Over Time

Financial information for the following five retailers is included in the Excel file associated with this chapter: The Home Depot, Inc. $($Home

Depot$),$ Lowe's Companies, Inc. $($Lowe$\text{'}$s$),$ Target Corporation $($Target$),$ The ODP Corporation $($ODP$),$ and Costco Wholesale Corporation

$($Costco$).$ In this problem we analyze the gross profit percentage $(($Sales revenue $-$ Cost of goods sold$)/$Sales revenue$)$ of retail companies

with different business models. The gross profit percentage measures a company's ability to cover its operating costs from revenues

after allowing for costs of goods and services sold. A gross profit percentage will vary by industry $($some industries require extensive

manufacturing operations for example$)$ and is also affected by a company's business strategy. For example, a company with a lower

gross profit percentage $($a grocery store$),$ will make up for profits with higher sales volume. A company with a high gross profit

percentage $($high$-$end jewelry store$)$ can afford to sell less products when each item has a higher gross profit percentage.

Required

Download the following Excel file: Companies

Calculate the gross profit percentage for each of the three years $($with Year $3$ being the most recent year$).$

Note: Enter the percent rounded to one digit after the decimal; enter $8\mathrm{.}4$ for $8\mathrm{.}44\%,$ or $8\mathrm{.}5$ for $8\mathrm{.}45\%.$

Create a line chart showing the trend of the gross profit percentage for each company over the three year period.

List the companies in order from the highest to the lowest gross profit percentage for each of the three years.

Add a trendline to the line chart for each company and forecast one additional period. Hint: Right click on each line in your chart and

add trendline. In the format trendline area under Forecast, forward $1$ period.

Answer & Explanation Solved by verified expert

See Answer