Please show all your work within Excel using functions. Thank you. Monroe Produc

Question

Please show all your work within Excel using functions. Thank you.

Monroe Products, Inc. is considering the development a new “XL” model that is expected to reduce sales of the existing “H” model. Forecasted sales quantities, prices and COGS of the H model without the introduction of the new XL model are shown below for years 2016-18   The sales quantities, unit prices and COGS if both the H and XL products are offered are also shown below. Determine the change in Gross Margin only for each year that could be used in preparing the proposal to develop and offer the XL model.. Sales Quantities 2016 2017 2018 H Only Sales quantity 350 375 400 Price $2,500 $2,500 $2,500 COGS $1,800 $1,800 $1,800 If both H and XL are offered H Sales quantity 265 250 200 Price $2,500 $2,250 $2,000 COGS $1,600 $1,600 $1,600 XL Sales quantity 100 150 250 Price $3,500 $3,350 $3,200 COGS $3,000 $2,500 $2,250 Solution

Explanation / Answer

Gross Margin for each year is as shown in the table. Firstly gross profit for H only is calculated. In the second the gross profit for H and XL is calculated.

Gross Margin deatlis for H only and XL and H for the three years are as given below

Gross Margin = Gross Profit/Total Sales

Sales Quantities 2016 2017 2018 H Only Sales quantity 350.00 375 400 Price $2,500 $2,500 $2,500 COGS $1,800 $1,800 $1,800 Gross Profit $                      2,45,000 $             2,62,500 $               2,80,000 If both H and XL are offered H Sales quantity 265 250 200 Price $2,500 $2,250 $2,000 COGS $1,600 $1,600 $1,600 XL Sales quantity 100 150 250 Price $3,500 $3,350 $3,200 COGS $3,000 $2,500 $2,250 Gross Profit $                      3,15,000 $             2,90,000 $               3,17,500

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.