accounting You have been engaged as a consultant to design a master budget model and then to assist Helping Hand Corp in making some management decisions based on that master budget Helping Hand is a small, rapidly growing wholesaler of consumer electronic products The companys main product lines are small kitchen appliances and power tools The marketing manager has recently completed a sales forecast She believes the companys sales will increase by 2 percent each month over the previous months sales from December 2019 through March 2020 Then sales are expected to remain constant for several months Helping Hands projected balance sheet as of December 31, 2019 is as follows Cash Accounts receivable Marketable securities Inventory Buildings and equipment (net of accumulated depreciation) Total assets Accounts payable Sales commissions payable Bond interest payable Property taxes payable Bonds payable (3 due in 2023) Common stock Retained earnings Total liabilities and stockholders' equity $ $ $ $ 140,000 202,851 55,000 35,894 610,000 1,043,745 124,488 7,038 5,000 0 500,000 250,000 157,219 1,043,745 The following information has been accumulated to assist with preparing the master budget for the first quarter of 2020 1) Projected sales for November 2019 are $230,000 Credit sales are typically 85 of total sales Helping Hands credit experience indicates that 12 of credit sales are collected during the month of sale, 74 in the month following the sale, and 14 in the second month following the sale Experience shows the remaining credit sales are uncollectible 2) Helping Hands cost of goods sold generally runs at 60 of sales Inventory is purchased on account and 12 of each months purchases are paid during the month of purchase The remainder is paid during the following month In order to have adequate stocks of inventory on hand, the company attempts to have inventory on hand at the end of each month equal to 25 of the next months projected cost of goods sold 3) The controller has estimated that Helping Hands other monthly expenses will be as follows Sales salaries Advertising and promotion Administrative salaries Depreciation Interest on bonds Property taxes $ 30,000 6,000 11,000 8,000 1,250 4,000 In addition, sales commissions run at the rate of 3 0 percent of sales Sales commissions are paid in the month following the month of sale 4) The company president has indicated that the company should invest $200,000 in an automated inventoryhandling system to control the movement of inventory in the companys warehouse just after the new year begins The president would like to purchase the equipment primarily from the companys cash and marketable securities However, the president believes the company should have a minimum cash balance of $20,000 at the end of each month If necessary, the remainder of the equipment purchase may be financed using shortterm credit from a local bank The minimum lending period for such a loan is three months (this means the earliest the loan can be paid off is March 31st) The current shortterm interest rates are 6 percent per year and are expected to remain at this rate through the time the equipment is purchased If a loan is necessary, the entire amount required for the quarter must be borrowed on January 1st and must be in a $1,000 increment The loan is a short term loan and the president has decided it should be paid off at the end of the first quarter if possible If the entire amount cannot be repaid at March 31st, any partial payment will be paid at the end of the first quarter and in a $1,000 increment 5) Helping Hands board of directors has indicated an intention to declare and pay dividends of $100,000 on the last day of each quarter 6) The interest on any shortterm borrowing will be paid when the loan is repaid Interest on Helping Hands bonds is paid semiannually on February 28 and August 31 for the preceding six month period 7) Property taxes are paid quarterly on March 31, June 30, September 30, and December 31 for the preceding threemonth period Required Build a model to forecast Helping Hand Corps cash balance at March 31, 2020 Your model must contain the following master budget schedules Round all amounts to the nearest dollar Your model should allow you to change any of the assumptions provided above and easily recalculate the ending cash balance at March 31, 2020 The assumptions may be on a separate worksheet but all of the schedules below must be on one worksheet 1) Sales budget Total sales Cash sales Sales on account 2019 2020 February November December January March 2020 1st Quarter 2) Cash receipts budget January February March 1st Quarter Cash sales Cash collections from credit sales made during current month Cash collections from credit sales made during preceding month Cash collections from credit sales made during 2nd preceding month Total cash receipts 3) Purchases budget Budgeted cost of goods sold Add Desired ending inventory Total goods needed Less Expected beginning inventory Purchases 2019 December 2020 January February March 1st Quarter 4) Cash disbursements budget Inventory purchases Cash payments for purchases during the current month Cash payments for purchases during the preceding month Total cash payments for inventory purchases Other expenses Sales salaries Advertising and promotion Administrative salaries Interest on bonds Property taxes Sales commissions Total cash payments for other expenses Total cash disbursements 2020 January February March 1st Quarter 5) Summary cash budget Cash receipts (sch 2) Less Cash disbursements (sch 4) Change in cash balance during period due to operations Sale of marketable securities (1 2 20) Proceeds from bank loan (1 2 20) Purchase of equipment Repayment of bank loan (3 31 20) Interest on bank loan Payment of dividends Change in cash balance during the month Beginning cash balance Ending cash balance January February 2020 March 1st Quarter 6) Prepare a memo to the president of Helping Hands Corp with at least two recommendations on how the company can ensure it completes the first quarter of 2020 with the minimum required cash balance You should provide a plan to support your recommendation For example, if you recommend an increase in sales, how can this be attained Be specific You should provide specific financial information for your recommendations utilizing your model (include a model for each of your recommendations) For example, if the company does X, the change in ending cash will be Y Your model will become the property of Helping Hands Corp and should be easy to use Not buying the equipment is not an option All assumptions are based on the purchase of the new equipment

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Oct 03, 2024

accounting You have been engaged as a consultant to design a master budget model and then to assist Helping Hand Corp. in making some management

accounting

