Founded in 1960, the Arpeggio Music Company is a family-owned and operated firm specializing in the educational music market. Located in the US. middle-west, its products and services include instruments (the firm has exclusive distribution rights for many well-known brands such as Yamaha and Selmer), sheet music, instrument repairs, accessories, and novelty Items. It also rents studio space to music teachers. Arpeggio's key customer base is public and private schools (essentially the Boards of Education with the music directors as key decision makers) but they also sell to individual amateur and professional musicians, private music teachers, church choir directors, families, etc. The company (AMC) has limited advertising and web communication in the traditional sense, but they do sponsor regular concerts for the students taking lessons in their studio rooms. They use this platform to communicate to parents, to show how the music students are progressing, and to develop general brand awareness for the firm. The costs for the current year's concert planning budget appear below. AMC has tentatively planned on five major works or pieces with a total of 40 concerts. For example, they plan to rehearse Grieg's Peer Gynt Suite N degree 1 and perform it multiple times during the year. Like many amateur orchestras and bands, they hire professional musicians to fill holes and bolster weak instrument sections. For example, if they need four French horns for a piece, but only three students are ready for prime time, they would hire a professional for the concert. The professionals donate their time to the company and have a special arrangement with the union: they are only paid for the performance time, not for any required rehearsal time. Conversely, occasionally the company will need to hire musicians for rehearsals only. Typically this would be a piano player or possibly some other critical instrument for a specific piece. Some of the professional musicians may in fact be teachers who rent studio space from AMC. AMC also hires sound engineers and stagehands to help with the performances. Joe Trazinski, the president, conducts most of the concerts himself, but occasionally AMC does hire a professional due to Joe's time constraints. On occasion, AMC finds itself hiring rehearsal conductors, but this is rare. Some of the costs vary with the number of pieces, some with the number of concerts, and some are fixed and depend on neither the number of pieces nor the number of concerts. The cost of sheet music, stage equipment, and instruments depends on the number pieces planned for the year. If AMC needs a harpsichord for a Bach piece they are playing, for example, they rent it for the full year. The cost is the same regardless of how many times they play the piece. Given that one piece is highlighted for any given concert, the cost of advertising also depends on the repertoire for the given year. On the other hand, AMC believes that wages of musicians, conductors, stagehands, ticket collectors, and ushers vary with the number of concerts. The higher the number of concerts, the higher the wages. Likewise, the cost of renting the concert hall and printing the programs will vary with the number of concerts. Administrative expenses are more difficult to categorize. AMC's best estimate is that about 60% of the budgeted costs are fixed, 30% depend on the number of pieces performed, and the remaining 10% depend on the number of concerts. After the start of the year, the company ran into serious sheet music supply problems with one of the modern works that they had planned, so the number of pieces was reduced to four. And at the "request" of an influential parent who wanted her daughter to be less stressed, the number of concerts was decreased to 38. Data concerning actual costs appear below: 1. Prepare a flexible budget for Arpeggio Concerts based on the actual activity for the year. Send your (perfect) Excel spreadsheets with your budgets to me via email. Make sure the file is properly titled and your equations and formatting are good. 2. Prepare a report that summarizes the spending variances for all expense items and all the issues at hand. 3. If you were Arpeggio management, would you be pleased with how well costs were controlled during the year? Why or why not? Which costs and activities should they be monitoring most closely? 4. The cost formulas provide figures for the average cost per piece and per concert. How accurate do you think these figures would be for predicting the cost of adding a new piece or an additional performance of a given work? Explain. 5. An employee has suggested giving a small gift or prize to each student participant. Do you think this is a good idea? How much would you spend? What would you suggest for a gift? How would you budget for this potential cost? Do you need a new cost driver? Explain the pros and cons of adding a new cost driver. Using your analysis, add a second sheet to your Excel chart showing a new planning budget for the year with the additional expense for the gift. Label your sheets clearly. 6. Arpeggio management is considering discontinuing this concert activity because of the time involved. Is this a good idea? Why or why not? What additional Information would you need in order to advise them? Be specific and complete