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
1st Quarter
November
December
January
March
2020
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
January
February
2020 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
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 $ 124,488 7,038 5,000 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 500,000 250,000 157,219 1,043,745 $ Helping Hand Sales Budget Reg 1 Total sales Cash sales Sales on account Nov 230,000 34,500 195,500 Dec 234,600 35,190 199,410 Jan 239,292 35,894 203,398 Feb 244,078 36,612 207,466 Mar 248,959 37,344 211,615 Quarter 732,329 109,849 622,480 Jan Cash receipts budget Reg2 Cash sales Cash collections from credit sales during current month Cash collections from credit sales during preceding month Cash collections from credit sales during 2nd preceding month Total cash recipts 35,894 24,408 147,563 27,370 235,235 Feb 36,612 24,896 150,515 27,917 239,940 Mar 37,344 25,394 153,525 28,476 244,738 Quarter 109,849 74,698 451,603 83,763 719,913 Purchase budget Req3 Budgeted COGS Add Desired Ending inventory Total Needs Less Beginning inventory Required Purchases Dec 140,760 35,894 176,654 35,190 141,464 Jan 143,575 36,612 180,187 35,894 144,293 Feb 146,447 37,344 183,791 36,612 147,179 Mar 149,376 37,344 186,720 37,344 149,376 Quarter 439,398 37,344 476,741 35,894 440,848 Jan Feb Mar Quarter 17,315 124,488 141,803 17,661 126,978 144,639 17,925 129,517 147,443 52,902 380,984 433,885 Cash disbursements budget Req 4 Inventory Purchases Cash payments for purchases during current month Cash payments for purchases during preceding month Total cash payments for inventory purchases Other expenses Sales salaries Advertising & promotion Administrative salaries Interest on bonds Property taxes Sales comissions Total cash payments for other expesnes Total cash disbursements 30,000 6,000 11,000 30,000 30,000 6,000 11,000 30,000 30,000 6,000 11,000 90,000 18,000 33,000 7,500 12,000 21,539 182,039 615,924 7,038 7,179 12,000 7,322 66,322 213,765 195,841 206,318 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 $ 124,488 7,038 5,000 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 500,000 250,000 157,219 1,043,745 $ Helping Hand Sales Budget Reg 1 Total sales Cash sales Sales on account Nov 230,000 34,500 195,500 Dec 234,600 35,190 199,410 Jan 239,292 35,894 203,398 Feb 244,078 36,612 207,466 Mar 248,959 37,344 211,615 Quarter 732,329 109,849 622,480 Jan Cash receipts budget Reg2 Cash sales Cash collections from credit sales during current month Cash collections from credit sales during preceding month Cash collections from credit sales during 2nd preceding month Total cash recipts 35,894 24,408 147,563 27,370 235,235 Feb 36,612 24,896 150,515 27,917 239,940 Mar 37,344 25,394 153,525 28,476 244,738 Quarter 109,849 74,698 451,603 83,763 719,913 Purchase budget Req3 Budgeted COGS Add Desired Ending inventory Total Needs Less Beginning inventory Required Purchases Dec 140,760 35,894 176,654 35,190 141,464 Jan 143,575 36,612 180,187 35,894 144,293 Feb 146,447 37,344 183,791 36,612 147,179 Mar 149,376 37,344 186,720 37,344 149,376 Quarter 439,398 37,344 476,741 35,894 440,848 Jan Feb Mar Quarter 17,315 124,488 141,803 17,661 126,978 144,639 17,925 129,517 147,443 52,902 380,984 433,885 Cash disbursements budget Req 4 Inventory Purchases Cash payments for purchases during current month Cash payments for purchases during preceding month Total cash payments for inventory purchases Other expenses Sales salaries Advertising & promotion Administrative salaries Interest on bonds Property taxes Sales comissions Total cash payments for other expesnes Total cash disbursements 30,000 6,000 11,000 30,000 30,000 6,000 11,000 30,000 30,000 6,000 11,000 90,000 18,000 33,000 7,500 12,000 21,539 182,039 615,924 7,038 7,179 12,000 7,322 66,322 213,765 195,841 206,318
