Lab $2-3$ Inventory Management by Customer Profitability $($Excel$)$

Skip to question

$[$The following information applies to the questions displayed below.$]$

Required:

Calculate gross margin $($revenues less cost of goods$/$products sold$)$ by customer.

Ask the Question: Which customers are the most profitable?

Master the Data: Fruitopia sells exotic fruit from throughout the world to customers throughout the country by mail order. The company keeps a sales journal that records each sale and its related sales and cost details. This sales journal is for March $14,2021.$

Software needed

Excel

Screen capture tool $($Windows: Snipping Tool; Mac: Cmd$+$Shift$+4)$

Data: Excel File Lab $2-3$ Data.xlsx$.$

Perform the Analysis: Refer to Lab $2-3$ in the text for instructions and steps for each of the lab parts.

Share the Story: We can share the story by reporting our findings of our most profitable customers to the sales department and to management. Potentially, we could send additional advertising emails to them, enhance the customer service toward them, or give them discounts on future purchases.

Part $2$: Assessment

Required:

$1.$ Which customer number is the most profitable with $$357\mathrm{.}70$ in gross margin?

multiple choice $1$

$2047$

$2046$

$2088$

$2072$

$2.$ If we wanted to know total sales by customer, what would we insert into the rows and $\backslash$Sigma Values fields respectively?

multiple choice $2$

Invoice #$,$ Sum of Sales

Customer #$,$ Sum of Gross Margin

Customer #$,$ Sum of Sales

Invoice #$,$ Sum of Gross Margin

$3.$ If we wanted to know total gross margin by invoice, what would we insert into the rows and $\backslash$Sigma Values fields respectively?

multiple choice $3$

Invoice #$,$ Sum of Sales

Customer #$,$ Sum of Sales

Customer #$,$ Sum of Gross Margin

Invoice #$,$ Sum of Gross Margin

$4.$ If we wanted to know which invoice had the largest total sales amount, what would we insert into the rows and $\backslash$Sigma Values fields respectively?

multiple choice $4$

Invoice #$,$ Sum of Sales

Customer #$,$ Sum of Gross Margin

Invoice #$,$ Sum of Gross Margin

Customer #$,$ Sum of Sales

$5.$ If we wanted to know total quantity$/$pounds for each invoice, what would we insert into the rows and $\backslash$Sigma Values fields respectively?

multiple choice $5$

Quantity $($Pounds$),$ Sum of Customer #

Quantity $($Pounds$),$ Sum of Invoice #

Invoice #$,$ Sum of Quantity $($Pounds$)$

Customer #$,$ Sum of Quantity $($Pounds$)$