CBH Company CBH Company sells bicycles that they buy from various manufacturers. They are in the process of preparing a Master Budget including the Sales Budget, The Purchasing Budget, the Cash Budget and the Income Statement for 2022. The yearly budget is broken into quarters. The year end is December 31st. Requirements: Requirement A: Your group has been requested to compile various budget schedules for the fiscal year 2022. Your submission should include the following (there is a tab for each; 1. Sales budget for each quarter including totals for the year 2. Purchasing Budget for each quarter including totals for the year 3. Cost of Goods Sold Schedule for the year 4. Selling and Administration budget for each quarter including totals for the year 5. Budgeted Income Statement for the year 6. Contribution Format Budgeted Income Statement for the year 7. Budgeted Cash Statement for the year broken down by quarter 8. Work sheet for Cash Collections and Disbursements Requirement B: Your group has been requested to complete the following CVP analysis for fiscal 2022. Use the CVP Analysis Tab to complete this analysis. Ignore interest expense for this section. 1. What is the breakeven point in units sold? What is the breakeven point in sales dollars? 2. How many units need to be sold to earn an aftertax profit of $ 500,000 3. What is the margin of safety in $? Assume a tax rate of 30% 4. If sales were to increase by 15% in 2023 from 2022 projected levels and expense behaviour remains the same for 2023 as in 2022, how much will net income before tax and interest increase by as a percentage. Use operating leverage to answer this. 5. If CBH spend $ 50,000 on an advertising campaign, it believes sales (units) will increase 5.00% without any change in average selling price. Should it take on the advertising? Why or why not? Additional details: Below you will see yellow data boxes labeled with the letters A through M. Your instructor will provide your group with the data to enter into these boxes. This data set is unique to your group, so make sure you enter the data correctly. Average estimated selling price per electric bike is There are $325 1,840 bicycles in Bike Inventory at the end of 2021 with a value of $ 303,600 15% of next quarters estimated sales 2,000 bikes are required in ending inventory 75% of sales will be cash and 25% will be credit sales. At the end of each quarter, CBH Company wishes to have units in Bike Inventory. At the end of 2022 assum For each quarter, it is estimated that Of the credit sales, 80% pay in the quarter of the sale and 20% pay in the following quarter. Credit sales from Q4 2021 were $430,000 Assume operating expenses occur evenly throughout the year and are all paid in cash. For each quarter, the purchase and Q4 2021 were CBH Company will pay 70% of Bike purchases are paid for in cash in the quarter of 30% are paid in the following quarter. Total bike purchases in $510,000 The expected average purchase price of a bike in 2022 is $60,000 in dividends in Q4 2022. $ 175 The cash balance in the bank at the end of 2021 is $ 10,000. CBH Company wishes to maintain a minimum cash balance of $10,000 in the bank for each quarter. Budgeted 2022 sales volumes in units for each quarter are: Q1 3200 Q2 4300 Q3 3600 Q4 1900 Selling and Administration expenses for the budgeted year are as follows; Sales Wages: Sales wages are a mixed cost (salary and commissions). CBH Company plans on estimating these costs for 2022 based on actual historical data using the high-low method. Here are the total sales in units and the total sales wages expense for the last 4 years. Units sold $ 2021 12585 111803 2020 12905 113883 2019 11385 104003 2018 8175 83138 Administrative Salaries Computer costs Fixed Costs: $ Accounting & professional services 1800 77000 Advertising 9000 4200 Depreciation 35000 2300 1400 1200 500 20000 3000 155400 Office Supplies Printing Insurance Property taxes Rent Utilities Total Fixed Costs CBH Company will purchase a new store on 1/1/2022 worth payments. The first payment will be in Q1 and the second in Q3. Taxation is $ 500,000 to be be paid for in 2 equal 30% on taxable income and paid at the end of Q 4 each year. For Cost of Sales (COS); Add total purchases + Beginning Bike Inventory - Ending Bike Inventory. Interest expense for a quarter is calculated based on the loan balance at the end of the previous quarter and is calculated based on an annual rate of The loan balance at the end of 2021 was 5.00% $ 78,000 Schedule of receipts from customers Accounts Receivable - 31st Dec 2021 First Quarter Second Quarter Third Quarter Fourth Quarter Total Payments:- Cash Sales Credit Sales Cash Sales Credit Sales Qtr 1 Receipts by Quarter Qtr 2 2023 Qtr 3 Qtr 4 Qtr 1 $ $ 780,000 $ $ 104,812,500 $ 430,000 $ 260,000 $ 349,375 86,000 208,000 $ 52,000 105,092,000 $ 69,875 $ 877,500 $ 292,500 $ $ 463,125 $ 154,375 $ 106,933,125 $ 1,056,250 $ 294,000 $ 105,144,000 $ 1,111,500 $ 58,500 $ 586,625 1,181,375 $ 645,125 Collection of Credit sales in the quarter of the sale Collection of Credit sales in the following quarter Schedule of expected payments for Purchases Accounts Payable - 31st Dec 2021 First Quarter Second Quarter Third Quarter Fourth Quarter Total Payments:- Purchases paid in cash in the same quarter Purchases paid in the following quarter 75% 25% 80% 20% Purchases Qtr 1 Payments by Quarter Qtr 2 2023 Qtr 3 Qtr 4 Qtr 1 $ 510,000 $ $ 350,875 $ 153,000 245,613 $ $ 734,125 $ $ 585,375 105,263 513,888 $ $ $ 632,625 $ 2,813,000 $ 398,613 $ 619,150 $ 220,238 409,763 $ 175,613 $ 442,838 630,000 $ 618,450 70% 30% Bike Purchases Budget Quarter 1 2 3 4 TOTAL Expected Sales Units 3,200 4,300 3,600 1,900 13,000 Add desired ending of Inventory 645 540 285 2,000 3,470 Total needs 3,845 4,840 3,885 3,900 16,470 Less beginning of Inventory 1,840 645 540 285 3,310 Required Purchases: Units 2,005 4,195 3,345 3,615 13,160 Purchase Cost per unit 175 175 175 175 175 Cost of Material to be purchased $350,875.00 $ 734,125.00 $585,375.00 $632,625.00 $2,303,000.00 Selling and Administrative Expense Budget Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year Budgeted Sales in units: 3,200 4,300 3,600 1,900 13,000 Variable Cost: Sales Wages 20,800 $ 27,950 $ 23,400 $ 12,350 $ 84,500 Total Variable Costs: $ 20,800 $ 27,950 $ 23,400 $ 12,350 $ 84,500 Fixed Costs: Accounting & professional services $ 443 $ 595 $ 498 $ 263 $ 1,800 Administrative salaries 18,954 $ 25,469 $ 21,323 $ 11,254 $ 77,000 Sales Wages - fixed portion S 7,385 $ 9,923 $ 8,308 $ 4,385 $ 30,001 Advertising $ 2,215 $ 2,977 $ 2,492 $ 1,315 $ 9,000 Computer costs $ 1,034 $ 1,389 $ 1,163 $ 614 $ 4,200 Depreciation $ 8,615 $ 11,577 $ 9,692 $ 5,115 $ 35,000 Office Supplies $ 566 $ 761 $ 637 $ 336 $ 2,300 Printing $ 345 $ 463 $ 388 $ 205 $ 1,400 Insurance $ 295 $ 397 $ 332 $ 175 $ 1,200 Property taxes 123 $ 165 $ 138 $ 73 $ 500 Rent S 4,923 $ 6,615 $ 5,538 $ 2,923 $ 20,000 Utilities $ 738 $ 992 $ 831 S 438 $ 3,000 Total Fixed Costs: Total costs: $ 45,637 $ 61,325 $ $ 66,437 $ 89,275 $ 51,342 74,742 $ $ 27,097 $ 185,401 39,447 $ 269,901 Cash Total per quarter: $ $ 57,822 $ 77,698 $ 65,049 $ 34,332 $ 234,901 Note: Deduct Depreciation.