You have been engaged as a consultant to design a master budget model and then to assist Helping Hand Corp. in making some management decisions based on that master budget.

Helping Hand is a small, rapidly growing wholesaler of consumer electronic products. The companys main product lines are small kitchen appliances and power tools. The marketing manager has recently completed a sales forecast. She believes the companys sales will increase by 2 percent each month over the previous months sales from December 2019 through March 2020. Then sales are expected to remain constant for several months. Helping Hands projected balance sheet as of December 31, 2019 is as follows:

Cash Accounts receivable Marketable securities Inventory Buildings and equipment (net of accumulated depreciation) Total assets

Accounts payable Sales commissions payable Bond interest payable Property taxes payable Bonds payable (3%; due in 2023) Common stock Retained earnings Total liabilities and stockholders' equity

140,000 202,851 55,000 35,894 610,000 1,043,745

124,488 7,038 5,000 0 500,000 250,000 157,219 1,043,745

The following information has been accumulated to assist with preparing the master budget for the first quarter of 2020:

1) Projected sales for November 2019 are $230,000. Credit sales are typically 85% of total sales. Helping Hands credit experience indicates that 12% of credit sales are collected during the month of sale, 74% in the month following the sale, and 14% in the second month following the sale. Experience shows the remaining credit sales are uncollectible.

2) Helping Hands cost of goods sold generally runs at 60% of sales. Inventory is purchased on account and 12% of each months purchases are paid during the month of purchase. The remainder is paid during the following month. In order to have adequate stocks of inventory on hand, the company attempts to have inventory on hand at the end of each month equal to 25% of the next months projected cost of goods sold.

3) The controller has estimated that Helping Hands other monthly expenses will be as follows:

Sales salaries Advertising and promotion Administrative salaries Depreciation Interest on bonds Property taxes

30,000 6,000 11,000 8,000 1,250 4,000

In addition, sales commissions run at the rate of 3.0 percent of sales. Sales commissions are paid in the month following the month of sale.

4) The company president has indicated that the company should invest $200,000 in an automated inventoryhandling system to control the movement of inventory in the companys warehouse just after the new year begins. The president would like to purchase the equipment primarily from the companys cash and marketable securities. However, the president believes the company should have a minimum cash balance of $20,000 at the end of each month. If necessary, the remainder of the equipment purchase may be financed using shortterm credit from a local bank. The minimum lending period for such a loan is three months (this means the earliest the loan can be paid off is March 31st). The current shortterm interest rates are 6 percent per year and are expected to remain at this rate through the time the equipment is purchased. If a loan is necessary, the entire amount required for the quarter must be borrowed on January 1st and must be in a $1,000 increment. The loan is a short term loan and the president has decided it should be paid off at the end of the first quarter if possible. If the entire amount cannot be repaid at March 31st, any partial payment will be paid at the end of the first quarter and in a $1,000 increment.
5) Helping Hands board of directors has indicated an intention to declare and pay dividends of $100,000 on the last day of each quarter.
6) The interest on any shortterm borrowing will be paid when the loan is repaid. Interest on Helping Hands bonds is paid semiannually on February 28 and August 31 for the preceding six month period.
7) Property taxes are paid quarterly on March 31, June 30, September 30, and December 31 for the preceding threemonth period.

Required: Build a model to forecast Helping Hand Corps cash balance at March 31, 2020. Your model must contain the following master budget schedules. Round all amounts to the nearest dollar. Your model should allow you to change any of the assumptions provided above and easily recalculate the ending cash balance at March 31, 2020. The assumptions may be on a separate worksheet but all of the schedules below must be on one worksheet.

1) Sales budget:

Total sales Cash sales Sales on account

2019

2020 February

November

December

January

March

2020

1st Quarter

2) Cash receipts budget:

January

February

March

1st Quarter

Cash sales Cash collections from credit sales made during current month Cash collections from credit sales made during preceding month Cash collections from credit sales made during 2nd preceding month Total cash receipts

3) Purchases budget:

Budgeted cost of goods sold Add: Desired ending inventory Total goods needed

Less: Expected beginning inventory Purchases

2019 December

2020

January

February

March

1st Quarter

4) Cash disbursements budget:

Inventory purchases: Cash payments for purchases during the

current month Cash payments for purchases during the

preceding month Total cash payments for inventory

purchases Other expenses:

Sales salaries Advertising and promotion Administrative salaries Interest on bonds Property taxes Sales commissions Total cash payments for other expenses

Total cash disbursements

2020

January

February

March

1st Quarter

5) Summary cash budget:

Cash receipts (sch 2) Less: Cash disbursements (sch 4)

Change in cash balance during period due to operations Sale of marketable securities (1/2/20) Proceeds from bank loan (1/2/20) Purchase of equipment

Repayment of bank loan (3/31/20) Interest on bank loan Payment of dividends Change in cash balance during the month Beginning cash balance

Ending cash balance

January

February

2020 March

1st Quarter

6) Prepare a memo to the president of Helping Hands Corp with at least two recommendations on how the company can ensure it completes the first quarter of 2020 with the minimum required cash balance. You should provide a plan to support your recommendation. For example, if you recommend an increase in sales, how can this be attained. Be specific. You should provide specific financial information for your recommendations utilizing your model (include a model for each of your recommendations). For example, if the company does X, the change in ending cash will be Y. Your model will become the property of Helping Hands Corp. and should be easy to use. Not buying the equipment is not an option. All assumptions are based on the purchase of the new equipment.