CBH lCompany 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 scal year 2022. Your submission should include the following [there is a tab for each; . Sales budget for each quarter including totals for the year . Purchasing Budget for each quarter including totals for the year . Cost of IGoods Sold Schedule for the year I .l . Selling and Administration budget for each quarter including totals for the year . Budgeted Income Statement for the year . Contribution Format Budgeted Income Statement for the year . Budgeted Cash Statement for the year broken down by quarter . Work sheet for Cash Collections and Disbursements OO'H-JOWLJ'ILLUhJih Requirement B: Your group has been requested to complete the following C'VP analysis for scal 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 S 500,000 .Assume a tax rate of 3. What is the margin of safety in S? 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 5 50,000 on an advertising campaign, it believes sales {units} will increas 5.00% without any change in average selling price. Should it take on the advertising? Why or why not? ndeitional details: ielow you will see yellow data boxes labeled with the letters A through M. Your instructor will provide your group with he data to enter into these boxes. This data set is unique to your group, so make sure you enter the data correctly. I Average estimated selling price per electric bike is $325 I There are bicycles in Bike Inventory at the end of 2021 with a value ofS 303,600 At the end of each quarter, CBH Company wishes to have 15% of next quarters estimated sales units in Bike Inventory. At the end of 2022 assum 2.000 bikes are required in ending inventory I For each quarter, it is estimated that 75% of sales will be cash and 25% will be credit sales. Of the credit sales. 80% pay in the quarter of the sale and 20% pay in the following quarter. Credit sales from [14 2021 were $430,000 I Assume operating expenses occur evenly throughout the year and are all paid in cash. I For each quarter, 20% of Bike purchases are paid for in cash in the quarter of the purchase and 30% are paid in the following quarter. Total bike purchases in Q4 2021 were $510,000 The expected average purchase price of a bike in 2022 is S 175 I CBH Company will pay $60,000 in dividends in [14 2022. I The cash balance in the bank at the end of 2021 is 5 10,000 . CBH Company wishes to maintain a minimum cash balance of $10,000 in the bank for each quarter. I Budgeted 2022 sales volumes in units for each quarter are: m o2 : ea cm I 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 highlow method. Here are the total sales in units and the total sales wages expense for the last 4years. Units sold 5 2021 12585 111803 2020 12905 113883 2019 11385 104003 2018 817'5 83138 2018 8175 83138 Fixed Costs: Accounting & professional services 1800 Administrative Salaries 77000 Advertising 9000 Computer costs 4200 Depreciation 35000 Office Supplies 2300 Printing 1400 Insurance 1200 Property taxes 500 Rent 20000 Utilities 3000 Total Fixed Costs 155400 CBH Company will purchase a new store on 1/1/2022 worth $ 500,000 to be be paid for in 2 equal payments. The first payment will be in Q1 and the second in Q3. Taxation is 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 5.00% The loan balance at the end of 2021 was $ 78,000Schedule of receipts from customers Receipts by Quarter 2023 Cash Sales Credit Sales Qtr 1 Qtr 2 Qtr 3 Qtr 4 Qtr 1 Accounts Receivable - 31st Dec 2021 S 430,000 S 86,000 First Quarter S 780,000 S 260,000 S 208,000 S 52,000 second Quarter $ 104,812,500 S 349,375 S 105,092,000 S 69,875 Third Quarter S 877,500 S 292,500 S 1,111,500 $ 58,500 Fourth Quarter S 463,125 $ 154,375 S 586,625 Total Payments:- $ 106,933,125 $ 1,056,250 $ 294,000 | $ 105,144,000 | $ 1,181,375 $ 645,125 Cash Sales 75% Credit Sales 25% Collection of Credit sales in the quarter of the sale 80% Collection of Credit sales in the following quarter 20% Schedule of expected payments for Purchases Payments by Quarter 2023 Purchases Otr 1 Qtr 2 Qtr 3 Qtr 4 Qtr 1 Accounts Payable - 31st Dec 2021 S 510,000 $ 153,000 First Quarter S 350,875 $ 245,613 105,263 Second Quarter S 734,125 S 513,888 S 220,238 Third Quarter S 585,375 S 409,763 $ 175,613 ourth Quarter S 632,625 $ 442,838 otal Payments:- $ 2,813,000 S 398,613 $ 619,150 $ 630,000 $ 618,450 Purchases paid in cash in the same quarter 70% Purchases paid in the following quarter 30%CASH BUDGET for the year ending 31st December 2022 1 2 3 4 Year Beginning cash balance $ 10,000 Add: Receipts Collections from Customers $ 1,040,000 $ 1,397,500 $ 1,170,000 $ 617,500 Total receipts: Total available cash: $ 1,050,000 Less: Disbursements Bike Purchases 398,613 619,150 $ 630,000 | $ 618,450 Selling and Administrative expenses S 56.437 89,275 74,742 39,447 Purchase of new store 500,000 Income Tax expense Dividends Total disbursements: S 84,950 Excess (deficiency) of available cash over cash disbursements Financing Previous period borrowings Interest Costs Excess (deficiency) after interest payment Amount available to pay down loan without considering $10,000 minumum Add: Current period borrowings ess: Repayments considering $10,000 minimum balance Ending cash balance Notes: Minimum cash balance: $10,000 Interest on loan to be paid in each quarter based on the loan balance of the previous quarter. Taxation amount taken from the Income Statement 75% cashBike 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.00Selling 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 S 20,800 S 27,950 | $ 23,400 S 12,350 S 84,500 Total Variable Costs: S 20,800 S 27,950 S 23,400 S 12,350 S 84,500 8 Fixed Costs: Accounting & professional services S 443 S 595 S 498 S 263 1,800 Administrative salaries 18,954 S 25,469 S 21,323 S 11,254 77,000 Sales Wages - fixed portion S 7,385 9.923 S 8,308 S 4,385 30,001 Advertising 2,215 2,977 S 2,492 1,315 9,000 Computer costs S 1,034 S 1,389 S 1,163 S 614 4,200 Depreciation 8,615 11,577 9,692 S 5,115 35,000 Office Supplies 566 S 761 S 637 S 336 2,300 Printing 345 463 S 388 S 205 1,400 Insurance 295 397 S 332 5 175 1,200 Property taxes 123 165 S 138 73 500 Rent S 4,923 6,615 S 5,538 2,923 20,000 Utilities S 738 992 S 831 438 3,000 Total Fixed Costs: 45,637 S 61,325 S 51,342 S 27,097 S 185,401 Total costs: S 66,437 S 89,275 S 74,742 S 39,447 269,901 Cash Total per quarter: S S 57,822 S 77,698 $ 65,049 S 34,332 $ 234,901 Note: Deduct Depreciation.Sales Budget Quarter 1 2 4 TOTAL Expected Sales 3,200 units 4,300 3,600 1,900 13,000 Selling Price S 325 325 325 325 325 Total Sales S 1,040,000 $ 1,397,500 $ 1,170,000 617,500 S 4,225,000