Excel Analytics 05-02 (Static) Cost-Volume-Profit Relationships [LO5-1, LO5-2, LO5-3, LO5-4, LO5-5, LO5-7, LO5-9]
Skip to question
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. Click here to download the Excel template, which you will use to answer the questions that follow.
Click here for a brief tutorial on Goal Seek in Excel.
Click here for a a brief tutorial on Charts in Excel.
Click here for a brief tutorial on Conditional Formatting in Excel.
rev: 05_07_2020_QC_CS-210952, 01_09_2021_QC_CS-246235
Excel Analytics 05-02 (Static) Part 7
6-f. In the Excel template, using Goal Seek, calculate the total unit sales required to break even given the revised sales mix. How does your answer compare to the break-even point that you calculated in requirement 3a?
In the Excel template, using Goal Seek, calculate the total unit sales required to break even given the revised sales mix.
How does this break-even point compare to the break-even point from requirement 3a?
| |
| | | It is | less than | the break-even point from requirement 3a. | |
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 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
