Question

The marketing manager has recently completed a sales forecast. She believes the company’s sales will increase by 1 percent each month over the previous month’s sales from December 2015 through March 2016. Then sales are expected to remain constant for several months. Helping Hand’s 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.

5).Helping Hand’s 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 Hand’s 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 Hand’s 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 Corp’s 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

Answer 1

			SALES BUDGET
			November,15	December,15	January,16	February,16	March,16	1st quarter salesof 2016
	A	Sales	$200,000	$202,000	$204,020	$206,060	$208,121	$618,201
	B=0.1*A	Cash sales	$20,000	$20,200	$20,402	$20,606	$20,812	$61,820
	C=0.9*A	Sales on account	$180,000	$181,800	$183,618	$185,454	$187,309	$556,381
			CASH RECEIPT BUDGET
			November,15	December,15	January,16	February,16	March,16	1st quarter salesof 2016
	A	Sales	$200,000	$202,000	$204,020	$206,060	$208,121	$618,201
	B=0.1*A	Cash sales	$20,000	$20,200	$20,402	$20,606	$20,812	$61,820
	C=0.9*A	Sales on account	$180,000	$181,800	$183,618	$185,454	$187,309	$556,381
	D	Collection from Nov Sales	$23,400.00	$135,000.00	$18,000.0
	E	Collection from Dec Sales		$23,634.00	$136,350	$18,180
	F	Collection from Jan16 Sales			$23,870.34	$137,714	$18,362
	G	Collection from Feb Sales				$24,109.04	$139,090.64
	H	Collection from MarchSales					$24,350.13
	I=D+E+F+G+H	Total Cash Receipts			$178,220	$180,003	$181,803	$540,025
			PURCHASE BUDGET
			Data on cost of goods sold and ending inventory is required
			CASH DISBURSEMENT BUDGET
			Data on cost of goods sold and ending inventory is required