This should be done in Excel with formulas.

2 4 8 14 32 44 7 D 10 12 16 18 20 22 24 26 28 30 36 38 Music Teachers, Inc., is an educational association for music teachers that has 20.000 members. The association operates from a tral headquarters but has local membership chapters throughout the United States. Monthly meetings are bold by the local chapters to discuss real developments ou topics of interest to music teachers. The association's magazine, Teachers' Forum, is issued monthly with features about recent developments in the field. The association publishes books and reports and also sponsors professional courses that qualify for continuing professional education credit. The associnion's statement of revers and expenses for the current year is presented below Music Teachers, Inc. Statement of Revenues and Expenses For the Year Ended November 20 $ $ 3,275,000 920,000 230,000 290,000 800,000 Revenges Expenees Salaries Personnel costs Occupancy costs Reimbursement of member costs to local chapters Other membership services Printing and paper Postage and shipping Instructors' fees General and administrative Tocal expenses Excess of resch Vere PSC 320,000 176,000 90,000 38.000 3,144 131.000 $ The board of directes of Music Teachers, Inc., bus requested that a segmented income statement be prepared showing the contribution of each segment to the association. The association has fuur segments: Membership Division, Magazine Subscriptions Division, Books and Reports Division, and Continuing Education Division, Mike Dryle has been assigned responsibility for preparing the segmented income statement, and he has gathered the following data: 1. The 20.000 members of the association pay dues of $100 per year, of which $20 covers a cine year subscription to the Teachers' Forum.Other benefits include membership in the association and chapter affiliation. The portion of the dues covering the magazine subscription ($20) should he assigned to the Magazine Subscriptions Division b. A total of 2,500 vne-year subscriptions to Teachers' Forum were also suld last year to nunmembers and libraries at $30 per subscription. In addition to subscriptions, the journal generated $100,000 in advertising revenues. c. The costs to produce the reachers' Forum Magazine included $7 per subscription for printing and paper and $4 per sahscription for postage and shipping. d. A wtal of 28,000 tochnical reports and professional texts were sokl by the Books and Reports Division at an average selling price per unit of $25. Average costs per publication were St for printing and paper and s2 for postage and shipping. e. The association offers a variety of continuing education courses to both members and manmembers. The one day courses had a tuition cost of $75 each and were attended by 2,400 students. A total of 1,760 students took two day courses at a tuition cost of $125 for each student. Outside instructors were paid to teach some cases f. Salary costs and space occupied by division follow Salaries Space Occupied square feet) S Membership Magazine Subscriptions Books and Reports Coacinuing Education Corporate start Teal 2101.IMNI 150 MMI 300.000 180.000 80.000 9211.INNI 2000 2.000 3,000 2,000 1.000 S Personnel costs are 25% of salaries in the separate divisions as well as for the corporale stall. The $280,000 in occupancy costs includes $50,000 in scalal cost for a warehouse used by the Books and Reports Division for storage purposes. Assume that this cost could be avoided if the division were eliminated 2. Printing and paper costs other than for magazine subscriptions and for books and reports relate to the Continuing Education Division h. General and administrative cxpenses include costs relating to overall administration of the association as a whole. The company's corporale stali does sume mailing of materials for general administrative purposes. The expenses that can be traced or assigned to the corporate staif, as well as any other expenses that are not traceable to the segments, will be treated as common costs. It is not necessary to distinguish between variable and fixed costs. Required: 1. Prepare a segmented income statement for Music Teachers, Inc. This statement should show the segment marin for each division as well as results for the association as a whole. 2 4 8 14 32 44 7 D 10 12 16 18 20 22 24 26 28 30 36 38 Music Teachers, Inc., is an educational association for music teachers that has 20.000 members. The association operates from a tral headquarters but has local membership chapters throughout the United States. Monthly meetings are bold by the local chapters to discuss real developments ou topics of interest to music teachers. The association's magazine, Teachers' Forum, is issued monthly with features about recent developments in the field. The association publishes books and reports and also sponsors professional courses that qualify for continuing professional education credit. The associnion's statement of revers and expenses for the current year is presented below Music Teachers, Inc. Statement of Revenues and Expenses For the Year Ended November 20 $ $ 3,275,000 920,000 230,000 290,000 800,000 Revenges Expenees Salaries Personnel costs Occupancy costs Reimbursement of member costs to local chapters Other membership services Printing and paper Postage and shipping Instructors' fees General and administrative Tocal expenses Excess of resch Vere PSC 320,000 176,000 90,000 38.000 3,144 131.000 $ The board of directes of Music Teachers, Inc., bus requested that a segmented income statement be prepared showing the contribution of each segment to the association. The association has fuur segments: Membership Division, Magazine Subscriptions Division, Books and Reports Division, and Continuing Education Division, Mike Dryle has been assigned responsibility for preparing the segmented income statement, and he has gathered the following data: 1. The 20.000 members of the association pay dues of $100 per year, of which $20 covers a cine year subscription to the Teachers' Forum.Other benefits include membership in the association and chapter affiliation. The portion of the dues covering the magazine subscription ($20) should he assigned to the Magazine Subscriptions Division b. A total of 2,500 vne-year subscriptions to Teachers' Forum were also suld last year to nunmembers and libraries at $30 per subscription. In addition to subscriptions, the journal generated $100,000 in advertising revenues. c. The costs to produce the reachers' Forum Magazine included $7 per subscription for printing and paper and $4 per sahscription for postage and shipping. d. A wtal of 28,000 tochnical reports and professional texts were sokl by the Books and Reports Division at an average selling price per unit of $25. Average costs per publication were St for printing and paper and s2 for postage and shipping. e. The association offers a variety of continuing education courses to both members and manmembers. The one day courses had a tuition cost of $75 each and were attended by 2,400 students. A total of 1,760 students took two day courses at a tuition cost of $125 for each student. Outside instructors were paid to teach some cases f. Salary costs and space occupied by division follow Salaries Space Occupied square feet) S Membership Magazine Subscriptions Books and Reports Coacinuing Education Corporate start Teal 2101.IMNI 150 MMI 300.000 180.000 80.000 9211.INNI 2000 2.000 3,000 2,000 1.000 S Personnel costs are 25% of salaries in the separate divisions as well as for the corporale stall. The $280,000 in occupancy costs includes $50,000 in scalal cost for a warehouse used by the Books and Reports Division for storage purposes. Assume that this cost could be avoided if the division were eliminated 2. Printing and paper costs other than for magazine subscriptions and for books and reports relate to the Continuing Education Division h. General and administrative cxpenses include costs relating to overall administration of the association as a whole. The company's corporale stali does sume mailing of materials for general administrative purposes. The expenses that can be traced or assigned to the corporate staif, as well as any other expenses that are not traceable to the segments, will be treated as common costs. It is not necessary to distinguish between variable and fixed costs. Required: 1. Prepare a segmented income statement for Music Teachers, Inc. This statement should show the segment marin for each division as well as results for the association as a whole