please help me its so urgent

Excal Fe Est View Format Tons Data Window Help DO Mountain Sports Ltd Case Study ACCT1110 Spring-VF-2 insert Draw Page Layout Formulas Data Review View Tell me Comments WEAT Calibri 11 - General AK D-A- Desi #73103- 57 Ay Sty Pa Ca 21 x fx A D C 1 Question 2-Segmented Income Statement & Product me Anal (53) 2 Mountain Soorts cames a limited line of products and services that can be divided into four departmentsegment 1.000-country packages kis boots, and poles 2Mountain bike Accessories Parts and service The owners of the company have been pleased with the operating income to date as shown in the company's overall income statement in Question 1 However, they want to get a better idea of how each of its four departments are doing. The junior accountant, Kelly, thought a segmented income statement would provide the information the owners are looking for, however, Kelly has never prepared a segmented income statement. While Kelly tried to create the segmented statement, she has asked you to review it and make changes as necessary. Additional notes have been provided to the income statement so that you may assess if the junior accountant's statement is correct. Ultimately, it is up to you to provide a corrected segmented income statement and provide recommendations to the owners A note from the junior accountant, Kelly Hapain! I have to admit, I don't have much experience with segmented income statements. Our manager, Sarah, was trying to tell me about common and traceable feed costs Iddet understand tot redeep that between u gove it my best shot but fm really hoping you'll be able to help me. Here's some of the information and assumptions used when creating this segmentencomment 1. The sales and cost of goods sold information was taken directly from each department. My manager checked this over and it was correct) You can use this information when you re-state the segmented income statement below the parts & service expenses acomet) 21 just allocated a lot of the expenses equally to the product lines, I wasn't exactly sure what to do. You can see which ones lalocated equally below which include: depreciation property taxes, rent and unities. There's some notes to the segmented income statement below which I think should help you determine how to properly do it or maybe I did do it properly (hmmm, I'm not sure? My manager said all of these expenses will stay the same even if any one of the productes were eliminated in other words, the company would continue to incur all these costs regardless of which product lines are offered * There's a lot of information in the notes regarding salaries and commissions. The parts and service department is treated a bit differently because those employees don't receive commissions Soi just figured out the total salaries and commissions for the other employees and it it equally between cross country skis, mountain bikes and accessories. However, my manager said that these employees will continue to work at the company no matter what they sell all the products in the store) Does that matter? There was some advertising that was spent specifically on each department and then some that was for the company as a whole at kind of guessed how to put this into the statement Maybe this has something to do with the common s C af 03 03 125% stv A MacBook Air Home Fare Condo Fam Ca Mo o il Sut& Fod& Wed 15:24 D Share Home Parte Tools Help Mountain Sports Ltd Case Study ACCT1116 Spring-VF-2 insert Draw Formulas Data Tell me art Cabri A A 0. 28W Test Merge & Centr General 5-99 Deler Find A A- 2330 MA Conditional Forma Cal Formatting Format SWIT Dar 11 xfx| A D E H G 1 F . toermor marry There was some advertising that was spent specifically on each department and then some that was for the company as a whole. I just kind of guessed how to put this into the statement. Maybe this has something to do with the common i traceable expense thing? This is so hard! Help! have a feeling that my statement isn't correct since I ignored the whole common vs. traceable fixed expenses thing and fallocated things equally across some product lines. Can you help me create the segmented income statement properly? Kelly, Junior Accountant: Mountain Sports Ltd. Segmented Income Statement For the Year Ended Dec 31, 2019 Mountain Bikes 719 914 This is my fest amempt at the segmented income statement. I think it's safe to say that I need help Notes Cross Country Skid Packages Accessories Parts & Service 353,106 Sales 695,709 2 Cost of goods sold SON 42% 294,285 28% 100,459 TOTAL 126.615 16 20,676 105,99 M Cost of goods sold 356,357 Gross Profit 401,424 363,557 252,647 Operating expenses Advertising 2 33.671 43,798 8363 7,668 2 5,936 5,996 5.936 5,936 3 9.396 9,395 9,396 9,396 4 32,215 32,215 32,215 32,215 5 38.055 39.379 19,315 0 6 111,853 111,853 111.853 68.000 7 25.985 25,985 25.983 25.985 257,111 268.563 213.564 149,200 144,313 94,993 1064 43,262 8 2.500 2,500 2.500 141.813 36,584 45,762 22,667 11.505 4,125 $119,146 $25.079 549 887 + Excel IN Edit File View insert Format 89 Page Layout IV- 7 Depreciation Property taxm Pent Parts & service expense 1 Salaties & commissions Utilities Total Operating Expenses 4 Operating income fo s interest expense ncome (Los) before taxes 7 income tax Net income (Los) 11 02 15 Data Window Review View 6- $ 27% 00 04 2,500 92,493 23,456 $69,038 #tv A MacBook Ail 1,895,344 771,777 1,123,567 94,000 23,745 37,583 128,860 96,749 403,560 103.941 888,439 235,128 10,000 225.128 61253 $163,376 . So Wed Jun 15 5 Share Fin Cor DO NOT make changes in this statement. This is for information only. There is space below to creati the segmented income statement proper format A C D 28 Net Income Loss) $119,146 $690008 $75379 $163,376 1988 29 30 Notes to the financial statements: 1. Advertising is committed to at the beginning of the period by management. Management has many different forms of advertising campaigns with different focuses as follows 31 Cross country ski packages Mountain Bikes Accessories Parts & Service TOTAL Promotes company as a whole 10,000 $ 32 33 5 30,000 $ 40,000 $ 7,000 $ 94,000 7,000 $ 34 35 2. The company uses the straight-line method of depreciation 37 3. The property taxesare set by the City and do not change with changes in sales volume. 38 39 4. Renthas two components 40 Monthly amount 6,000 Percentage based on each 41 department's sales 3% 42 43 5 Parts and services expensearies with sales of cross country skis, mountain bikes, and accessories. There are no parts & service expense for the parts & service department 44 45 6 Sales commissionare paid on sales for cross country skis, mountain bikes, and accessories. The Parts & Service department employees do not earn sales commissions on their sales 46 47 Sales commissions 11% Total yearly salary of employees that sell skis, bikes, and accessories. These employees will continue to work for the company regardless of which product lines are offered. AR $ 61,000 The store managers are paid a total yearly salary. They are responsible for all of the departments. These managers 45 will continue to work even if any of the divisions are closed 5 80,000 The Parts & Service Employees only work in that department. If this department were closed, the technicians 03 01 02 04 E1 x fr A C D E F G H 43 S.Parts and services expens@aries with sales of cross country skis, mountain bikes, and accessories. There are no parts & service expense for the parts & service department 44 45 6: Sales commissionare paid on sales for cross country sks, mountain bikes, and accessories. The Parts & Service department employees do not earn sales commissions on their sales 46 47 Sales commissions 11% Total yearly salary of employees that sell sks, bikes, and accessories. These employees will continue to work for the company regardless of which product lines are offered. 48 5 61,000 The store managers are paid a total yearly salary. They are responsible for all of the departments. These managers 49 will continue to work even if any of the divisions are closed $ 80,000 The Parts & Service Employees only work in that department. If this department were closed, the technicians 50 would be laid off. The total salary for the technicians is $ 68,000 51 52 7. Utilities are the same regardless of activity and will not change even if a product line is dropped 53 54 8. Interest expenses charged on the outstanding bank loans. The total interest expense is S 10,000 55 56 9 Income taxes are calculated for the company as a whole. The company will have to pay taxes regardless of which product lines it offers 57 Required: Part A: (25 marks) 1) Restate the segmented Income statement using the statement the Junior accountant prepared and the notes to the financial statements (Chapter 7 Appendix) Check figures have been provided to ensure you are on the right track. Notes: +0+ The percentage column should be taken as a percentage of sales (for example, cost of goods sold % for mountain bikes should be taken as a % of mountain bike sales) There may be small rounding differences where total amounts are off by $1, that is ok! Use full Excel functionality and entering formulas into each cell if you know how if not using a calculator is fine View insert Formal Tools Dat Window Help Autolave Home Insert Draw Page Layout Formulas Data Review View Tell me Calori 11 AA wp- P Fre&C :x fx| 59 50 61 6.3 Cross Country Ski Packages 64 Amount 65 SALES 695,709 66 Less: Variable Costs MUST BE LISTED ALPHABETICALLY 67 Cost of Goods Sold 294,385 69 30 71 TOTAL 72 CONTRIBUTION MARGIN 23 Less Traceable Fixed Conts (MUST BE LISTED ALPHABETICALLY 14 Adverting 75 Salaries PS TOTAL Traceable Fed Costs Case v 01 02 Bar 11 15 Percent 100% 42.3% Mountain Sports Ltd Case Study ACCT1116 Spring-VF-2 General NA Conf Formatting Mountain Sportst Segmented Income Statement by Product Line For the Year Ended December 31, 2019 Mountain Bikes Amount Percent 719,914 356,357 tv A MacBook Air Accessories Amount 353,106 100,459 Percent Cel 1000 28.5% - Wed Jun 15 5/24 PM Share Comments 2-57-0. PROST TM An Seutuly Set & Find & Fiter Daf TOTAL D Former Parts and Service Department Amount Percent 126.615 1000% 20676 16.3% Amount Percent 1895,344 100.0% 721,777 40.7% 120% Edit View Insert Format Tools Data Window Help Page Layout Home Inst Draw Cabri Pasl ET x fx 74 Advertising 75 Salaries 276 TOTAL Traceable Fixed Costs 77 SEGMENT MARGIN 78 Les Common Fixed Costs (MUST BE LISTED ALPHABETICALLY 79 30 BY 182 23 84 00 TU TOTAL Common F/C OPERATING INCOME 87 Les terest Expen Earrings before tant Mountain Sports Ltd Case Study ACCT1116 Spring-VF-2 Formulas Data Review View Tell me A A General KE 5520 54 01 02 15 CH tv A MacBook Air Conditional Forma Formatting as fable Styles G Ellwan 2 Wed Jun 15 524 PM Share Comments 29-0-9 5. Sand & 0 FOM Select ty Analyt Dais 1 125A famings before taxes Let Income Tax 90 NET INCOME 01 12 Part 2b: Analysis (1 mark) 31 2. Which is the most profitable department? 94- 3. Management is considering implementing a new advertising campaign one of the departments: Cross Country Skis, Mountain Bikes, or Accessories. The estimates oft Increase in sales is shown below. How much would income Increase in each department? 95 16 97 New advertising campaign.co 15,000 Estimated % increase in sales M crease in income 100 Cross Country S 32% 101 Mountain bis 30% 102 Accessories 15% 101 Lor Which department should the advertising be spent on 10% 1064 Explain to management how you completed the increase in income calculations and why you chose the department you did in part 3 107 Case in 01 07 tv A MacBook Air ER 15 #D 12556 5. Management has been trying to decide if any departments should be closed. Based on the segmented income statemesrepared what are your 209 recommendations to management? Which (if any) department should be closed? 110 111 6. Management had been considering closing the Parts & Service Department based on the segmented Income statement that the junior accountant prepared. If this s were closed, management estimates that sales for both Cross Country Skis and Mountain Bikes will decrease by 15%. Calculate the overall decrease in income to the com as a whole If the parts & service department is closed. 112 113 114 Show all amounts as positive: 115 Contribution Margin Lost Parts & Service Department 116 Cross Country Skis 117 Mountain Bikes 118 Total CM Lost 119 Fored Costs Avoided 120 increase (decrease) in income 121 122 7. Based on both quantitative and qualitative analysis, should the Parts & Service Department be closed? Explain to management in a way that they can understand. B 123 use your answer from part 6 in your response. Do not give textbook answers, use question datal 324 Cian Intro 01 02 00 04 #tv A MacBook Air Fox 15 A Foxed Costs Avoided Increase (decrease) in income 7. Based on both quantitative and qualitative analysis, should the Parts & Service Department be closed? Explain to management in a way that they can understand. B use your answer from part 6 in your response. Do not give textbook answers, use question datal Part 2c: Memo to the Junior Accountant (10 marks) Write a professional memo to the junior accountant explaining the changes you made to the segmented income statement. It may be worthwhile to explain common v raceable fixed expenses Textbook definitions will not be awarded markPlease use question data and your own words. Even though the Junior accountant has some accounting background, the owners do not. As such, sase ensure you write explanations in a way that both the junior accountant and the owners can understand. Memo Excal Fe Est View Format Tons Data Window Help DO Mountain Sports Ltd Case Study ACCT1110 Spring-VF-2 insert Draw Page Layout Formulas Data Review View Tell me Comments WEAT Calibri 11 - General AK D-A- Desi #73103- 57 Ay Sty Pa Ca 21 x fx A D C 1 Question 2-Segmented Income Statement & Product me Anal (53) 2 Mountain Soorts cames a limited line of products and services that can be divided into four departmentsegment 1.000-country packages kis boots, and poles 2Mountain bike Accessories Parts and service The owners of the company have been pleased with the operating income to date as shown in the company's overall income statement in Question 1 However, they want to get a better idea of how each of its four departments are doing. The junior accountant, Kelly, thought a segmented income statement would provide the information the owners are looking for, however, Kelly has never prepared a segmented income statement. While Kelly tried to create the segmented statement, she has asked you to review it and make changes as necessary. Additional notes have been provided to the income statement so that you may assess if the junior accountant's statement is correct. Ultimately, it is up to you to provide a corrected segmented income statement and provide recommendations to the owners A note from the junior accountant, Kelly Hapain! I have to admit, I don't have much experience with segmented income statements. Our manager, Sarah, was trying to tell me about common and traceable feed costs Iddet understand tot redeep that between u gove it my best shot but fm really hoping you'll be able to help me. Here's some of the information and assumptions used when creating this segmentencomment 1. The sales and cost of goods sold information was taken directly from each department. My manager checked this over and it was correct) You can use this information when you re-state the segmented income statement below the parts & service expenses acomet) 21 just allocated a lot of the expenses equally to the product lines, I wasn't exactly sure what to do. You can see which ones lalocated equally below which include: depreciation property taxes, rent and unities. There's some notes to the segmented income statement below which I think should help you determine how to properly do it or maybe I did do it properly (hmmm, I'm not sure? My manager said all of these expenses will stay the same even if any one of the productes were eliminated in other words, the company would continue to incur all these costs regardless of which product lines are offered * There's a lot of information in the notes regarding salaries and commissions. The parts and service department is treated a bit differently because those employees don't receive commissions Soi just figured out the total salaries and commissions for the other employees and it it equally between cross country skis, mountain bikes and accessories. However, my manager said that these employees will continue to work at the company no matter what they sell all the products in the store) Does that matter? There was some advertising that was spent specifically on each department and then some that was for the company as a whole at kind of guessed how to put this into the statement Maybe this has something to do with the common s C af 03 03 125% stv A MacBook Air Home Fare Condo Fam Ca Mo o il Sut& Fod& Wed 15:24 D Share Home Parte Tools Help Mountain Sports Ltd Case Study ACCT1116 Spring-VF-2 insert Draw Formulas Data Tell me art Cabri A A 0. 28W Test Merge & Centr General 5-99 Deler Find A A- 2330 MA Conditional Forma Cal Formatting Format SWIT Dar 11 xfx| A D E H G 1 F . toermor marry There was some advertising that was spent specifically on each department and then some that was for the company as a whole. I just kind of guessed how to put this into the statement. Maybe this has something to do with the common i traceable expense thing? This is so hard! Help! have a feeling that my statement isn't correct since I ignored the whole common vs. traceable fixed expenses thing and fallocated things equally across some product lines. Can you help me create the segmented income statement properly? Kelly, Junior Accountant: Mountain Sports Ltd. Segmented Income Statement For the Year Ended Dec 31, 2019 Mountain Bikes 719 914 This is my fest amempt at the segmented income statement. I think it's safe to say that I need help Notes Cross Country Skid Packages Accessories Parts & Service 353,106 Sales 695,709 2 Cost of goods sold SON 42% 294,285 28% 100,459 TOTAL 126.615 16 20,676 105,99 M Cost of goods sold 356,357 Gross Profit 401,424 363,557 252,647 Operating expenses Advertising 2 33.671 43,798 8363 7,668 2 5,936 5,996 5.936 5,936 3 9.396 9,395 9,396 9,396 4 32,215 32,215 32,215 32,215 5 38.055 39.379 19,315 0 6 111,853 111,853 111.853 68.000 7 25.985 25,985 25.983 25.985 257,111 268.563 213.564 149,200 144,313 94,993 1064 43,262 8 2.500 2,500 2.500 141.813 36,584 45,762 22,667 11.505 4,125 $119,146 $25.079 549 887 + Excel IN Edit File View insert Format 89 Page Layout IV- 7 Depreciation Property taxm Pent Parts & service expense 1 Salaties & commissions Utilities Total Operating Expenses 4 Operating income fo s interest expense ncome (Los) before taxes 7 income tax Net income (Los) 11 02 15 Data Window Review View 6- $ 27% 00 04 2,500 92,493 23,456 $69,038 #tv A MacBook Ail 1,895,344 771,777 1,123,567 94,000 23,745 37,583 128,860 96,749 403,560 103.941 888,439 235,128 10,000 225.128 61253 $163,376 . So Wed Jun 15 5 Share Fin Cor DO NOT make changes in this statement. This is for information only. There is space below to creati the segmented income statement proper format A C D 28 Net Income Loss) $119,146 $690008 $75379 $163,376 1988 29 30 Notes to the financial statements: 1. Advertising is committed to at the beginning of the period by management. Management has many different forms of advertising campaigns with different focuses as follows 31 Cross country ski packages Mountain Bikes Accessories Parts & Service TOTAL Promotes company as a whole 10,000 $ 32 33 5 30,000 $ 40,000 $ 7,000 $ 94,000 7,000 $ 34 35 2. The company uses the straight-line method of depreciation 37 3. The property taxesare set by the City and do not change with changes in sales volume. 38 39 4. Renthas two components 40 Monthly amount 6,000 Percentage based on each 41 department's sales 3% 42 43 5 Parts and services expensearies with sales of cross country skis, mountain bikes, and accessories. There are no parts & service expense for the parts & service department 44 45 6 Sales commissionare paid on sales for cross country skis, mountain bikes, and accessories. The Parts & Service department employees do not earn sales commissions on their sales 46 47 Sales commissions 11% Total yearly salary of employees that sell skis, bikes, and accessories. These employees will continue to work for the company regardless of which product lines are offered. AR $ 61,000 The store managers are paid a total yearly salary. They are responsible for all of the departments. These managers 45 will continue to work even if any of the divisions are closed 5 80,000 The Parts & Service Employees only work in that department. If this department were closed, the technicians 03 01 02 04 E1 x fr A C D E F G H 43 S.Parts and services expens@aries with sales of cross country skis, mountain bikes, and accessories. There are no parts & service expense for the parts & service department 44 45 6: Sales commissionare paid on sales for cross country sks, mountain bikes, and accessories. The Parts & Service department employees do not earn sales commissions on their sales 46 47 Sales commissions 11% Total yearly salary of employees that sell sks, bikes, and accessories. These employees will continue to work for the company regardless of which product lines are offered. 48 5 61,000 The store managers are paid a total yearly salary. They are responsible for all of the departments. These managers 49 will continue to work even if any of the divisions are closed $ 80,000 The Parts & Service Employees only work in that department. If this department were closed, the technicians 50 would be laid off. The total salary for the technicians is $ 68,000 51 52 7. Utilities are the same regardless of activity and will not change even if a product line is dropped 53 54 8. Interest expenses charged on the outstanding bank loans. The total interest expense is S 10,000 55 56 9 Income taxes are calculated for the company as a whole. The company will have to pay taxes regardless of which product lines it offers 57 Required: Part A: (25 marks) 1) Restate the segmented Income statement using the statement the Junior accountant prepared and the notes to the financial statements (Chapter 7 Appendix) Check figures have been provided to ensure you are on the right track. Notes: +0+ The percentage column should be taken as a percentage of sales (for example, cost of goods sold % for mountain bikes should be taken as a % of mountain bike sales) There may be small rounding differences where total amounts are off by $1, that is ok! Use full Excel functionality and entering formulas into each cell if you know how if not using a calculator is fine View insert Formal Tools Dat Window Help Autolave Home Insert Draw Page Layout Formulas Data Review View Tell me Calori 11 AA wp- P Fre&C :x fx| 59 50 61 6.3 Cross Country Ski Packages 64 Amount 65 SALES 695,709 66 Less: Variable Costs MUST BE LISTED ALPHABETICALLY 67 Cost of Goods Sold 294,385 69 30 71 TOTAL 72 CONTRIBUTION MARGIN 23 Less Traceable Fixed Conts (MUST BE LISTED ALPHABETICALLY 14 Adverting 75 Salaries PS TOTAL Traceable Fed Costs Case v 01 02 Bar 11 15 Percent 100% 42.3% Mountain Sports Ltd Case Study ACCT1116 Spring-VF-2 General NA Conf Formatting Mountain Sportst Segmented Income Statement by Product Line For the Year Ended December 31, 2019 Mountain Bikes Amount Percent 719,914 356,357 tv A MacBook Air Accessories Amount 353,106 100,459 Percent Cel 1000 28.5% - Wed Jun 15 5/24 PM Share Comments 2-57-0. PROST TM An Seutuly Set & Find & Fiter Daf TOTAL D Former Parts and Service Department Amount Percent 126.615 1000% 20676 16.3% Amount Percent 1895,344 100.0% 721,777 40.7% 120% Edit View Insert Format Tools Data Window Help Page Layout Home Inst Draw Cabri Pasl ET x fx 74 Advertising 75 Salaries 276 TOTAL Traceable Fixed Costs 77 SEGMENT MARGIN 78 Les Common Fixed Costs (MUST BE LISTED ALPHABETICALLY 79 30 BY 182 23 84 00 TU TOTAL Common F/C OPERATING INCOME 87 Les terest Expen Earrings before tant Mountain Sports Ltd Case Study ACCT1116 Spring-VF-2 Formulas Data Review View Tell me A A General KE 5520 54 01 02 15 CH tv A MacBook Air Conditional Forma Formatting as fable Styles G Ellwan 2 Wed Jun 15 524 PM Share Comments 29-0-9 5. Sand & 0 FOM Select ty Analyt Dais 1 125A famings before taxes Let Income Tax 90 NET INCOME 01 12 Part 2b: Analysis (1 mark) 31 2. Which is the most profitable department? 94- 3. Management is considering implementing a new advertising campaign one of the departments: Cross Country Skis, Mountain Bikes, or Accessories. The estimates oft Increase in sales is shown below. How much would income Increase in each department? 95 16 97 New advertising campaign.co 15,000 Estimated % increase in sales M crease in income 100 Cross Country S 32% 101 Mountain bis 30% 102 Accessories 15% 101 Lor Which department should the advertising be spent on 10% 1064 Explain to management how you completed the increase in income calculations and why you chose the department you did in part 3 107 Case in 01 07 tv A MacBook Air ER 15 #D 12556 5. Management has been trying to decide if any departments should be closed. Based on the segmented income statemesrepared what are your 209 recommendations to management? Which (if any) department should be closed? 110 111 6. Management had been considering closing the Parts & Service Department based on the segmented Income statement that the junior accountant prepared. If this s were closed, management estimates that sales for both Cross Country Skis and Mountain Bikes will decrease by 15%. Calculate the overall decrease in income to the com as a whole If the parts & service department is closed. 112 113 114 Show all amounts as positive: 115 Contribution Margin Lost Parts & Service Department 116 Cross Country Skis 117 Mountain Bikes 118 Total CM Lost 119 Fored Costs Avoided 120 increase (decrease) in income 121 122 7. Based on both quantitative and qualitative analysis, should the Parts & Service Department be closed? Explain to management in a way that they can understand. B 123 use your answer from part 6 in your response. Do not give textbook answers, use question datal 324 Cian Intro 01 02 00 04 #tv A MacBook Air Fox 15 A Foxed Costs Avoided Increase (decrease) in income 7. Based on both quantitative and qualitative analysis, should the Parts & Service Department be closed? Explain to management in a way that they can understand. B use your answer from part 6 in your response. Do not give textbook answers, use question datal Part 2c: Memo to the Junior Accountant (10 marks) Write a professional memo to the junior accountant explaining the changes you made to the segmented income statement. It may be worthwhile to explain common v raceable fixed expenses Textbook definitions will not be awarded markPlease use question data and your own words. Even though the Junior accountant has some accounting background, the owners do not. As such, sase ensure you write explanations in a way that both the junior accountant and the owners can understand. Memo