60.1K

Verified Solution

Link Copied!

Need answers with FORMULAs in Excel

Management is reviewing the profitability of the Company's four products and the potential effects of several proposals for varying the product mix. The following data is available for the analysis:
	TOTALS	PRODUCT 1	PRODUCT 2	PRODUCT 3	PRODUCT 4
Revenue	$ 69,575	$ 11,275	$ 18,900	$ 13,600	$ 25,800
Cost of goods sold	44,274	4,750	7,056	13,968	18,500
Gross profit	$ 25,301	$ 6,525	$ 11,844	$ (368)	$ 7,300
Operating expenses	12,012	1,990	2,976	2,826	4,220
Income before taxes	$ 13,289	$ 4,535	$ 8,868	$ (3,194)	$ 3,080
Units sold		1,100	1,350	1,700	2,150
Sales price per unit		$ 10.25	$ 14.00	$ 8.00	$ 12.00
Variable cost of goods sold per unit		$ 3.00	$ 2.75	$ 5.75	$ 7.00
Variable operating expenses per unit		$ 1.10	$ 1.30	$ 1.00	$ 1.10
Each of the following proposals is to be considered independently. Consider only the product changes stated in each proposal; the activity of other products remains stable. Ignore income taxes.
Calculate the effect on total income if Product 3 is discontinued.
Item	Amount
Sales price
Variable costs
Unit contribution margin
Loss generated by sales
Calculate the effect on total income if Product 3 is discontinued and consequently a 200 unit decrease in the sales of Product 2 occurs.
Item	Amount
Income generated by discontinuing P3
Product 2 UCM
200 unit decrease in Product 2 sales
Net decrease in total income
Calculate the effect on income if the price of Product 3 is increased by $1 with a 300 unit decrease in sales.
Item	Amount
UCM for Product 3
Income generated by sales
Recovery of negative CM
Net effect on income
The production of Product 1 can be doubled by adding a second shift, but higher wages must be paid, increasing the variable cost of goods sold $1 per unit for these additional units. Calculate the effect on income if the additional units can be sold.
Item	Amount
Part of the plant in which Product 1 is produced can be easily adapted for the production of Product 4, but changes in quantities may make changes in sales prices advisable. Calculate the effect on income if production of Product 1 is reduced by 500 units (to be sold at $2 more per unit) and production of Product 4 is increased by 500 units (to be sold at $0.50 more per unit).
Item	Amount
New CM
Product 1
Product 4
Old CM
Product 1
Product 4
Net effect on income (New CM - Old CM)

Answer & Explanation Solved by verified expert

See Answer