Cost Revenue
Cost Quantity at Price Number Sold Total
Item per Item Beginning of March in March in March Cost
Gold Floor Lamp 20 25 35 14
Blue Armchair 55 16 105 7
Wood Side Table 8 44 25 40
Glass Coffee Table 85 35 95 4
Directions >>>
STEP #1 Need help? Use the information from Part I - it has the formulas to use along with example and directions
Calculate Total Cost starting in cell G7
Total Cost = Cost per Item multiplied by Quantity
Copy this formula to the other 3 Items - Rows 8, 9, 10
STEP #2
Calculate Total Revenue starting in cell H7
Total Revenue = Price multiplied by Number Sold
Copy this formula to the other 3 Items - Rows 8, 9, 10
STEP #3
Calculate Profit starting in cell I7
Profit = total revenue minus total cost.
Copy this formula to the other 3 Items - Rows 8, 9, 10
STEP #4
Evaluate the data - answer these THREE questions
1 - Based on your profit calculations, what changes should you make to your inventory?
ANSWER HERE >>>
2 - Is there an item that should be purchased in a larger quantity or smaller quantity? 3 - Why or why not?
ANSWER HERE >>>
1 - Based on your profit calculations, what changes should you make to your inventory?
To determine the changes that should be made to the inventory, we need to evaluate the profit calculations for each item. Comparing the total revenue and total cost for each item, we can identify the items that are generating the highest profit or the lowest losses.
For the Gold Floor Lamp:
Total cost = 20 * 14 = $280
Total revenue = 35 * 14 = $490
Profit = $490 - $280 = $210
For the Blue Armchair:
Total cost = 55 * 7 = $385
Total revenue = 105 * 7 = $735
Profit = $735 - $385 = $350
For the Wood Side Table:
Total cost = 8 * 40 = $320
Total revenue = 25 * 40 = $1000
Profit = $1000 - $320 = $680
For the Glass Coffee Table:
Total cost = 85 * 4 = $340
Total revenue = 95 * 4 = $380
Profit = $380 - $340 = $40
Based on these calculations, the changes that should be made to the inventory are as follows:
- It seems that the Blue Armchair and Wood Side Table are generating the highest profits. Therefore, it would be beneficial to increase the quantity of these items in the inventory.
- The Glass Coffee Table is only generating a small profit. It might be worth evaluating if this item is worth keeping in the inventory or if it should be replaced with an item that has a higher profit margin.
- The Gold Floor Lamp is generating a profit but not as high as the Blue Armchair and Wood Side Table. It could be worth reconsidering the quantity of this item in the inventory.
2 - Is there an item that should be purchased in a larger quantity or smaller quantity? Why or why not?
Based on the profit calculations, the Blue Armchair and Wood Side Table are generating high profits. Therefore, it would be advisable to purchase these items in a larger quantity to meet the demand and maximize profits.
On the other hand, the Glass Coffee Table is only generating a small profit. It might be worth purchasing this item in a smaller quantity or even considering replacing it with an item that has a higher profit margin.
Ultimately, the decision of purchasing in larger or smaller quantities depends on the demand and profitability of each item. It is essential to analyze the market trends, customer preferences, and overall profitability to make informed decisions about inventory quantities.