70.2K

Verified Solution

Link Copied!

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:

Open spreadsheet

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. fill in the blank 1 of 1

R2. Cell H2: Enter a formula that results in the breakeven point in total sales dollars. fill in the blank 1 of 1$

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 PivotTable. 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 "Show 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 purple products? fill in the blank 1 of 1

Q2. What is the lowest unit contribution margin among the following six products: Product 128, Product 13, Product 168, Product 18, Product 3, and Product 50? fill in the blank 1 of 1$

Q3. What is the amount of net income generated on Product 44 if actual total sales are $2,147,000? fill in the blank 1 of 1$

Q4. How many additional units of Product 49 must be sold beyond its breakeven point to achieve a target profit of $121,180? fill in the blank 1 of 1

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

table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} tr {mso-height-source:auto;} col {mso-width-source:auto;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; text-align:general; vertical-align:bottom; border:none; white-space:nowrap; mso-rotate:0;} .xl20 {font-weight:700; text-align:center;} .xl22 {text-align:center;}

Product	Color	Unit Sales Price	Unit Variable Cost	Fixed Cost
Product 1	Blue	$571	$343	$1,338,360
Product 2	Green	$792	$475	$1,382,120
Product 3	Purple	$798	$479	$449,790
Product 4	White	$171	$103	$611,320
Product 5	Purple	$659	$395	$1,848,000
Product 6	Green	$433	$260	$889,220
Product 7	White	$813	$488	$1,059,500
Product 8	Yellow	$365	$219	$410,260
Product 9	Gray	$819	$491	$1,964,720
Product 10	Green	$364	$218	$820,520
Product 11	Gray	$261	$157	$710,320
Product 12	Blue	$402	$241	$1,354,010
Product 13	Green	$848	$509	$1,596,690
Product 14	Orange	$437	$262	$1,141,000
Product 15	Black	$344	$206	$394,680
Product 16	Orange	$440	$264	$1,059,520
Product 17	Brown	$170	$102	$114,920
Product 18	Blue	$406	$244	$396,900
Product 19	Green	$702	$421	$1,399,380
Product 20	Gray	$824	$494	$2,640,000
Product 21	Black	$528	$317	$1,276,550
Product 22	Purple	$156	$94	$352,160
Product 23	Red	$428	$257	$1,321,830
Product 24	Yellow	$95	$57	$69,540
Product 25	Red	$515	$309	$700,400
Product 26	Green	$380	$228	$942,400
Product 27	Green	$213	$128	$204,000
Product 28	Brown	$604	$362	$1,919,060
Product 29	Red	$238	$143	$650,750
Product 30	Yellow	$70	$42	$49,000
Product 31	Yellow	$355	$213	$543,860
Product 32	Green	$667	$400	$2,181,390
Product 33	Yellow	$381	$229	$1,292,000
Product 34	Purple	$471	$283	$947,520
Product 35	Black	$701	$421	$1,730,400
Product 36	Black	$888	$533	$2,783,200
Product 37	Orange	$71	$43	$99,960
Product 38	Red	$192	$115	$376,530
Product 39	Black	$181	$109	$214,560
Product 40	White	$245	$147	$800,660
Product 41	Brown	$496	$298	$1,449,360
Product 42	Green	$61	$37	$82,080
Product 43	Black	$674	$404	$2,313,900
Product 44	Blue	$177	$106	$624,090
Product 45	Brown	$390	$234	$929,760
Product 46	Purple	$296	$178	$892,080
Product 47	Orange	$535	$321	$1,228,360
Product 48	Gray	$716	$430	$2,350,920
Product 49	Orange	$414	$248	$602,580
Product 50	Red	$180	$108	$74,880
Product 51	Green	$275	$165	$404,800
Product 52	Brown	$185	$111	$463,240
Product 53	White	$451	$271	$1,566,000
Product 54	Red	$752	$451	$1,182,930
Product 55	White	$289	$173	$206,480
Product 56	Green	$198	$119	$522,980
Product 57	Black	$82	$49	$252,450
Product 58	Purple	$312	$187	$1,068,750
Product 59	White	$494	$296	$609,840
Product 60	Purple	$445	$267	$1,171,240
Product 61	Purple	$637	$382	$1,823,250
Product 62	White	$774	$464	$2,219,600
Product 63	Blue	$766	$460	$2,203,200
Product 64	Green	$560	$336	$1,673,280
Product 65	Brown	$887	$532	$923,000
Product 66	Red	$492	$295	$797,850
Product 67	Gray	$221	$133	$741,840
Product 68	Red	$569	$341	$1,874,160
Product 69	Gray	$713	$428	$1,815,450
Product 70	Green	$808	$485	$2,690,590
Product 71	Gray	$443	$266	$635,430
Product 72	Green	$538	$323	$1,735,050
Product 73	Black	$658	$395	$265,630
Product 74	Orange	$817	$490	$931,950
Product 75	Blue	$146	$88	$495,900
Product 76	Orange	$814	$488	$430,320
Product 77	Orange	$62	$37	$194,250
Product 78	Red	$562	$337	$1,075,500
Product 79	Orange	$676	$406	$1,601,100
Product 80	Black	$406	$244	$633,420
Product 81	Gray	$283	$170	$484,770
Product 82	Black	$524	$314	$268,800
Product 83	White	$606	$364	$280,720
Product 84	Orange	$280	$168	$943,040
Product 85	Blue	$740	$444	$1,201,760
Product 86	Purple	$712	$427	$1,248,300
Product 87	Blue	$503	$302	$910,530
Product 88	White	$565	$339	$657,660
Product 89	Yellow	$751	$451	$375,000
Product 90	Orange	$639	$383	$1,297,920
Product 91	Green	$231	$139	$638,480
Product 92	Brown	$671	$403	$750,400
Product 93	White	$107	$64	$251,980
Product 94	Black	$570	$342	$278,160
Product 95	Black	$173	$104	$438,840
Product 96	Yellow	$279	$167	$424,480
Product 97	Gray	$318	$191	$229,870
Product 98	Purple	$403	$242	$692,300
Product 99	Brown	$534	$320	$357,380
Product 100	Green	$709	$425	$971,280
Product 101	Brown	$725	$435	$1,789,300
Product 102	Blue	$365	$219	$1,305,240
Product 103	Black	$224	$134	$506,700
Product 104	Brown	$437	$262	$1,359,750
Product 105	White	$172	$103	$592,710
Product 106	Brown	$693	$416	$831,000
Product 107	Black	$632	$379	$462,990
Product 108	Orange	$275	$165	$833,800
Product 109	Green	$60	$36	$24,240
Product 110	Brown	$100	$60	$220,400
Product 111	Gray	$460	$276	$544,640
Product 112	Yellow	$383	$230	$333,540
Product 113	Purple	$399	$239	$1,414,400
Product 114	Gray	$307	$184	$653,130
Product 115	Black	$457	$274	$949,770
Product 116	Brown	$148	$89	$195,880
Product 117	Blue	$581	$349	$1,032,400
Product 118	Green	$407	$244	$519,970
Product 119	Gray	$65	$39	$26,780
Product 120	Green	$431	$259	$454,080
Product 121	Green	$877	$526	$744,120
Product 122	Yellow	$536	$322	$1,382,440
Product 123	Gray	$753	$452	$1,041,460
Product 124	Orange	$863	$518	$3,011,850
Product 125	White	$281	$169	$771,680
Product 126	Brown	$477	$286	$362,900
Product 127	Green	$260	$156	$846,560
Product 128	White	$594	$356	$754,460
Product 129	Orange	$717	$430	$358,750
Product 130	Blue	$324	$194	$912,600
Product 131	Red	$272	$163	$517,750
Product 132	Orange	$657	$394	$578,600
Product 133	Orange	$277	$166	$291,930
Product 134	Purple	$830	$498	$919,640
Product 135	Green	$728	$437	$2,144,670
Product 136	Black	$856	$514	$998,640
Product 137	White	$566	$340	$537,880
Product 138	White	$425	$255	$1,475,600
Product 139	Orange	$495	$297	$776,160
Product 140	Black	$217	$130	$274,050
Product 141	Brown	$490	$294	$1,677,760
Product 142	White	$720	$432	$702,720
Product 143	Brown	$127	$76	$393,720
Product 144	Red	$747	$448	$508,300
Product 145	Brown	$770	$462	$985,600
Product 146	Red	$230	$138	$533,600
Product 147	Yellow	$562	$337	$992,250
Product 148	Black	$501	$301	$1,310,000
Product 149	Black	$502	$301	$1,047,210
Product 150	Green	$131	$79	$335,400
Product 151	Orange	$520	$312	$894,400
Product 152	Orange	$100	$60	$120,800
Product 153	Brown	$247	$148	$190,080
Product 154	Red	$789	$473	$2,758,680
Product 155	Green	$849	$509	$2,614,600
Product 156	Red	$806	$484	$2,340,940
Product 157	Red	$316	$190	$1,081,080
Product 158	Brown	$163	$98	$505,700
Product 159	Brown	$254	$152	$173,400
Product 160	Orange	$80	$48	$63,360
Product 161	Brown	$125	$75	$230,500
Product 162	Purple	$487	$292	$1,663,350
Product 163	Red	$402	$241	$386,400
Product 164	Green	$216	$130	$575,340
Product 165	Purple	$621	$373	$1,165,600
Product 166	Yellow	$499	$299	$1,704,000
Product 167	Red	$312	$187	$838,750
Product 168	White	$74	$44	$247,200
Product 169	White	$719	$431	$610,560
Product 170	White	$169	$101	$282,880
Product 171	Purple	$454	$272	$1,243,060
Product 172	Orange	$689	$413	$1,722,240
Product 173	Brown	$288	$173	$546,250
Product 174	Yellow	$770	$462	$2,069,760
Product 175	Black	$684	$410	$1,156,280
Product 176	White	$621	$373	$1,527,680
Product 177	Brown	$771	$463	$729,960
Product 178	Orange	$348	$209	$668,590
Product 179	Gray	$168	$101	$566,150
Product 180	Purple	$297	$178	$395,080
Product 181	Blue	$234	$140	$654,240
Product 182	Orange	$133	$80	$175,430
Product 183	Gray	$597	$358	$1,008,580
Product 184	Blue	$244	$146	$108,780
Product 185	Orange	$373	$224	$283,100
Product 186	White	$196	$118	$489,840
Product 187	Brown	$70	$42	$35,000
Product 188	Black	$243	$146	$811,890
Product 189	Red	$647	$388	$2,242,940
Product 190	Yellow	$648	$389	$2,105,670
Product 191	Yellow	$638	$383	$869,550
Product 192	Orange	$820	$492	$482,160
Product 193	Green	$872	$523	$631,690
Product 194	Orange	$851	$511	$1,502,800
Product 195	Purple	$894	$536	$755,380
Product 196	Green	$603	$362	$1,542,400
Product 197	Brown	$681	$409	$340,000
Product 198	Yellow	$596	$358	$949,620
Product 199	Orange	$316	$190	$1,076,040
Product 200	Purple	$419	$251	$601,440

Answer & Explanation Solved by verified expert

See Answer