Excel Analytics 05-02 (Static) Cost-Volume-Profit Relationships [LO5-1, LO5-2, LO5-3, LO5-4, LO5-5, LO5-7, LO5-9]

Lyndia Company is a merchandiser that sells a total of 15 products to its customers. The company provided the following information from last year:

Product	Unit Sales			Selling Price per Unit			Variable Cost per Unit
1		9,000		$	29		$	12.95
2		16,500		$	99		$	68.55
3		6,000		$	85		$	42.50
4		19,500		$	109		$	85.00
5		4,500		$	19		$	6.35
6		27,000		$	119		$	92.00
7		3,000		$	39		$	14.30
8		7,500		$	79		$	33.18
9		9,000		$	69		$	30.36
10		15,000		$	95		$	77.60
11		10,500		$	59		$	25.40
12		1,500		$	65		$	29.00
13		3,000		$	44		$	12.40
14		6,000		$	49		$	13.48
15		12,000		$	89		$	61.83
		150,000

Last year, Lyndias total fixed expenses and net operating income were $3,000,000 and $1,223,070, respectively. The company would like your assistance in developing some financial projections for this year.

rev: 05_07_2020_QC_CS-210952, 01_09_2021_QC_CS-246235

Excel Analytics 05-02 (Static) Part 4

5. Refer to the original data (in other words, return cells Q14 and Q16 to their original values of 150,000 units and 0%) and assume the sales mix holds constant.

a. Using Charts, prepare a profit graph. (Hint: Start by preparing formulas within cells C35 and C36 that compute the profit at sales volumes of zero units and 150,000 units.)

b. Prepare a CVP graph. (Hint: Start by inserting amounts in cells C41, D41, and E41 that reflect total sales dollars, total fixed expenses, and total expenses at a sales volume of zero units. Then input cell references or prepare formulas in cells C42, D42, and E42 that provide those same three amounts at a sales volume of 150,000 units.)

Queries & Connections Te Recent Sources Existing Connections El LB From Text/CSV From Web Get Data From Table/Range LO 21 A2 DHE 8 Pan Group Ungrou Stocks Currencies Sort Filter Refresh All Printing Queries & Connections Advanced Text to Columns Es Data Tools What If Forecast Analysis Sheet Subtota Gel Transform Data Data Types Sorter Forecast Outono 65 f E M N A 1 2 12 15 Total S Welchted Average CM per Unit 1 Last Year 2 Unit Sales 3 Sales mis pertentas 4 Selling price per unit 5 Vanable expense per unit 9.000 6% 29.00 12.95$ 16,500 11N 99.00 5 68 555 5 E F G 1 3 4 5 6 7 5 9 10 11 6000 15 500 4,500 27 000 3.000 7500 9,000 15,000 10 500 4 1 18 25 SN 10% 7 85 DOS 109 00 5 1900 5 119 05 39.00 $ 79.00 $ 69.00 S 95005 59.00 42505 2500 5 G355 92.00 5 14 30$ 3815 30365 77.50 S 25 40 5 510,000 $2,125 500S 35 500 $3,213.000 $117.000 S592.500S 621000 51.425.000 S 619.500 5 7355 1.657500 28.575 2,464,000 42.900 248 850 273 240 1.154000 266, 700 255.000 5 45 000 5 $ 56.995 56.935 S729,000 5 i 74.100 S 345,650 347.750 5751,000 251.000 S 352,800 13 3.000 2x 44.00 $ 1240 $ 1.500 1% 5500 5 29005 14 6,000 4% 4900 5 1348 150,000 100% 12.000 8 8900 6183 7 Sales 6 Variable expenses 9. Contribution Fixed expenses 11 Net operating income $ 251.000 5 116,550 5 144.450 1,633.500 5 1,131.075 502,425 5 97,500 $ 132,000 5 294,000 5 1,068.000 $12.795,000 43500 37,200 BOREO 741,060 R.571910 54000 5 94 2005 213, 1205 326040 4223070 3.000000 5.1.223.070 100 ON 670 33.0% 26.15 1 Projections for This Year: 10 Last year whit Sales 5 Change in unitates Change inline prices 1 Change in variable penses Change in used expenses 150,000 OS ON ON ON 1 2 2 3 4 Total X Welchted Average CM per Unit 100% 150,000 2 Sales mia percentages 32 Unit sales Selling price per unit 5 24 variable expense per unit s Contribution martin per units 6 9,000 29.00 $ 12.955 16053 115 15 500 99.00 S 68 555 30455 48 5.000 8500 5 42.50 S 42.50 5 13 19,500 109.00 $ 85.00 $ 2400 $ 5 6 7 R 9 10 11 12 13 14 15 18% 28 SN 5% 046 7% 19 21 49 X 4.500 27 000 3.000 74500 9,000 15 000 10 500 1.500 3.000 6,000 12,000 19.00 S 119.00 5 39.00 $ 79.00 S 69.00 95.00 S 59.00 $ 65.00 5 S 4400 S 4900 5 89.00 635 5 92.00 14305 33.18 S 30.36 S 77 60 5 25 40 5 2900 S 12.40 S 1348 S 6133 12.555 27.00 5 24,70S 45 R2 S 38645 17.40 5 33 36003 3:50 S 15 525 27.17 85,500 53.213,000 5 117,000 5 592500 S 621.000 $1,425,000 S 659.500 S 97500 S 932.000 S 294000 $ 1,068,000 28.575 2.484.000 42.900 748850 273.240 1.161,000 266,700 43,500 37200 30,380 7441.960 56,9355729.000 5729000 S 74,100 74 100 5 343,550 5347,760 5 261,000 5 352.2005 54.000 5 94,800 $ 5 211.120 5 326 040 $261 0005 116,550 S 144.450 1,633 5005 1,131,073 502/6255 510,000 5 2125.500 5 255,000 1657.500 255.000 468 000 100 ON 67 W ON S $12.795.000 2.571.930 4.223,070 3 000 000 5 1.223070 20 Variable expenses Contribution margin ed expenses 01 Net Operating income 32 Front Graph marion 21 Profit Data Point 150 000 301 CVP Grog information Data Points Sales Fixed Expenses Total Expenses S IS 3.000.000 S300X1000 150,000 5 12795,000 3,000,000 5 11571,930 43 Goal Seek Charts Conditional Formatting Requirement 1 Recuirement 2 Requirement 3 Requirement 4