Cash Budget Problem- Intro

Hillyard Company, an office supplies specialty store, prepares its master budget on a quarterly basis. The following data

have been assembled to assist in preparing the master budget for the first quarter:

Balance Sheet, Period Ending December 31, 2018

Cash $48,000

Accounts Payable $93,000

Accounts Receivable $224,000

$0

Working Capital Line

Inventory $60,000

ccrued Liabilities $o

PP&E $670,000

Accum Depr $300.000

Common Stock $500,000

Retained Earnings $109,000

Total Assets $702,000

Total Liab & Equity

702,000

Sales

December 2018 (Actual) $280,000

January 2019 Forecast $400,000

February 2019-Forecast $600,000

March 2019-Forecast $300,000

April 2019 Forecast $200,000

After lengthy conversations with the various department managers, the following assumptions were made for the

upcoming quarter:

Sales to customers are 20% for cash and 80% placed on credit.

All credit sales are fully collected in the month following the sale.

100% of the December 31 Accounts Receivable are from December credit sales.

The company's expected gross margin on sales for the quarter is 40%, each month.

Monthly expenses are forecasted as follows:

o Salaries expense is $27,000, per month

Advertising expense is $70,000 per month

Shipping costs is 5% of sales

Other Administrative expenses is 3% of sales

o

o Depreciation is $14,o00, per month

Each month's ending inventory should equal 25% of the following month's cost of goods sold.

Of the inventories purchased, 50% is paid for in the month it is purchased and the other 50% is paid for in the

following month. December Accounts Payable is fully paid in January

During February, the company will purchase (for cash) a new copy machine for $3,500. During March, other

equipment will be purchased at a cost of $84,500 (for cash).

During January, the company will declare and pay cash dividends totaling $45,000

The CFO wants to maintain a minimum cash balance of $30,000 at the end of each month, beginning at the end

of January.

The company has an agreement with the bank that allows borrowings in increments of $1,000. Any anticipated

borrowings are taken out at the beginning of the month.

Interest on borrowings is 19% per month; interest is not compounded.

At the end of the month, any excess cash is used to pay outstanding borrowings and interest owed.

REQUIRED:

A. Prepare a cash budget for the months of Jan, Feb and March 2019

B. Prepare a Proforma Balance Sheet as of March 31, 2019

C. Prepare of Proforma Income Statement for the first quarter (3 months) ending March 31, 2019

