Hi

Can anyone help me in budgeting, please find attached.

Thank You

Management Accounting \"Adding time to your day\" is the slogan of Holzmann Cleaning Services (HCS). The company was started by Gretel, who began offering cleaning services to support herself while she was in university studying for a business degree. At the age of 21 she was determined to be known as more than a simple labourer who needed to fund her education. She researched different cleaning products and methodologies to deliver a fast and thorough service to her clients. Ten years later, the business has grown in size and has expanded throughout the city and local rural areas. Gretel is the majority shareholder of the private corporation with two other shareholders. The company consists of a full-time staff of three operations managers (one for commercial operations and two for residential operations), an administrative staff of three, and part-time cleaning staff of over 200 university students, called cleaning agents. Not only is Gretel able to provide high-quality cleaning services to her clients, but she is also able to offer university students part-time work, reducing the need for student loans. The company also owns a fleet of three vans that are used to transport cleaning agents to and from work sites. A fourth van is available for the operations managers to make site visits if necessary. Gretel's training regimen is thorough as potential hires are trained in the areas of cleaning products, effective cleaning methods and acceptable deportment when entering a client's residence or place of business. Through this training process, which Gretel carries out herself, she is able to hire only those individuals she feels would provide the high-quality service that keeps clients coming back. Recently, Gretel engaged Martine, a CPA and a local consultant, to review the latest operating statement. Gretel is discouraged with the 20X6 profit margin (Exhibit 1). Additionally, she is concerned with the cash flow as there are seasonal fluctuations in the work, with more work in the months of March and April when many homeowners request more hours for spring-cleaning chores. As such, Gretel must ensure that there is enough cash on hand to build cleaning supplies inventories and pay for training of any new recruits required to handle the extra work. Other than springtime, cleaning supplies inventory on hand is minimal. 2 / 13 Management Accounting Project She is hoping that Martine will provide some guidance that will help the company attain its profit margins. She has called a meeting with Martine, Sam, the operations manager of commercial operations and Ling, one of the operations managers of residential operations. GRETEL: I thought we had a pretty good year; sales were up, and we've had great reviews. Yet when I look at the bottom line, the profit margin is only 9%. I was hoping for at least 12%. According to industry statistics, the profit margins should be at least 25%. The other issue is that we were short on cash in the late summer. MARTINE: Have there been any significant changes in the operations in the past year? LING: I've noted that we're taking on more residential clients outside the city. I've had to make sure that we schedule in more travel time to reach the destination on time. SAM: And don't forget that at the request of a majority of our commercial clients, we've included more eco-friendly cleaning products. It helps boost their image of being socially responsible. MARTINE: I can imagine that this has increased both your travel and cleaning supplies costs. How do you currently cost out your services and how do you arrive at a chargeout rate? GRETEL: We treat the wage costs of our cleaning agents as direct costs. The cost of cleaning supplies, transportation and operations managers' salaries are considered overhead. We apply the overhead at $10 per direct labour hour and add on an additional 60% to arrive at our charge-out rate. MARTINE: Have you ever taken an activity-based approach to costing your services? Besides the increase in cleaning supplies' costs, are there some other significant differences in the costs consumed by your commercial clients versus the costs consumed by your residential clients? LING: Well, I've had a bit of free time on the residential side so I've helped Sam out on occasion with the commercial side. I've noted that most of the commercial clients are clustered around the downtown core. So it takes less time to travel there. GRETEL: Because our commercial clients have in-house accounts payable departments, we usually only bill them once per month. While our more recent residential clients are on monthly billing, we still have some that are on weekly billing. However, you should note that we spend almost three times more hours training cleaning agents who work for our commercial clients than training those who work for our residential clients. Part of the reason is that eco-friendly products require more application knowledge. 3 / 13 Management Accounting Project MARTINE: So I understand that the operations managers are not that busy on the residential side? What's the breakdown between residential and commercial clients? GRETEL: We have about 120 regular commercial clients. They account for 60% of the revenue, even though they amount to less than half of the clients. As for the residential side, I'm not sure we require both full-time operations managers. Alex has gone home early on some days because there was a shortage of work. MARTINE: And are your cash disbursements pretty much in line with your revenues? GRETEL: For the most part. Our major expenses are cleaning supplies and transportation, which mostly fluctuate with revenues, except for annual preventive maintenance on our vans, which takes place in the slower months of July and August. Oh, and one more thing: I would really like a solid way of giving a potential client a quote before we begin a contract with them. Right now, I measure the square metres and estimate how many hours it will take to complete the work. I multiply that by the direct labour hourly rate and then add on our $10 overhead factor per direct labour hour and 60% for profit. Any ideas? MARTINE: Let me work on this and get back to you with a full report. In the week following the meeting, Martine spent time with Gretel, the operations managers and the administrative staff. She developed the following: cost pools for indirect costs related to cleaning services along with the cost driver for each pool and activity breakdowns for residential and commercial clients (Exhibit 2) regression analyses summary results (Exhibit 3) to determine the statistical relationship between: o direct labour hours and transportation costs o direct labour hours and cleaning supplies costs details regarding expected growth for 20X7 (Exhibit 4) details regarding a typical commercial and residential client that could be used to develop quotes (Exhibit 5) details relating to cash collections and disbursements (Exhibit 6) Required: You are a partner in Martine's consulting business and Martine has contracted you to assist her with completing the analysis and developing a final report for Gretel. Prepare a report and supporting exhibits for Gretel, president of HCS, to analyze HCS's current cost allocations and provide advice for the future. It should include the following: 1. Prepare a 20X7 operating budget for both commercial and residential operations, based on the details provided in Exhibit 4. The operating budget will require adjustments to the activity-based analysis provided in requirement 2. Indirect general costs should be allocated 60% to commercial and 40% to residential. (6 marks) Provide a discussion of the results in your report. (4 marks) 2. (8 marks) a) Revise the 20X7 operating budget statements to a contribution format. Assume the following are variable costs: i. Wages and benefits ii. Cleaning supplies iii. Transportation costs All remaining costs are fixed. (1 mark) 5 / 13 Management Accounting Project Based on the 20X7 budget: b) Calculate the break-even point in sales dollars based on the 20X7 sales mix. (2 marks) c) Calculate the sales revenue required to achieve a 25% profit margin. (2 marks) d) Provide comments on the results in the report. Your discussion should include comments on the margin of safety (1 mark) and the limits of this analysis as it pertains to HCS. (2 marks) 3. To assist Gretel in submitting bids for new work, use the 20X7 budget to prepare an average markup on variable cost analysis for both commercial and residential cleaning services. Using the markup percentages, calculate the sales price of a typical commercial and residential contract (see Exhibit 5). (8 marks) Also provide a short discussion of the results in your report. (2 marks) 6 / 13 Management Accounting Project Exhibit 1 EXHIBIT 1 Holzmann Cleaning Services Report on Operations REVENUES AND EXPENSES Total revenue 20X5 20X6 20X6 Budget Variance from 20X5 actual Variance from 20X6 budget 728,910 819,000 778,050 90,090 12% 40,950 5% 233,747 292,184 277,575 58,437 25% 14,609 5% 66,406 88,541 67,291 22,135 33% 21,250 32% Cost of sales (direct expenses) Wages and benefits Cleaning supplies Transportation costs (including fuel, insurance, depreciation and maintenance) 23,619 30,281 25,890 6,662 28% 4,391 17% 116,000 439,772 120,000 531,006 118,000 488,756 4,000 3% 2,000 2% 91,234 21% 42,250 9% 289,138 287,994 289,294 175,750 185,000 166,500 9,250 5% 18,500 11% 2,703 2,350 2,303 (353) (13%) 47 2% 881 890 872 9 1% 18 2% 4,712 4,620 4,528 (92) (2%) 92 2% 10,320 10,320 10,114 0 0% 206 2% 998 1,050 1,029 52 5% 21 2% Business fees 4,200 4,200 4,116 0 0% 84 2% Advertising and promotion 1,890 2,520 2,470 630 33% 50 2% 2,594 204,048 2,730 213,680 2,675 194,607 136 5% 55 2% 9,632 5% 19,073 10% 85,090 74,314 94,687 (10,776) (13%) (20,373) (22%) Salaries of operations managers Cost of sales (direct expenses) Gross margin Operating expenses (indirect expenses) Administrative labour (training and billing) Depreciation (excluding transportation vehicles) Repairs and maintenance (excluding transportation) Utilities and telephone/telecommunication Rent Interest and bank charges Insurance (excluding transportation) Net operating profit/loss 11.67% 7 / 13 9.07% 12.17% Management Accounting Exhibit 2 Cleaning supplies commercial Cleaning supplies residential Billing Transportation Training Operations management Total expenses 8 / 13 Project Cost pools and activities for activity-based costing analysis Cost driver Square metres Square metres Number of invoices Number of kilometres Number of hours Number of managers $ $ $ $ $ $ $ Cost pool 70,833 17,708 129,500 30,281 55,500 120,000 423,822 Total Commercial Residential activity activity activity 664,054 664,054 442,703 442,703 3,054 1,062 1,992 53,124 37,187 15,937 554 138 416 3 1 2 Management Accounting Exhibit 3 Project Summary results of regression analyses Analysis of transportation costs and statistical relationship with direct labour hours TRANSPORTATION ANALYSIS USING LABOUR HOURS Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations 0.370106959 0.136979161 0.050677077 205.6109076 12 ANOVA df Regression Residual Total Intercept Labour hours 9 / 13 1 10 11 SS 67100.46346 422758.4532 489858.9167 MS 67100.46346 42275.84532 Coefficients 2006.01049 0.280500299 Standard Error 414.9577567 0.222646934 t-Stat 4.8342523 1.25984353 Management Accounting Project Analysis of cleaning-supply costs and statistical relationship with direct labour hours CLEANING SUPPLIES COST ANALYSIS USING LABOUR HOURS Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations 0.834454446 0.696314222 0.665945644 152.2094937 12 ANOVA df Regression Residual Total Intercept Labour hours 10 / 13 1 10 11 SS 531207.6171 231677.2996 762884.9167 MS 531207.6171 23167.72996 Coefficients 5922.619076 0.78922842 Standard Error 307.1846274 0.164820911 t-Stat 19.28032378 4.788399819 Management Accounting Exhibit 4 Project 20X7 expected growth data Change in dollars: Expected revenue growth (due to increase in charge-out rate to $39 per hour) Expected increase in cleaning supplies costs due to increase in volume (residential and commercial) Expected increase in commercial cleaning supplies costs due to price increase Labour wage and benefit rate per hour Expected increase in transportation costs 8% 7% 9% $13.46 9% Change in activity for ABC analysis: Increase in overall square metres of cleaning Increase in number of invoices (commercial only, see option 2 below for residential) Increase total kilometres driven (30% of total kilometres relate to commercial clients) All other expenses will be unchanged. 7% 5% 8% Cost reduction options: 1. Reduce operations management staff costs by putting one residential operations manager on half-time. This would reduce the total cost from $120,000 to $100,000. 2. Change all residential billing to monthly. This would reduce the number of residential invoices sent out by 10% from 20X6 activity. 11 / 13 Management Accounting Exhibit 5 Project Client data for contract quote development According to Gretel, a cleaning agent is able to clean approximately 50 square metres per hour, on average. Base the annual sales amount on 50 annual visits for commercial clients and a per-visit basis for residential clients. Use the following variable costs: Wages and benefits see 20X7 budget and information in Exhibit 4 for hourly rates. Cleaning-supply costs see 20X7 budget and information in Exhibit 4 for rate per square metre. Transportation costs see 20X7 budget and information in Exhibit 4 for rate per kilometre. Commercial client: Typical commercial client details are as follows: square metres kilometres driven per visit (round trip) 1,950 20 Residential client: Typical residential client details are as follows: square metres kilometres driven per visit (round trip) 232 25 12 / 13 Management Accounting Exhibit 6 Project Cash collection and disbursement activity May Sales revenue Purchases of cleaning supplies Transportation costs Direct labour wages Management salaries General operating expenses Cash collections: June July August September October $85,100 $62,900 $67,155 $49,210 $50,875 $68,265 $7,664 $2,022 $30,360 $25,417 $2,390 $7,240 $1,904 $22,440 $25,417 $2,390 $7,500 $2,323 $23,958 $25,417 $2,390 $6,850 $2,377 $17,556 $25,417 $2,390 $6,900 $1,327 $18,150 $25,417 $2,390 $7,575 $1,475 $24,354 $25,417 $2,390 Cash sales Credit collections: Amount collected in month of service Amount collected in month following service Amount collected in second month following service Uncollectable Cash disbursements: Transportation Cleaning supplies Direct labour Remaining expenses 100% 60% 40% 50% 50% 100% 25% 50% 30% 15% 5% Paid in month incurred Paid in month incurred Paid in month following Paid in month incurred Paid in month following Paid in month incurred Additional notes: Depreciation included in monthly general operating expenses Annual dividend payment to three shareholders, paid in August Expected July 1 beginning cash balance Marks summary: Profit analysis Cost analysis Operating budget Cost-volume-profit (CVP) analysis Contract pricing Cash budget Observations Report format and professionalism Total marks 13 / 13 8 19 10 8 10 10 2 3 70 $ 196 $8,000 $5,000