Background Information:

Question: Prepare a worksheet for cash collections and disbursements

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. Assume a tax rate of 30% 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 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 $300 - There are 1,800 bicycles in Bike Inventory at the end of 2021 with a value of $297,000.00 At the end of each quarter, CBH Company wishes to have 20% of next quarters estimated sales - For each quarter, it is estimated that r 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. CreditsalesfromQ42021were$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 $165 - 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. 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. - 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 The loan balance at the end of 2021 was \begin{tabular}{|rr|} \hline & 5.00% \\ \hline$ & 78,000 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline 3 & \multicolumn{3}{|l|}{ Schedule of receipts from customers } & & & & & \\ \hline 4 & & & & \multicolumn{4}{|c|}{ Receipts by Quarter } & 2023 \\ \hline 5 & & Cash Sales & Credit Sales & Qtr 1 & Qtr 2 & Qtr 3 & Qtr 4 & Qtr 1 \\ \hline 6 & Accounts Receivable - 31st Dec 2021 & & & & & & & \\ \hline 7 & First Quarter & & & & & & & \\ \hline 8 & Second Quarter & & & & & & & \\ \hline 9 & Third Quarter & & & & & & & \\ \hline 10 & Fourth Quarter & & & & & & & \\ \hline 11 & Total Payments:- & & & & & & & \\ \hline \multicolumn{9}{|l|}{12} \\ \hline 13 & Cash Sales & & 75% & & & & & \\ \hline 14 & Credit Sales & & 25% & & & & & \\ \hline 15 & \multirow{3}{*}{\multicolumn{2}{|c|}{\begin{tabular}{l} Collection of Credit sales in the quarter of the sale \\ Collection of Credit sales in the following quarter \end{tabular}}} & 80% & & & & & \\ \hline 16 & & & 20% & & & & & \\ \hline 17 & & & & & & & & \\ \hline 18 & \multicolumn{2}{|c|}{ Schedule of expected payments for Purchases } & & & & & & \\ \hline 19 & & & & \multicolumn{4}{|c|}{ Payments by Quarter } & 2023 \\ \hline 20 & & & Purchases & Qtr 1 & Qtr 2 & Qtr 3 & Qtr 4 & Qtr 1 \\ \hline 21 & Accounts Payable - 31st Dec 2021 & & & & & & & \\ \hline 22 & First Quarter & & & & & & & \\ \hline 23 & Second Quarter & & & & & & & \\ \hline 24 & Third Quarter & & & & & & & \\ \hline 25 & Fourth Quarter & & & & & & & \\ \hline 26 & Total Payments:- & & & & & & & \\ \hline 27 & & & & & & & & \\ \hline 28 & Purchases paid in cash in the same quarter & & & 70% & & & & \\ \hline 29 & Purchases paid in the following quarter & & & 30% & & & & \\ \hline \end{tabular}