Breakeven Analysis Completing this activity will help you learn to: Determine the breakeven point and sales necessary to achieve a target profit. Extract information from a data set using Excel formulas, functions, and/or pivot tables. Scenario: Company A is planning to open a manufacturing facility that will produce one product. The company is analyzing 40 products in terms of costs and breakeven points to determine which one to select. The Data worksheet contains a list of products with cost information. Each row provides information about one product, including its color, unit selling price, unit variable cost, and total fixed cost. Required: X Open spreadsheet Product Product 1 Product 2 Product 3 Product 4 Product 5 Product 6 Product 7 Product 8 Product 9 Product 10 Product 11 Product 12 Product 13 Product 14 Product 15 Product 16 Product 17 Product 18 Product 19 Product 20 Product 21 Product 22 Product 23 Product 24 Product 25 Product 26 Product 27 Product 28 Product 29 Product 30 Product 31 Product 32 Product 33 Product 34 Product 35 Product 36 Product 37 Product 38 Product 39 Product 40 Product 41 Product 42 Product 43 Product 44 Product 45 Product 46 Product 47 Product 48 Product 49 Product 50 Product 51 Product 52 Product 53 Product 54 Product 55 Product 56 Product 57 Product 58 Product 59 Product 60 Product 65 Product 66 Product 67 Product 68 Product 69 Product 70 Color Blue Green Purple White Purple Green White Yellow Gray Green Gray Blue Green Orange Black Orange Brown Blue Green Gray Black Purple Red Yellow Red Product Product Product Green Green Brown Red Yellow Yellow Green Yellow Purple Black Black Orange Red Black White Brown Green Black Blue Brown Purple Orange Gray Orange Red Green Brown White Purple Product 61 Purple Product 62 White Product 63 Blue Product 64 Green Brown Red Gray Red Gray Green Gray Product 71 Product 72 Green Red White Green Black Purple White Product 73 Black Product 74 Product 75 Product 76 Product 77 Product 78 Orange Blue Orange Orange Red Product 79 Product 80 Product 81 Product 82 Product 83 Product 84 Product 85 Purple Product 86 Product 87 Blue Product 88 White Product 89 Yellow Product 90 Orange Product 91 Green Product 92 Brown Product 93 White Orange Black Gray Black White Orange Blue Product 94 Black Product 95 Black Product 96 Yellow Product 97 Gray Product 98 Purple Product 99 Brown Product 100 Green Product 101 Brown Product 102 Blue Product 103 Black Product 104 Brown Product 105 White Product 106 Brown Product 107 Black Product 108 Orange Product 109 Green Product 110 Brown Product 111 Gray Product 112 Yellow Product 113 Purple Product 114 Gray Product 115 Black Product 116 Brown Product 117 Blue Product 118 Green Product 119 Product 120 Green Product 121 Green Product 122 Yellow Product 123 Gray Product 124 Orange Product 125 White Product 126 Brown Product 127 Green Product 128 White Product 129 Orange Product 130 Blue Product 131 Red Product 132 Orange Product 133 Orange Product 134 Purple Product 135 Green Product 136 Black Product 137 White Product 138 White Product 139 Orange 140 Black Product Gray Product 141 Brown Product 142 White Product 143 Brown Product 144 Red Product 145 Brown Product 146 Red Product 147 Yellow Product 148 Black Product 149 Black Product 150 Green Product 151 Orange 152 Orange Product Product 153 Brown Product 154 Red 155 Green Red Product Product 156 Product 157 Red Product 158 Brown Product 159 Brown Product 160 Orange Product 161 Brown Product 162 Product 163 Purple Red Product 164 Green Product 165 Purple Product 166 Yellow Product 167 Red Product 168 White 169 White 170 White Product Product Product Purple 171 Product 172 Orange Product 173 Brown 174 Yellow Product Product 175 Black Product 176 White Product 177 Brown Product 178 Orange Product 179 Gray Product 180 Purple Product 181 Blue Product 182 Orange Product 183 Gray Product 184 Blue 185 Orange Product Product 186 White Product 187 Brown Product 188 Black Product 189 Red Product 190 Yellow Product 191 Yellow Product 192 Orange Product 193 Green Product 194 Orange 195 Purple 196 Green 197 Brown Product 198 Yellow Product 199 Orange Product 200 Purple Unit Sales Price $693 $160 $863 $352 $497 $755 $757 $284 $83 $818 $371 $339 $717 $103 $516 $385 $133 $379 $637 $679 $789 $833 $515 $687 $614 $743 $632 $445 $236 $255 $748 $774 $264 $397 $706 $290 $523 $688 $774 $520 $798 $690 $189 $766 $231 $637 $95 $85 $137 $259 $743 $266 $428 $146 $340 $313 $524 $658 $648 $815 $697 $423 $241 $832 $678 $869 $761 $126 $598 $473 $656 $613 $495 $74 $420 $114 $285 $590 $654 $755 $893 $411 $758 $474 $716 $680 $466 $303 $870 $629 $534 $522 $692 $233 $658 $827 $843 $407 $832 $827 $864 $678 $321 $620 $634 $679 $223 $299 $707 $610 $810 $494 $369 $234 $157 $828 $586 $747 $637 $592 $108 $355 $124 $585 $312 $120 $426 $377 $396 $143 $201 $450 $805 $137 $696 $302 $460 $832 $697 $467 $538 $428 $119 $850 $814 $676 $75 $763 $695 $496 $325 $322 $717 $358 $429 $807 $281 $139 $449 $258 $472 $304 $130 $729 $704 $183 $367 $899 $805 $890 $420 $136 $297 $817 $382 $154 $754 $715 $99 $448 $333 $874 $698 $645 $446 $454 $547 $529 $473 $252 $440 $349 $248 $559 $156 $664 $254 $796 $206 $569 Unit Variable Cost $416 $96 $518 $211 $298 $453 $454 $170 $50 $491 $223 $203 $430 $62 $310 $231 $80 $227 $382 $407 $473 $500 $309 $412 $368 $446 $379 $267 $142 $153 $449 $464 $158 $238 $424 $174 $314 $413 $464 $312 $479 $414 $113 $460 $139 $382 $57 $51 $82 $155 $446 $160 $257 $88 $204 $188 $314 $395 $389 $489 $418 $254 $145 $499 $407 $521 $457 $76 $359 $284 $394 $368 $297 $44 $252 $68 $171 $354 $392 $453 $536 $247 $455 $284 $430 $408 $280 $182 $522 $377 $320 $313 $415 $140 $395 $496 $506 $244 $499 $496 $518 $407 $193 $372 $380 $407 $134 $179 $424 $366 $486 $296 $221 $140 $94 $497 $352 $448 $382 $355 $65 $213 $74 $351 $187 $72 $256 $226 $238 $86 $121 $270 $483 $82 $418 $181 $276 $499 $418 $280 $323 $257 $71 $510 $488 $406 $45 $458 $417 $298 $195 $193 $430 $215 $257 $484 $169 $83 $269 $155 $283 $182 $78 $437 $422 $110 $220 $539 $483 $534 $252 $82 $178 $490 $229 $92 $452 $429 $59 $269 $200 $524 $419 $387 $268 $272 $328 $317 $284 $151 $264 $209 $149 $335 $94 $398 $152 $478 $124 $341 Fixed Cost $709,120 $172,160 $1,104,000 $262,260 $1,683,540 $2,385,800 $2,030,100 $492,480 $173,580 $474,150 $592,000 $860,880 $1,742,090 $252,150 $937,300 $357,280 $107,590 $1,140,000 $1,058,250 $1,357,280 $805,800 $749,250 $1,382,260 $1,314,500 $1,820,400 $344,520 $928,510 $1,375,940 $434,280 $400,860 $1,988,350 $2,021,200 $181,260 $262,350 $2,067,060 $185,600 $802,560 $1,666,500 $2,349,800 $1,803,360 $2,864,620 $1,374,480 $475,000 $1,306,620 $478,400 $991,950 $175,560 $188,360 $349,800 $323,440 $1,116,720 $316,940 $1,484,280 $469,220 $618,800 $272,500 $1,764,000 $1,914,640 $1,274,280 $2,764,480 $2,050,650 $1,429,740 $807,360 $1,964,700 $1,823,830 $2,561,280 $1,596,000 $428,500 $1,718,410 $370,440 $1,616,540 $2,151,100 $1,199,880 $247,200 $633,360 $137,540 $149,340 $1,390,040 $1,815,660 $1,579,460 $2,231,250 $365,720 $2,154,330 $912,000 $2,090,660 $462,400 $1,008,120 $820,380 $2,324,640 $856,800 $1,320,380 $315,590 $664,800 $270,630 $2,019,840 $2,585,110 $407,770 $1,160,560 $2,347,650 $1,446,470 $2,979,060 $747,960 $770,560 $1,745,920 $1,828,800 $1,955,680 $299,040 $192,000 $1,831,010 $1,454,240 $1,111,320 $326,700 $708,920 $350,620 $289,800 $1,926,420 $1,944,540 $1,136,200 $2,287,350 $1,431,480 $347,010 $1,224,040 $176,000 $1,476,540 $1,011,250 $404,160 $1,057,400 $1,102,300 $545,100 $475,380 $91,200 $219,600 $431,480 $389,950 $1,584,600 $423,500 $848,240 $1,132,200 $767,250 $256,190 $516,000 $449,730 $321,600 $1,536,800 $2,239,620 $783,000 $140,700 $600,850 $864,580 $966,240 $243,100 $797,220 $1,159,480 $1,161,160 $995,880 $1,253,240 $621,600 $419,440 $360,000 $710,700 $1,646,190 $927,200 $352,560 $2,403,160 $575,280 $574,510 $342,510 $2,329,200 $1,036,840 $2,192,960 $1,293,600 $93,960 $797,300 $1,929,300 $738,990 $430,280 $2,150,240 $2,090,660 $141,600 $905,740 $911,050 $1,697,500 $658,440 $1,217,760 $1,596,660 $1,605,240 $799,350 $1,040,920 $1,476,090 $529,240 $184,800 $396,200 $244,530 $360,640 $261,640 $319,200 $851,700 $2,035,200 $627,300 $1,780,680 Complete two columns on the Data worksheet: Breakeven Point in Units and Breakeven Point in Sales R1. Cell G2: Enter a formula that results in the breakeven point in units. R2. Cell H2: Enter a formula that results in the breakeven point in total sales dollars. $ R3. Copy down the entries in cells G2 and H2 to all the other rows with data. Steps to create a pivot table: S1. Select a cell in the data on the Data worksheet. S2. Select Insert / PivotTable. The data range will automatically be inserted. A "Create PivotTable" window should appear. S3. In the window, under "Choose where you want the PivotTable report to be placed", select "New Worksheet". A new worksheet should appear with the Pivot Table. S4. A PivotTable Field List should pop up. Drag fields into rows, columns, filters, and values areas as needed. (If this gray work box does not appear, right click in the pivot table and select "S Field List".) Questions: Use the information on the Data worksheet and pivot tables, formulas, and/or functions to answer the following questions. Q1. What is the highest breakeven point (in sales dollars) among all the red products? Q2. What is the lowest unit contribution margin among the following six products: Product 12, Product 13, Product 154, Product 17, Product 3, and Product 96? $ Q3. What is the amount of net income generated on Product 77 if actual total sales are $593,600? $ Q4. How many additional units of Product 52 must be sold beyond its breakeven point to achieve a target profit of $42,400? Q5. Company A performed its analysis and narrowed the choices down to four products. Based on profitability only, which product should the company manufacture? Product 50 Expected sales: 13,330 units Product 91 Expected sales: 15,560 units Product 131 Expected sales: 14,520 units Product 158 Expected sales: 17,670 units