Hilly Company Cash Budget Apr Beginning Cash Balance Jan $ Feb Mar $ 30,000 $ 30,000 48,000 $ 80.000 $ 60,000 Cash Sales - Current Month Credit Collections - Jan Sales Credit Collections - Feb Sales Credit Collections - Mar Sales 120,000 $ 320,000 480,000 240,000 Hillyard Company Income Statement - Quarting Ending March 31, 2019 Jan Feb War Qur Apr A/R at End of March Sales $ 400,000 $ 500.000 $ 300,000 $1,300,000 $ 200.000 -COGS 1240.000) 380,000) : 180,0001 + (780,000) $ (120,000) Gross Margin 160.000 $ 240,000 $ 120.000'$ 520,000 $80,000 Sales Exp $ (27,000) $ 127.000) = 127.000) (81.0001 Adv Exp (70,000) $ (70,000 $ (70.000) $ (210.0001 Shipping Costs (20,000) $ (30.000) = [15.000 $ 165,000) Admin Exp $ (12,000) $ (18.000) = (3.000) $ 139,000) Depreciation Exp (14,000) $ (14,00015 (14.000) (42.0001 Interest Exp $ 1800) $ 1810). (810) 12.4203 Total Expenses $ (143,800) (159,810) 1135,870) $ (439.4201 Dec AIR Collections $ 224,000 $ $ Total Collections $ 304,000 $ 440,000 $540,000 $ (135,000) $ Disbursements Inv Purch - Jan (see below) Inv Pureh - Feb (see below) Inv Purch - Mar (see below) Dec AIP (135.000) (157,500) $ Net Income $ 16,200 $ (157,500) (82,500) 80,190 $ (15,8101 # 80,580 (82,500) $ (93,000) Hillyard Company Balance Sheet - December 31, 2018 Sales Exp Advertising Exp Shipping Costs (5% of Sales) Admin Exp (3% of Sales! (27.000) 170,000) 120,000) $ (12.000) $ (27,000) $ (70,000) $ (30,000) (18,000) $ (27,000) AIP at End of March (70,000) (15,000) (9,000) 33.000 $ $ Cash AIR Inventory PP&E - Gross Accum Depr 48,000 AIP 224,000 Working Capital Line * 60,000 Accrued Expenses $ 670,000 $ (300,000) Common Stock Retained Earrings $ Purchase New Copier Purchase Other New Equipment (3,500) (84.500) 500.000 109.000 + Cash Dividend $ (45,0001 Total Assets $ 702,000 Total Liab & Equity $ 702.000 Total Disbursements $ (402,000) $ (441,000) $ (445,500) Available Cash Borrowings/Pay Back WC) Interest on WIC (50,000) $ 29,000 $ 124,500 80,000 $ 1,000 $ (81.000) $ (2.420) Hillyard Company Balance Sheet - March 31, 2019 $ 82.500 End Cash $ 30,000 $ 30,000 $ 41,080 Cash AIR Inventory PP&E - Gross Accum Depr 41.080 AIP $ 240.000 Working Capital Line $ 30,000 Accrued Expenses 758.000 $ (342,000) Common Stock Retained Earrings $ 500.000 144.580 Inventory $ Jan Feb Mar Beginning Balance 60,000 $ 90,000 $ 45,000 Purchases $ 270,000 $ 315,000 $ 165,000 Salos $ (240,000) $ (360,000) 1180,000) Ending 90,000 $ 45,000 $ 30,000 Total Assets $ 727,080 Total Liab & Equity $ 727.080 Hilly Company Cash Budget Apr Beginning Cash Balance Jan $ Feb Mar $ 30,000 $ 30,000 48,000 $ 80.000 $ 60,000 Cash Sales - Current Month Credit Collections - Jan Sales Credit Collections - Feb Sales Credit Collections - Mar Sales 120,000 $ 320,000 480,000 240,000 Hillyard Company Income Statement - Quarting Ending March 31, 2019 Jan Feb War Qur Apr A/R at End of March Sales $ 400,000 $ 500.000 $ 300,000 $1,300,000 $ 200.000 -COGS 1240.000) 380,000) : 180,0001 + (780,000) $ (120,000) Gross Margin 160.000 $ 240,000 $ 120.000'$ 520,000 $80,000 Sales Exp $ (27,000) $ 127.000) = 127.000) (81.0001 Adv Exp (70,000) $ (70,000 $ (70.000) $ (210.0001 Shipping Costs (20,000) $ (30.000) = [15.000 $ 165,000) Admin Exp $ (12,000) $ (18.000) = (3.000) $ 139,000) Depreciation Exp (14,000) $ (14,00015 (14.000) (42.0001 Interest Exp $ 1800) $ 1810). (810) 12.4203 Total Expenses $ (143,800) (159,810) 1135,870) $ (439.4201 Dec AIR Collections $ 224,000 $ $ Total Collections $ 304,000 $ 440,000 $540,000 $ (135,000) $ Disbursements Inv Purch - Jan (see below) Inv Pureh - Feb (see below) Inv Purch - Mar (see below) Dec AIP (135.000) (157,500) $ Net Income $ 16,200 $ (157,500) (82,500) 80,190 $ (15,8101 # 80,580 (82,500) $ (93,000) Hillyard Company Balance Sheet - December 31, 2018 Sales Exp Advertising Exp Shipping Costs (5% of Sales) Admin Exp (3% of Sales! (27.000) 170,000) 120,000) $ (12.000) $ (27,000) $ (70,000) $ (30,000) (18,000) $ (27,000) AIP at End of March (70,000) (15,000) (9,000) 33.000 $ $ Cash AIR Inventory PP&E - Gross Accum Depr 48,000 AIP 224,000 Working Capital Line * 60,000 Accrued Expenses $ 670,000 $ (300,000) Common Stock Retained Earrings $ Purchase New Copier Purchase Other New Equipment (3,500) (84.500) 500.000 109.000 + Cash Dividend $ (45,0001 Total Assets $ 702,000 Total Liab & Equity $ 702.000 Total Disbursements $ (402,000) $ (441,000) $ (445,500) Available Cash Borrowings/Pay Back WC) Interest on WIC (50,000) $ 29,000 $ 124,500 80,000 $ 1,000 $ (81.000) $ (2.420) Hillyard Company Balance Sheet - March 31, 2019 $ 82.500 End Cash $ 30,000 $ 30,000 $ 41,080 Cash AIR Inventory PP&E - Gross Accum Depr 41.080 AIP $ 240.000 Working Capital Line $ 30,000 Accrued Expenses 758.000 $ (342,000) Common Stock Retained Earrings $ 500.000 144.580 Inventory $ Jan Feb Mar Beginning Balance 60,000 $ 90,000 $ 45,000 Purchases $ 270,000 $ 315,000 $ 165,000 Salos $ (240,000) $ (360,000) 1180,000) Ending 90,000 $ 45,000 $ 30,000 Total Assets $ 727,080 Total Liab & Equity $ 727.080