Help finish the CVP income statement

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 increas 5.00% without any change in average selling price. Should it take on the advertising? Why or why not? 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 $525 There are 1,365 bicycles in Bike Inventory at the end of 2021 with a value of $ 280,000 At the end of each quarter, CBH Company wishes to have 65% of next quarters estimated sales units in Bike Inventory. At the end of 2022 assum 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 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 $ 325 CBH Company will pay $60,000 in dividends in Q4 2022. The cash balance in the bank at the end of 2021 i $ 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 2100 Q2 2400 Q3 Q4 3200 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. 4200 Here are the total sales in units and the total sales wages expense for the last 4 years. Units sold $ 2021 12585 155130 2020 12905 169975 2019 11385 175225 2018 8175 137145 $ 1800 77000 9000 4200 35000 Fixed Costs: Accounting & professional services Administrative Salaries Advertising Computer costs Depreciation Office Supplies Printing Insurance Property taxes Rent Utilities Total Fixed Costs 2300 1400 1200 500 20000 3000 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 $ CVP Budgeted Income Statement for year ended 31st December 2022 $ Sales Less: Variable Costs Cost of Sales Selling & Administration variable costs Total Contribution Margin Less: Fixed costs Selling & Administration fixed costs Net operating income Interest costs Income Tax Net Income after tax Check result with Income Statement: 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 increas 5.00% without any change in average selling price. Should it take on the advertising? Why or why not? 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 $525 There are 1,365 bicycles in Bike Inventory at the end of 2021 with a value of $ 280,000 At the end of each quarter, CBH Company wishes to have 65% of next quarters estimated sales units in Bike Inventory. At the end of 2022 assum 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 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 $ 325 CBH Company will pay $60,000 in dividends in Q4 2022. The cash balance in the bank at the end of 2021 i $ 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 2100 Q2 2400 Q3 Q4 3200 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. 4200 Here are the total sales in units and the total sales wages expense for the last 4 years. Units sold $ 2021 12585 155130 2020 12905 169975 2019 11385 175225 2018 8175 137145 $ 1800 77000 9000 4200 35000 Fixed Costs: Accounting & professional services Administrative Salaries Advertising Computer costs Depreciation Office Supplies Printing Insurance Property taxes Rent Utilities Total Fixed Costs 2300 1400 1200 500 20000 3000 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 $ CVP Budgeted Income Statement for year ended 31st December 2022 $ Sales Less: Variable Costs Cost of Sales Selling & Administration variable costs Total Contribution Margin Less: Fixed costs Selling & Administration fixed costs Net operating income Interest costs Income Tax Net Income after tax Check result with Income Statement