Requirement #1 Prepare an input page with all of the information from the case. Prepare a spreadsheet showing the Static Budget and Actual Results for The Squeaky Horn. You can use the Budgets provided on page 5 of the case as your check numbers. However, you should make the following changes for your spreadsheet:
- List the revenue from each of the 5 jobs out separately.
- Actual Owners Salary Bonus based on revenue and Delivery charges are not a whole dollar amounts. Make sure you round those calculation so the answer comes out to a whole dollar amount.
- The Owners Salary and the Orchestral Repairers Salaries are mixed costs. Separate out the fixed and the variable portions. The fixed portions should not be included in your contribution margin calculations. Check figures: Static CM = $419,845 and Actual CM = $369,500
- Replacement Parts should be separated based on Major Repairs and Minor Repairs
Requirement #2 Prepare the Level 0 and Level 1 (Static Budget) variances and indicate if they are favorable or unfavorable.
Requirement #3 Prepare the Flexible Budget and all level 2 variances.
- Flexible Owners Salary Bonus based on revenue and Delivery charges are not a whole dollar amounts. Make sure you round those calculation so the answer comes out to a whole dollar amount. Check figure: Flexible CM = $434,044
Requirement #4 Prepare the Level 3 variances for the VARIABLE expenses ONLY and indicate if they are favorable or unfavorable.
Requirement #5 Prepare the Level 3 Variances for the Sales Volume Variance REVENUE ONLY. Tell if they are favorable or unfavorable.
Requirement #6 Prepare a Segmented Income Statement using the actual results based on the 5 services The Squeaky Horn Offers. You should trace costs (both variable and fixed) to the various services if they can be traced. (Even though the rush jobs are local, allocate the delivery costs to them anyway.) Remember to separate the traceable fixed costs from the common fixed costs. Calculate the total Segmented Contribution Margin and show what that is on a per job.
Questions to Answer:
- Explain what is causing the level 3 variances in requirement #4.
- Explain what is causing the level 3 variances in requirement #5. Since this is only a revenue analysis you should use the information in the case (price changes, number of jobs), and the segmented income statement to discuss the differences.
- How does the way the workers are compensated impact the profit?
- Based on your answers to the questions above, what are your suggestions to the owners of The Squeaky Horn? Make sure you tie in your analysis from above to your suggestions.
THE SQUEAKY HORN Eugene Decker hung up his office phone and frowned. As part owner of the Squeaky Horn, a musical-instrument epair shop. Decker was responsible for setting the charges for various types of repairs. A potential customer had just called to inquire about the cost to repair the bridge on her cello. After Decker quoted an estimated price for the job, the woman had remarked, "Thank you for the quote, but I'll be going to Best Instrument Repair. I've heard they give good service, and their prices are lower than yours." Unfortunately, Decker had heard similar statements many times during the past few months. Ever since Best Instrument Repair had opened across town, Decker and his partners had found themselves having to compete for business more than ever before. To attract repair jobs and avoid layoffs, Decker and his partners had lowered prices for minor repairs for the first time in 10 years. Decker looked at the budgeted versus actual operating-profit statement on his desk (Exhibit 1). How could he tell what portion of the company's lost profits was due to the price decreases and how much was related to other factors? Background The Squeaky Horn was a musical-instrument repair shop that specialized in the repair and restoration of band and orchestral instruments. The shop was owned and managed by Decker and two partners, who were all well regarded for their exacting repair work and attention to detail. Professional musicians from all over the country sent their instruments to the Squeaky Horn for minor adjustments or major overhauls. Demanding concert and travel schedules placed great stress on the delicate parts of musical instruments, and professional musicians were careful to keep their instruments in peak condition. Service Lines Currently, the Squeaky Horn offered four main services: major and minor repairs and restorations of band instruments such as saxophones and French horns, and major and minor repairs and restorations of orchestral instruments such as violins and cellos. Historically, minor repairs were billed at a rate of $35.00 an hour for band instruments and $32.50 an hour for orchestral instruments. Major repairs and restorations were performed under individual flat-fee arrangements that were quoted to customers based on the type of work needed for each instrument. The Squeaky Horn's three owners performed all major repairs and restorations in the shop. Minor repairs of band instruments were performed by hourly employees, and minor repairs of orchestral instruments were performed by three full-time salaried employees. In addition, rush jobs for minor repairs were occasionally performed for local customers only. These jobs were subcontracted to a retired employee of the Squeaky Horn, who performed the repairs for $25 an hour. Rush jobs were billed under flat-fee arrangements that averaged $150 per job. The average rush job took three hours to complete. The Annual Operating Plan At the beginning of the current year, Decker's CPA had prepared the annual operating plan for the Squeaky Horn (Exhibit 1). The Squeaky Hom's business was small and relatively straightforward, which enabled the CPA to develop the company's operating budget using specific volume and revenue data for each product line. The following information was used in preparing the annual plan: 1. Based on prior years' work orders, the shop was expected to perform the following number of jobs in the coming year: 390 major band repairs, 1,830 minor band repairs, 540 major orchestral repairs, 1,560 minor orchestral repairs, and 50 rush jobs. Average major repairs were quoted at $400 and $300 for band and orchestral jobs, respectively. The average minor band repair took two hours to complete, whereas the average minor orchestral repair took four hours to complete. 2. All three partners drew annual base salaries of $60,000 plus bonuses of 5 percent of sales revenue. 3. The hourly employees were paid $20 an hour for work performed. 4. The salaried employees were paid annual base salaries of $38,000. To the extent that the number of minor orchestral repairs exceeded 1,560, the orchestral repairers were paid a flat rate of $80 per job to complete those repairs. 5. Replacement parts and other supplies were budgeted at $50 for each major job and $10 for each minor job (including rush jobs), based on experience. 6. Approximately 35 percent of the instruments that the Squeaky Horn worked on were shipped to the shop from out of town. The company expected to incur average shipping charges of $30 per package to ship the instruments back to their owners. 7. Advertising, depreciation, office rent, and miscellaneous expenses were budgeted as fixed expenses. Actual Results for the Year At the end of the year, Decker's CPA prepared the actual operating-profit statement shown in the last column of Exhibit 1. Although Decker knew the Squeaky Horn had experienced significant changes during the past year, he was still shocked to discover that the company's profit was $50,745 less than planned even though revenues had increased by $7,770. In order to understand fully the decrease in profits, Decker sat down and compiled the following information: 1. Owing to increased competition, minor band repairs were billed at $30 an hour, and minor orchestral repairs were billed at $28 an hour. 2. Planned versus actual jobs and average hours per job were as follows: Job Type Band major repairs Band minor repairs Orchestral major repairs Orchestral minor repairs Rush jobs Plan Volume No. of Jobs 390 1,830 540 1,560 50 Plan Hrs./Job 7.0 2.0 6.0 4.0 3.0 Actual Volume No. of Jobs 450 1,740 510 1,650 55 Actual Hrs./Job 7.0 3.0 7.0 3.6 2.8 Total 4,370 4,405 3. On average, major repairs and restorations required more replacement parts than in past years. Therefore, replacement-parts expenses for major repairs increased an average of $10 per job for the year. In addition, shipping expenses increased by approximately $5 per package 4. Advertising expenses were $500 more than budgeted owing to an unplanned printing of flyers for band camp. Miscellaneous expenses, however, were $100 less than budgeted. Exhibit 1 THE SQUEAKY HORN Schedule of Planned versus Actual Operating Results Plan Actual Revenue $ 656,400 $ 664,170 Owner Salaries - Base Owner Salaries - Bonus Band Repairers Wages Orchestral Repairers Salaries Rush Job Wages Replacement Parts Delivery Contribution 180,000 32,820 73,200 114,000 3,750 80,900 45,885 125,845 180,000 33,209 104,400 121,200 3,850 92,050 53,961 75,500 Advertising Depreciation Office Rent Miscellaneous 12,000 3,600 48,000 4,500 12,500 3,600 48,000 4,400 Profit $ 57,745 $ 7,000 THE SQUEAKY HORN Eugene Decker hung up his office phone and frowned. As part owner of the Squeaky Horn, a musical-instrument epair shop. Decker was responsible for setting the charges for various types of repairs. A potential customer had just called to inquire about the cost to repair the bridge on her cello. After Decker quoted an estimated price for the job, the woman had remarked, "Thank you for the quote, but I'll be going to Best Instrument Repair. I've heard they give good service, and their prices are lower than yours." Unfortunately, Decker had heard similar statements many times during the past few months. Ever since Best Instrument Repair had opened across town, Decker and his partners had found themselves having to compete for business more than ever before. To attract repair jobs and avoid layoffs, Decker and his partners had lowered prices for minor repairs for the first time in 10 years. Decker looked at the budgeted versus actual operating-profit statement on his desk (Exhibit 1). How could he tell what portion of the company's lost profits was due to the price decreases and how much was related to other factors? Background The Squeaky Horn was a musical-instrument repair shop that specialized in the repair and restoration of band and orchestral instruments. The shop was owned and managed by Decker and two partners, who were all well regarded for their exacting repair work and attention to detail. Professional musicians from all over the country sent their instruments to the Squeaky Horn for minor adjustments or major overhauls. Demanding concert and travel schedules placed great stress on the delicate parts of musical instruments, and professional musicians were careful to keep their instruments in peak condition. Service Lines Currently, the Squeaky Horn offered four main services: major and minor repairs and restorations of band instruments such as saxophones and French horns, and major and minor repairs and restorations of orchestral instruments such as violins and cellos. Historically, minor repairs were billed at a rate of $35.00 an hour for band instruments and $32.50 an hour for orchestral instruments. Major repairs and restorations were performed under individual flat-fee arrangements that were quoted to customers based on the type of work needed for each instrument. The Squeaky Horn's three owners performed all major repairs and restorations in the shop. Minor repairs of band instruments were performed by hourly employees, and minor repairs of orchestral instruments were performed by three full-time salaried employees. In addition, rush jobs for minor repairs were occasionally performed for local customers only. These jobs were subcontracted to a retired employee of the Squeaky Horn, who performed the repairs for $25 an hour. Rush jobs were billed under flat-fee arrangements that averaged $150 per job. The average rush job took three hours to complete. The Annual Operating Plan At the beginning of the current year, Decker's CPA had prepared the annual operating plan for the Squeaky Horn (Exhibit 1). The Squeaky Hom's business was small and relatively straightforward, which enabled the CPA to develop the company's operating budget using specific volume and revenue data for each product line. The following information was used in preparing the annual plan: 1. Based on prior years' work orders, the shop was expected to perform the following number of jobs in the coming year: 390 major band repairs, 1,830 minor band repairs, 540 major orchestral repairs, 1,560 minor orchestral repairs, and 50 rush jobs. Average major repairs were quoted at $400 and $300 for band and orchestral jobs, respectively. The average minor band repair took two hours to complete, whereas the average minor orchestral repair took four hours to complete. 2. All three partners drew annual base salaries of $60,000 plus bonuses of 5 percent of sales revenue. 3. The hourly employees were paid $20 an hour for work performed. 4. The salaried employees were paid annual base salaries of $38,000. To the extent that the number of minor orchestral repairs exceeded 1,560, the orchestral repairers were paid a flat rate of $80 per job to complete those repairs. 5. Replacement parts and other supplies were budgeted at $50 for each major job and $10 for each minor job (including rush jobs), based on experience. 6. Approximately 35 percent of the instruments that the Squeaky Horn worked on were shipped to the shop from out of town. The company expected to incur average shipping charges of $30 per package to ship the instruments back to their owners. 7. Advertising, depreciation, office rent, and miscellaneous expenses were budgeted as fixed expenses. Actual Results for the Year At the end of the year, Decker's CPA prepared the actual operating-profit statement shown in the last column of Exhibit 1. Although Decker knew the Squeaky Horn had experienced significant changes during the past year, he was still shocked to discover that the company's profit was $50,745 less than planned even though revenues had increased by $7,770. In order to understand fully the decrease in profits, Decker sat down and compiled the following information: 1. Owing to increased competition, minor band repairs were billed at $30 an hour, and minor orchestral repairs were billed at $28 an hour. 2. Planned versus actual jobs and average hours per job were as follows: Job Type Band major repairs Band minor repairs Orchestral major repairs Orchestral minor repairs Rush jobs Plan Volume No. of Jobs 390 1,830 540 1,560 50 Plan Hrs./Job 7.0 2.0 6.0 4.0 3.0 Actual Volume No. of Jobs 450 1,740 510 1,650 55 Actual Hrs./Job 7.0 3.0 7.0 3.6 2.8 Total 4,370 4,405 3. On average, major repairs and restorations required more replacement parts than in past years. Therefore, replacement-parts expenses for major repairs increased an average of $10 per job for the year. In addition, shipping expenses increased by approximately $5 per package 4. Advertising expenses were $500 more than budgeted owing to an unplanned printing of flyers for band camp. Miscellaneous expenses, however, were $100 less than budgeted. Exhibit 1 THE SQUEAKY HORN Schedule of Planned versus Actual Operating Results Plan Actual Revenue $ 656,400 $ 664,170 Owner Salaries - Base Owner Salaries - Bonus Band Repairers Wages Orchestral Repairers Salaries Rush Job Wages Replacement Parts Delivery Contribution 180,000 32,820 73,200 114,000 3,750 80,900 45,885 125,845 180,000 33,209 104,400 121,200 3,850 92,050 53,961 75,500 Advertising Depreciation Office Rent Miscellaneous 12,000 3,600 48,000 4,500 12,500 3,600 48,000 4,400 Profit $ 57,745 $ 7,000
