Question

PLEASE SHOW ALL WORK!!

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 company’s main product lines are small kitchen appliances and power tools. The marketing manager has recently completed a sales forecast. She believes the company’s sales will increase by 2 percent each month over the previous month’s sales from December 2019 through March 2020. Then sales are expected to remain constant for several months. Helping Hand’s projected balance sheet as of December 31, 2019 is as follows: Cash	$ 140,000
Accounts receivable	202,851
Marketable securities	55,000
Inventory	35,894
Buildings and equipment (net of accumulated depreciation)	610,000
Total assets	$ 1,043,745
Accounts payable	$ 124,488
Sales commissions payable	7,038
Bond interest payable	5,000
Property taxes payable	0
Bonds payable (3%; due in 2023)	500,000
Common stock	250,000
Retained earnings	157,219
Total liabilities and stockholders' equity	$ 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 Hand’s 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 Hand’s cost of goods sold generally runs at 60% of sales. Inventory is purchased on account and 12% of each month’s 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 month’s projected cost of goods sold.

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

Sales salaries	$ 30,000
Advertising and promotion	6,000
Administrative salaries	11,000
Depreciation	8,000
Interest on bonds	1,250
Property taxes	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 inventory-handling system to control the movement of inventory in the company’s warehouse just after the new year begins. The president would like to purchase the equipment primarily from the company’s 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 short-term 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 short-term 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 Hand’s 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 short-term borrowing will be paid when the loan is repaid. Interest on Helping Hand’s 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 three-month period.

Required: Build a model to forecast Helping Hand Corp’s 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:

2019

2020

November	December	January	February	March	1st Quarter
Total sales
Cash sales
Sales on account

2020
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:

2019	2020
December	January	February	March	1st Quarter
Budgeted cost of goods sold
Add: Desired ending inventory
Total goods needed
Less: Expected beginning inventory
Purchases

4) Cash disbursements budget:

2020
January	February	March	1st Quarter
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

5) Summary cash budget:

2020
January	February	March	1st Quarter
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

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.

Answer 1

Dear student ,

As per HomeworkLib policy , I answered first 4 Question . Please re post the same Question for balance , I will provide answer accordingly

Thank You!!

Company expects Sales increase 2% each month over the previous month
perdion - Dec '2019- March '2020
Projected Sales in Nov $ 230,000 = Cash
Collection pattern =12% of credit sales = Collected in Same month
and 74% of following month sales
and final Balance of 14% = second month of following sales
Credit Sales represnts =85% of sales
Remaining credit sales are not collectable
Sales commission =3% of sales
Company expects Sales increase 2% each month over the previous month
Company should have minimum Cash Balance $ 20000
Current short term Interest Rate =6%
		Amnt($)
Total Asset		1043745
Less
Account Receivable		202851
Marketable securities		55000
Invnetory		35894
Building & Equipment		610000
		903745
Cash Bal		140000
	Nov$	Dec$		Jan$	Feb$	March$	Quarter$
Sales $	2,30,000	2,34,600		2,39,292	2,44,078	2,48,959	7,32,329
Cash Sales
15%	34,500	35,190		35,894	36,612	37,344	1,09,849
Credit Sales
85%	1,95,500	1,99,410		2,03,398	2,07,466	2,11,615	6,22,480
Cash Sales				35,894	36,612	37,344	1,09,849
Collection paterm
First Stage -12%- current Month sale				24,408	24,896	25,394	74,698
Second Stage -74%- following Month sale				1,49,558	1,52,549	1,55,600	4,57,706
Third Stage -14%-second Month allowing in Sales				27,370	27,917	28,476	83,763
Total Cash Receipt				2,37,229	2,41,974	2,46,813	7,26,016
Next Purchase Budget
Cost of goods sold = 60% of sales	1,38,000	1,40,760		1,43,575	1,46,447	1,49,376	4,39,398
Add- Closing Inventory -at 25%- Next Month Cost of goods Sold	35,190	35,894		36,612	37,344	37,344	1,11,299
Less- Opening Inventory		35,190		35,894	36,612	37,344	1,09,849
Derived Cost of Goods sold		1,41,464		1,44,293	1,47,179	1,49,376	4,40,848
Purchase pattern
12% each month Purchase				17,315	17,661	17,925	52,902
Balance 88% paid during folliwng month				1,24,488	1,26,978	1,29,517	3,80,984
Total Cash Payment = Inventory purchase=A				1,41,803	1,44,639	1,47,443	4,33,885
Other expenses
Sales Saalries				30,000	30,000	30,000	90,000
Advertisement & Promotion				6,000	6,000	6,000	18,000
Admin Salary				11,000	11,000	11,000	33,000
Depreciation				8,000	8,000	8,000	24,000
Interest on Bond					1,250		1,250
Property tax						4,000	4,000
Sales commission (3% of following mnth sales )				7,038	7,179	7,322	21,539
Total Cash payment- Other Expenses -B				62,038	63,429	66,322	1,91,789
Total Cash disbursement ( A+B)				2,03,841	2,08,068	2,13,765	6,25,674
Final Position of Cash Budget
Total Cash Receipt				2,37,229	2,41,974	2,46,813	7,26,016
Total Cash disbursement ( A+B)				2,03,841	2,08,068	2,13,765	6,25,674