90.2K

Verified Solution

Link Copied!

You need to prepare a Master Budget for the The company has an exclusive right to sell Mighty Modules and sales have been brisk. The Master Budget will be for the next three months starting April 1. The following information is available related to the budget. The company needs to maintain a minimum cash balance at the end of every month in the amount of $15,000. The Modules are forecasted to sell at $30 each. Recent actual and projected sales in units) are as follows Actual Jan Feb Mar 60,000 72,000 84,000 Projected Apr 105,000 135,000 Jun 180,000 Projected Jul 120,000 Aug 108,000 96,000 May Sep In order to meet the product demand, the company has established a policy requiring that ending inventory for each month must be equal to 90% of the units expected to sold in the next month. The cost to purchase each unit of product is $18. Purchases are typically paid for as follows: 50% paid in the month of purchase, and the remaining 50% paid in the month after purchase. All sales are on credit, with no discount, and payable within 15 days. The company's collections on account usually are 25% in the month of sale, 50% in the month immediately after the sale, and 25% in the second month after sale. The company has a very rigorous credit policy and there are virtually no bad debts. The company's operating expenses are shown below: Variable: Sales Commissions $3 per unit Fixed: Wages Utilities Insurance expired Depreciation Miscellaneous $49,000 1,700 1,600 2,100 2,700 All operating expenses are paid during the month, in cash, with the exception of depreciation and insurance expired. New fixed assets will be purchased during May for $30,000. The company declares dividends of $16,000 each quarter, payable in the first month of the following quarter. Page 1 s Balance Sheet at March 31 is as follows. ASSETS Cash $14,000 Accounts receivable* 2,430,000 Inventory (94500 units)** 1,701,000 Unexpired insurance 19,200 Fixed assets (net of depreciation) 193,600 Total Assets $4,357,800 LIABILITIES AND EQUITY Accounts payable (purchases) Dividends payable Capital stock, (no par) Retained Earnings Total Liabilities & Equity $926, 100 16,000 400,000 3,015,700 $4,357,800 *Accounts receivable consists of $540,000 from February sales and $1,890,000 from March Sales. Use these numbers for both scenarios. ** Use this same March ending inventory number for both scenarios. The company has a good relationship with its bank and can borrow money at a 10% annual rate at any time and in any amount. All borrowing and repayments must be made at the end of the month. When the company is ready to make a payment, all unpaid interest must be paid first. After the unpaid interest is paid, then principal can be repaid as long as the minimum cash balance is maintained. You will complete all tasks listed below for the original facts above...this will be Scenario 1. Then you will repeat the entire process for Scenario 2. This second scenario will show what would happen if there was an increase of 20% (twenty percent) in the number of units sold. This is essentially a flexible budget. SCENARIO 1 Prepare a Master Budget for the three month period ending June 30th. Include the following detailed budgets: 1. a. A sales budget by month and in total. b. A schedule of budgeted cash collections from sales and accounts receivable by month and in total. c. A purchases budget in units and dollars by month and in total. d. A schedule of budgeted cash payments for purchases by month and in total. 2. A cash budget by month and in total. 3. A budgeted income statement for the three-month period ending June 30. Use the contribution margin approach. 4. A budgeted balance sheet as of June 30. 5. Calculate the Contribution Margin and Break-Even amounts (for the three month period) based on your assumptions about variable and fixed costs. SCENARIO 2 Repeat all the steps (1-5) shown above assuming that the number of units expected to be sold increase by 20%. The months January to March have already occurred so those will be the same for both Scenarios. Please pay attention to the information above when it says: *Accounts receivable consists of $540,000 from February sales and $1,890,000 from March Sales. Use these numbers for both scenarios. ** Use this same March ending inventory number for both scenarios. Budgeted Ending Inventory for June is based on July sales. Therefore you will need to increase the expected July sales in Scenario 2 and this will mean June Ending Inventory will be different in Scenario 2. Here are some check figures to check your final work. If you agree with these check numbers it is an important confirmation, although it is not guarantee that everything is correct. Amounts for the quarter: Scenario 1 Scenario 2 Sales budget $12,600,000 $15,120,000 Budgeted cash collections $9,967,500 $11,475,000 Budgeted purchases $7,803,000 $9,703,800 Budgeted cash payments-purchases $7,595,100 $9,269,100 Ending Cash Balance $920,200 $495,234 Inc Stmt Interest Expense $6,466 Inc Stmt Net income $3,608,700 $4,358,234 Bal Sheet AR $5,062,500 $6,075,000 Bal Sheet Inventory $1,944,000 $2,332,800 Bal Sheet AP $1,134,000 $1,360,800 Bal Sheet Retained Earnings (RE) $6,608,400 $7,357,934 Bal Sheet Total Assets (=Liab+OE) $8,158,400 $9,134,734 $0 Scenario 1 Scenario 2 Scenario Scenario 2 Amounts for the quarter: Sales budget Budgeted cash collections Budgeted purchases Budgeted cash payments purchases Ending Cash Balance Inc Stmt Interest Expense Ins Stmt Net income Bal Sheet AR Bal Sheet Inventory Bal Sheet AP Bal Sheet Retained Earnin Bal Sheet Total Assets (Liab+OE) SCENARIO 1 Name of your Company Sales budget For the Three Months Ending June 30, 202X April May June Quarter Budgeted sales (units) Budgeted sales price/unit Budgeted sales price/dollars SCENARIO 1 Name of your Company Budgeted cash collections For the Three Months Ending June 30, 202X April May June Quarter February sales March sales April sales May sales June sales Total cash collections SCENARIO 1 Name of your Company Budgeted purchases For the Three Months Ending June 30, 202X April May June Quarter Budgeted sales (units) Add budgeted ending inventory Total needs Less beginning inventory Required purchases in units Unit cost Required purchases in dollars "Budgeted ending inventory at 90% of next months sales in units. SCENARIO 1 Name of your Company Budgeted cash payments for purchases For the Three Months Ending June 30, 202X April May June Quarter March purchases April purchases May purchases June purchases Total cash payments SCENARIO 1 Name of your Company Cash Budget For the Three Months Ending June 30, 202X April May June Quarter Cash balance beginning of month Add cash from customers Total cash available Less cash payments: Purchase of Inventory Sales commissions Salaries and wages Utilities Misc Dividends paid Equipment Purchases Total cash paid Excess (deficiency) of available over pets Financing Borrowing (Repayment) Interest Total financing Cash balance end of month SCENARIO 1 Name of your Company Budgeted Income Statement For the Three Months Ending June 30, 202X Sales in units (Memo) per unit Sales dollars Less variable expenses Cost of Goods sold at S_ Commissions $_ per unit Contribution Margin Less fixed expenses Wages Utilities Insurance expired Depreciation Miscellaneous Net operating income Less interest expense" Net income "MEMO: interest expense calculation: MEMO: To help you calculate interest and round the amount to the nearest dollar, formulas are provided in the yellow cells. ENTER AMOUNTS FROM CASH BUDGET HERE (any) Interest expense on amount borrowed in 2nd month: total amount borrowed in 2nd month 10.00% CHANGE YOUR RATE HERE if different 0 12 rate per year interest for 12 months $ number of months in a year interest for 1 month $ months outstanding total interest expense for this amount 0 1 0 0 MEMO: note rounding formula in the column G formula ENTER AMOUNTS FROM CASH BUDGET HERE (if any) CHANGE YOUR RATE HERE if different 10.00% 0 Interest expense on amount borrowed in 1st month: total amount borrowed in 1st month rate per year interest for 12 months $ number of months in a year interest for 1 month $ months outstanding total interest expense for this amount total interest expense for both amounts 12 0 2 0 0 MEMO: note rounding formula in the column G formula 0 SCENARIO 1 Name of your Company Budgeted Balance Sheet June 30, 202x Cash AR Inventory_units at $ per unit Unexpired insurance Fixed assets, not Total Assets Liabilities & Equity Interest Payable Dividends payable Notes payable-bank Capital Stock, no par Retained Earnings Total Liabilities & Equity MEMO Balance Sheet detail: Accounts Receivable (AR) May Sales June Sales AR Beginning +Sales 0 1 Collections AR Ending Retained Earnings (RE) Beg RE Net income Less Dividends declared Contribution Margin and BEP Unit Sales price Unit Purchase price Unit Sales Commissions Unit Contribution Margin Fixed Costs for three month period Wages Utilities Insurance expired Depreciation Miscellaneous Total Fixed Costs Break Even Point: Fixed Costs/Unit CM BEP units Fixed Costs/CM% BEP dollars SCENARIO 2 (Increase sales units by 20%) Name of your Company Sales budget For the Three Months Ending June 30, 202X April May June Quarter Budgeted sales (units) Budgeted sales price/unit Budgeted sales price/dollars SCENARIO 2 (Increase sales units by 20%) Name of your Company Budgeted cash collections For the Three Months Ending June 30, 202X April May June Quarter February sales March sales April sales May sales June sales Total cash collections SCENARIO 2 Increase sales units by 20%) Name of your Company Budgeted purchases For the Three Months Ending June 30, 202X April May June Quarter Budgeted sales (units) Add budgeted ending inventory Total needs Less beginning inventory Required purchases in units Unit cost Required purchases in dollars beginning AP "Budgeted ending inventory at 90% of next months sales in units. SCENARIO 2 (Increase sales units by 20%) Name of your Company Budgeted cash payments for purchases For the Three Months Ending June 30, 202X April May June Quarter March purchases April purchases May purchases June purchases Total cash payments SCENARIO 2 (Increase sales units by 20%) Name of your Company Cash Budget For the Three Months Ending June 30, 202X April May June Quarter Cash balance beginning of month Add cash from customers Total cash available Less cash payments: Purchase of Inventory Sales commissions Salaries and wages Utilities Misc Dividends paid Equipment Purchases Total cash paid Excess (deficiency) of available over pets Financing Borrowing (Repayment) Interest Total financing Cash balance end of month SCENARIO 2 (Increase sales units by 20%) Name of your Company Budgeted Income Statement For the Three Months Ending June 30, 202X Sales in units (Memo) Sales dollars Less variable expenses Cost of Goods sold at S per unit Commissions $_per unit Contribution Margin Less fixed expenses Wages Utilities Insurance expired Depreciation Miscellaneous Net operating income Less interest expense" Net income = "MEMO: interest expense calculation: MEMO: To help you calculate interest and round the amount to the nearest dollar, formulas are provided in the yellow cells. ENTER AMOUNTS FROM CASH BUDGET HERE (any) CHANGE YOUR RATE HERE if different 10.00% 0 Interest expense an amount borrowed in 2nd month: total amount borrowed in 2nd month rate per year interest for 12 months $ number of months in a year interest for 1 month $ months outstanding total interest expense for this amount 12 0 1 0 0 MEMO: note rounding formula in the column G formula ENTER AMOUNTS FROM CASH BUDGET HERE (any) CHANGE YOUR RATE HERE if different 10.00% 0 Interest expense on amount borrowed in 1st month: total amount borrowed in 1st month rate per year interest for 12 months $ number of months in a year interest for 1 month $ months outstanding total interest expense for this amount total interest expense for both amounts 12 0 2 0 0 MEMO: note rounding formula in the column G formula 0 SCENARIO 2 (Increase sales units by 20%) Name of your Company Budgeted Balance Sheet June 30, 202x Cash AR per unit Inventory units at $ Unexpired insurance Fixed assets, not Total Assets - Liabilities & Equity Interest Payable Dividends payable Notes payable-bank Capital Stock, no par Retained Earnings Total Liabilities & Equity MEMO: Balance Sheet detail: Accounts Receivable (AR) May Sales June Sales 0 AR Beginning +Sales + Collections 1 AR Ending Retained Earnings (RE) Beg RE Net income Less Dividends declared Contribution Margin and BEP Unit Sales price Unit Purchase price Unit Sales Commissions Unit Contribution Margin Fixed Costs for three month period Wages Utilities Insurance expired Depreciation Miscellaneous Total Fixed Costs Break Even Point: Fixed Costs/Unit CM BEP units Fixed Costs/CM% BEP dollars You need to prepare a Master Budget for the The company has an exclusive right to sell Mighty Modules and sales have been brisk. The Master Budget will be for the next three months starting April 1. The following information is available related to the budget. The company needs to maintain a minimum cash balance at the end of every month in the amount of $15,000. The Modules are forecasted to sell at $30 each. Recent actual and projected sales in units) are as follows Actual Jan Feb Mar 60,000 72,000 84,000 Projected Apr 105,000 135,000 Jun 180,000 Projected Jul 120,000 Aug 108,000 96,000 May Sep In order to meet the product demand, the company has established a policy requiring that ending inventory for each month must be equal to 90% of the units expected to sold in the next month. The cost to purchase each unit of product is $18. Purchases are typically paid for as follows: 50% paid in the month of purchase, and the remaining 50% paid in the month after purchase. All sales are on credit, with no discount, and payable within 15 days. The company's collections on account usually are 25% in the month of sale, 50% in the month immediately after the sale, and 25% in the second month after sale. The company has a very rigorous credit policy and there are virtually no bad debts. The company's operating expenses are shown below: Variable: Sales Commissions $3 per unit Fixed: Wages Utilities Insurance expired Depreciation Miscellaneous $49,000 1,700 1,600 2,100 2,700 All operating expenses are paid during the month, in cash, with the exception of depreciation and insurance expired. New fixed assets will be purchased during May for $30,000. The company declares dividends of $16,000 each quarter, payable in the first month of the following quarter. Page 1 s Balance Sheet at March 31 is as follows. ASSETS Cash $14,000 Accounts receivable* 2,430,000 Inventory (94500 units)** 1,701,000 Unexpired insurance 19,200 Fixed assets (net of depreciation) 193,600 Total Assets $4,357,800 LIABILITIES AND EQUITY Accounts payable (purchases) Dividends payable Capital stock, (no par) Retained Earnings Total Liabilities & Equity $926, 100 16,000 400,000 3,015,700 $4,357,800 *Accounts receivable consists of $540,000 from February sales and $1,890,000 from March Sales. Use these numbers for both scenarios. ** Use this same March ending inventory number for both scenarios. The company has a good relationship with its bank and can borrow money at a 10% annual rate at any time and in any amount. All borrowing and repayments must be made at the end of the month. When the company is ready to make a payment, all unpaid interest must be paid first. After the unpaid interest is paid, then principal can be repaid as long as the minimum cash balance is maintained. You will complete all tasks listed below for the original facts above...this will be Scenario 1. Then you will repeat the entire process for Scenario 2. This second scenario will show what would happen if there was an increase of 20% (twenty percent) in the number of units sold. This is essentially a flexible budget. SCENARIO 1 Prepare a Master Budget for the three month period ending June 30th. Include the following detailed budgets: 1. a. A sales budget by month and in total. b. A schedule of budgeted cash collections from sales and accounts receivable by month and in total. c. A purchases budget in units and dollars by month and in total. d. A schedule of budgeted cash payments for purchases by month and in total. 2. A cash budget by month and in total. 3. A budgeted income statement for the three-month period ending June 30. Use the contribution margin approach. 4. A budgeted balance sheet as of June 30. 5. Calculate the Contribution Margin and Break-Even amounts (for the three month period) based on your assumptions about variable and fixed costs. SCENARIO 2 Repeat all the steps (1-5) shown above assuming that the number of units expected to be sold increase by 20%. The months January to March have already occurred so those will be the same for both Scenarios. Please pay attention to the information above when it says: *Accounts receivable consists of $540,000 from February sales and $1,890,000 from March Sales. Use these numbers for both scenarios. ** Use this same March ending inventory number for both scenarios. Budgeted Ending Inventory for June is based on July sales. Therefore you will need to increase the expected July sales in Scenario 2 and this will mean June Ending Inventory will be different in Scenario 2. Here are some check figures to check your final work. If you agree with these check numbers it is an important confirmation, although it is not guarantee that everything is correct. Amounts for the quarter: Scenario 1 Scenario 2 Sales budget $12,600,000 $15,120,000 Budgeted cash collections $9,967,500 $11,475,000 Budgeted purchases $7,803,000 $9,703,800 Budgeted cash payments-purchases $7,595,100 $9,269,100 Ending Cash Balance $920,200 $495,234 Inc Stmt Interest Expense $6,466 Inc Stmt Net income $3,608,700 $4,358,234 Bal Sheet AR $5,062,500 $6,075,000 Bal Sheet Inventory $1,944,000 $2,332,800 Bal Sheet AP $1,134,000 $1,360,800 Bal Sheet Retained Earnings (RE) $6,608,400 $7,357,934 Bal Sheet Total Assets (=Liab+OE) $8,158,400 $9,134,734 $0 Scenario 1 Scenario 2 Scenario Scenario 2 Amounts for the quarter: Sales budget Budgeted cash collections Budgeted purchases Budgeted cash payments purchases Ending Cash Balance Inc Stmt Interest Expense Ins Stmt Net income Bal Sheet AR Bal Sheet Inventory Bal Sheet AP Bal Sheet Retained Earnin Bal Sheet Total Assets (Liab+OE) SCENARIO 1 Name of your Company Sales budget For the Three Months Ending June 30, 202X April May June Quarter Budgeted sales (units) Budgeted sales price/unit Budgeted sales price/dollars SCENARIO 1 Name of your Company Budgeted cash collections For the Three Months Ending June 30, 202X April May June Quarter February sales March sales April sales May sales June sales Total cash collections SCENARIO 1 Name of your Company Budgeted purchases For the Three Months Ending June 30, 202X April May June Quarter Budgeted sales (units) Add budgeted ending inventory Total needs Less beginning inventory Required purchases in units Unit cost Required purchases in dollars "Budgeted ending inventory at 90% of next months sales in units. SCENARIO 1 Name of your Company Budgeted cash payments for purchases For the Three Months Ending June 30, 202X April May June Quarter March purchases April purchases May purchases June purchases Total cash payments SCENARIO 1 Name of your Company Cash Budget For the Three Months Ending June 30, 202X April May June Quarter Cash balance beginning of month Add cash from customers Total cash available Less cash payments: Purchase of Inventory Sales commissions Salaries and wages Utilities Misc Dividends paid Equipment Purchases Total cash paid Excess (deficiency) of available over pets Financing Borrowing (Repayment) Interest Total financing Cash balance end of month SCENARIO 1 Name of your Company Budgeted Income Statement For the Three Months Ending June 30, 202X Sales in units (Memo) per unit Sales dollars Less variable expenses Cost of Goods sold at S_ Commissions $_ per unit Contribution Margin Less fixed expenses Wages Utilities Insurance expired Depreciation Miscellaneous Net operating income Less interest expense" Net income "MEMO: interest expense calculation: MEMO: To help you calculate interest and round the amount to the nearest dollar, formulas are provided in the yellow cells. ENTER AMOUNTS FROM CASH BUDGET HERE (any) Interest expense on amount borrowed in 2nd month: total amount borrowed in 2nd month 10.00% CHANGE YOUR RATE HERE if different 0 12 rate per year interest for 12 months $ number of months in a year interest for 1 month $ months outstanding total interest expense for this amount 0 1 0 0 MEMO: note rounding formula in the column G formula ENTER AMOUNTS FROM CASH BUDGET HERE (if any) CHANGE YOUR RATE HERE if different 10.00% 0 Interest expense on amount borrowed in 1st month: total amount borrowed in 1st month rate per year interest for 12 months $ number of months in a year interest for 1 month $ months outstanding total interest expense for this amount total interest expense for both amounts 12 0 2 0 0 MEMO: note rounding formula in the column G formula 0 SCENARIO 1 Name of your Company Budgeted Balance Sheet June 30, 202x Cash AR Inventory_units at $ per unit Unexpired insurance Fixed assets, not Total Assets Liabilities & Equity Interest Payable Dividends payable Notes payable-bank Capital Stock, no par Retained Earnings Total Liabilities & Equity MEMO Balance Sheet detail: Accounts Receivable (AR) May Sales June Sales AR Beginning +Sales 0 1 Collections AR Ending Retained Earnings (RE) Beg RE Net income Less Dividends declared Contribution Margin and BEP Unit Sales price Unit Purchase price Unit Sales Commissions Unit Contribution Margin Fixed Costs for three month period Wages Utilities Insurance expired Depreciation Miscellaneous Total Fixed Costs Break Even Point: Fixed Costs/Unit CM BEP units Fixed Costs/CM% BEP dollars SCENARIO 2 (Increase sales units by 20%) Name of your Company Sales budget For the Three Months Ending June 30, 202X April May June Quarter Budgeted sales (units) Budgeted sales price/unit Budgeted sales price/dollars SCENARIO 2 (Increase sales units by 20%) Name of your Company Budgeted cash collections For the Three Months Ending June 30, 202X April May June Quarter February sales March sales April sales May sales June sales Total cash collections SCENARIO 2 Increase sales units by 20%) Name of your Company Budgeted purchases For the Three Months Ending June 30, 202X April May June Quarter Budgeted sales (units) Add budgeted ending inventory Total needs Less beginning inventory Required purchases in units Unit cost Required purchases in dollars beginning AP "Budgeted ending inventory at 90% of next months sales in units. SCENARIO 2 (Increase sales units by 20%) Name of your Company Budgeted cash payments for purchases For the Three Months Ending June 30, 202X April May June Quarter March purchases April purchases May purchases June purchases Total cash payments SCENARIO 2 (Increase sales units by 20%) Name of your Company Cash Budget For the Three Months Ending June 30, 202X April May June Quarter Cash balance beginning of month Add cash from customers Total cash available Less cash payments: Purchase of Inventory Sales commissions Salaries and wages Utilities Misc Dividends paid Equipment Purchases Total cash paid Excess (deficiency) of available over pets Financing Borrowing (Repayment) Interest Total financing Cash balance end of month SCENARIO 2 (Increase sales units by 20%) Name of your Company Budgeted Income Statement For the Three Months Ending June 30, 202X Sales in units (Memo) Sales dollars Less variable expenses Cost of Goods sold at S per unit Commissions $_per unit Contribution Margin Less fixed expenses Wages Utilities Insurance expired Depreciation Miscellaneous Net operating income Less interest expense" Net income = "MEMO: interest expense calculation: MEMO: To help you calculate interest and round the amount to the nearest dollar, formulas are provided in the yellow cells. ENTER AMOUNTS FROM CASH BUDGET HERE (any) CHANGE YOUR RATE HERE if different 10.00% 0 Interest expense an amount borrowed in 2nd month: total amount borrowed in 2nd month rate per year interest for 12 months $ number of months in a year interest for 1 month $ months outstanding total interest expense for this amount 12 0 1 0 0 MEMO: note rounding formula in the column G formula ENTER AMOUNTS FROM CASH BUDGET HERE (any) CHANGE YOUR RATE HERE if different 10.00% 0 Interest expense on amount borrowed in 1st month: total amount borrowed in 1st month rate per year interest for 12 months $ number of months in a year interest for 1 month $ months outstanding total interest expense for this amount total interest expense for both amounts 12 0 2 0 0 MEMO: note rounding formula in the column G formula 0 SCENARIO 2 (Increase sales units by 20%) Name of your Company Budgeted Balance Sheet June 30, 202x Cash AR per unit Inventory units at $ Unexpired insurance Fixed assets, not Total Assets - Liabilities & Equity Interest Payable Dividends payable Notes payable-bank Capital Stock, no par Retained Earnings Total Liabilities & Equity MEMO: Balance Sheet detail: Accounts Receivable (AR) May Sales June Sales 0 AR Beginning +Sales + Collections 1 AR Ending Retained Earnings (RE) Beg RE Net income Less Dividends declared Contribution Margin and BEP Unit Sales price Unit Purchase price Unit Sales Commissions Unit Contribution Margin Fixed Costs for three month period Wages Utilities Insurance expired Depreciation Miscellaneous Total Fixed Costs Break Even Point: Fixed Costs/Unit CM BEP units Fixed Costs/CM% BEP dollars

Answer & Explanation Solved by verified expert

See Answer