2 3 4 Year Beginning cash balance Add: Receipts Collections from Customers Total receipts: Total available cash: Less: Disbursements Bike Purchases Selling and Administrative expenses Purchase of new store Income Tax expense Dividends Total disbursements: 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 Less: 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 StatementCVP Budgeted Income Statement for year ended 315t December 2022 Sales @vmable Costs Cost of Sales Selling & Administration variable costs Total Contribution Margin Fixed costs Selling & Administration xed costs Net operating income _ Interest costs Income Tax 5 181,702 Net Income after tax _ Check result with Income Statement: .3 414,871 Budgeted Income Statement for year ended 315t December 2022 Sales 5,300,000 Cost of sales 3,710,000 Gross prot 1,590,000 Selling and Administration expenses 993,427 Operating Income 596,573 Interest expense 3,900 Net Income before taxation 592,673 Income tax expense 177,802 Net Income after tax 414,871 mmmmmmmmm '{h Analysis of Sales Wages Using the High-low Method: Volume Cost Variable Component (per unit sold) $ 8.00 High volume 12905 174,975 Low volume 8175 137,145 Fixed Component --> total for the year $ 71,762.28 Difference 4730 37,830 Change in cost / Change in activity= 8.00 Y= a + bx Total Cost = 174975 Variable element = 103212.72 Fixed Element = 71762.28 Selling and Administrative Expense Budget Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year Budgeted Sales in units: 2,500 2,400 2,700 3,000 10,600 Variable Cost: Sales Wages $ 19,995 $ 19,195 $ 21,594 $ 23,994 84,778 Total Variable Costs: S 19,995 $ 19,195 $ 21,594 $ 23,994 84,778 Fixed Costs: Accounting & professional services $ 1,800 $ 1,800 $ 1,800 $ 1,800 7,200 Administrative salaries 77,000 $ 77,000 |$ 77,000 $ 77,000 308,000 Sales Wages - fixed portion $ 71,762 $ 71,762 $ 71,762 $ 71,762 287,049 Advertising 9,000 $ 9,000 $ 9,000 $ 9,000 36,000 Computer costs $ 4,200 $ 4,200 $ 4,200 $ 4,200 16,800 Depreciation 35,000 $ 35,000 | $ 35,000 | $ 35,000 140,000 Office Supplies $ 2,300 $ 2,300 $ 2,300 $ 2,300 9,200 Printing $ 1,400 $ 1,400 $ 1,400 $ 1,400 5,600 Insurance 1,200 $ 1,200 $ 1,200 $ 1,200 1,800 Property taxes 500 $ 500 $ 500 $ 500 2,000 Rent 20,000 $ 20,000 20,000 $ 20,000 80,000 Utilities 3,000 $ 3,000 3,000 $ 3,000 12,000 Total Fixed Costs: 227,162 $ 227,162 $ 227,162 $ 227,162 908,649 Total costs: 247,157 $ 246,357 $ 248,757 $ 251,156 993,427 Cash Total per quarter: $ $ 212,157 $ 211,357 $ 213,757 $ 216,156 Note: Deduct Depreciation.2 3 Cost of Sales S 4 Purchases during the year un 3,622,500 5 Beginning Bike Inventory 787,500 6 Less: Ending Bike Inventory 700,000 7 Cost of Goods Sold S 3,710,000 8 10 11 12 13 14 15 16 17 18 19Expected Sales Units 2,500 2,400 2,700 m Add desired ending of Inventory 2,160 2,430 2,700 2,000 Total needs \"mm Less beginning of Inventory Sales Budget Quarter _- TOTAL Expected Sales units 2, 500 2,400 2,700 3,000 10,600 Selling Price Total Sales 5 1,250,000 5 1,200,000 $ 1,350,000 $ 1,500,000 5 5,300,000 ACCT 7004 - Group Project 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 foreach; . Sales budget for each quarter including totals for the year . Purchasing Budget for each quarter including totals for the year . Cost of Goods Sold Schedule for the year . 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 mmmwal Requirement B: Your group has been requested to complete the following GVP analysis for fiscal 2022. Use the CVP Analysis Tab to complete this analysis. Ignore interest expense forthis section. . What is the breakeven point in units sold? What is the breakeven point in sales dollars? . How many units need to be sold to earn an aftertax profit of $ 500,000 .Assume a tax rate of 1 2 3. What is the margin of safety in $? 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 levera- e to answer this. h5.00% 5. If CBH spend $ 50,000 on an advertising campaign, it believes sales (units) will increase by without any change in average selling price. Should it take on the advertising? Why or why not? Submission Information: Once the project is complete submit this file {one per group) in the appropriate submission folder on FOL. Due date for this project is November 27, 2022 Additional details: Below you will see vellow 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 A There are B bicycles in Bike Inventory at the end of 2021 with a value of $ C At the end of each quarter, CBH Company wishes to have D I of next quarters estimated sales units in Bike Inventory. At the end of 2022 assume 2,000 bikes are required in ending inventory 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 Q4 2021 were $430.000 . Assume operating expenses occur evenly throughout the year and are all paid in cash. For each quarter. 70% _of Bike purchases are paid for in cash in the quarter of the purchase and 0% 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 E CBH Company will pay $60,000 in dividends in Q4 2022. . 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 F Q2 G Q3 H Q4 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 2020 12905 2019 11385 2018 8175 M 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 55400Total 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,000