PLEASE SHOW ALL CALCULATIONS FOR EXCEL SPREADSHEET!!!

Case Study #2 Budgeting

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 1 percent each month

over the previous months sales from December 2015 through March 2016. Then sales are expected to

remain constant for several months. Helping Hands projected balance sheet as of December 31, 2015 is

as follows:

Cash $ 60,000

Accounts receivable 172,530

Marketable securities 10,000

Inventory 39,784

Buildings and equipment (net of accumulated depreciation) 600,000

Total assets $ 882,314

Accounts payable $ 111,940

Sales commissions payable 4,040

Bond interest payable 8,000

Property taxes payable 0

Bonds payable (4%; due in 2020) 600,000

Common stock 100,000

Retained earnings 58,334

Total liabilities and stockholders' equity $ 882,314

The following information has been accumulated to assist with preparing the master budget for the first

quarter of 2016:

1) Projected sales for November 2015 are $200,000. Credit sales are typically 90% of total sales.

Helping Hands credit experience indicates that 13% of credit sales are collected during the

month of sale, 75% in the month following the sale, and 10% 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 65% of sales. Inventory is purchased on

account and 15% 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 30%

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 $ 35,000

Advertising and promotion 5,000

Administrative salaries 12,000

Depreciation 7,500

Interest on bonds 2,000

Property taxes 1,000

In addition, sales commissions run at the rate of 2.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 $225,000 in an automated

inventory-handling 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 $30,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. 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 president has decided it should

be paid off at the end of the first quarter if possible. 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

$150,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 Hands bonds is paid semiannually on February 28 and August 31 for the preceding sixmonth

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 Corps cash balance at March 31, 2016. 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, 2016. The assumptions may be on a separate

worksheet but all of the schedules below must be on one worksheet.

1) Sales budget:

2015 2016

November December January February March 1st Quarter

Total sales

Cash sales

Sales on account

2) Cash receipts budget:

2016

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:

2015 2016

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:

2016

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:

2016

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/16)

Proceeds from bank loan (1/2/16)

Purchase of equipment

Repayment of bank loan (3/31/16)

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 2016 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. 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.