Question

Davis Dry Goods distributes silk ties. You are in charge of creating Davis' master budget for the upcoming second quarter, April - June 2018. Davis desires a minimum ending cash balance each month of $10.000. The ties are sold to retailers for $8 each Recent and forecasted sales in units are as follows: January (actual) February (actual) March (actual) April May 22,000 June 33,000 July 37,000 August 41,000 September 51,000 67,000 46,000 37,000 34,000 The large buildup in sales before and during June is due to Father's Day. Ending inventories are supposed to equal 90% of the next month's sales in units. The ties cost the company $5 each. Purchases are paid for as follows: 50% in the month of purchase and the remaining 50% in the following month. All sales are on credit, with no discount, and payable within 15 days. The company has found, however, that only 25% of a month's sales are collected by month-end. An additional 75% is collected in the following month. Bad debts have been negligible. The company's monthly selling and administrative expenses are given below: SI per tie Variable: Sales commissions Fixed: Wages and salaries Utilities Insurance Depreciation Miscellaneous $ 23,500 $ 15,300 $ 1,200 $ 1,500 $ 3.100 All selling and administrative expenses are paid during the month, in cash, with the exception of depreciation and insurance expired. Davis has an agreement with a bank that allows it to borrow in increments of $1,000 at the beginning of each month. The interest rate on these loans is 1% per month, and for simplicity, we will assume that interest is not compounded. At the end of the quarter, the company would pay the bank all of the accumulated interest on the loan and as much of the loan as possible (in increments of $1,000), while still retaining at least $10,000 in cash.

Answer 1

Solution Requirement 1a. : Sales budget, by month and in total Sales Budget April 41,000 May 51,000 June 67,000 Budgeted sales units Selling price per unit Total sales Quarter 159,000 $8 $1,272,000 $8 $8 $328,000 $408,000 $536,000 Requirement 1b. : Schedule of Expected cash collections from sales, by month and in total Davis Dry Goods Schedule of Expected Cash collections April $222,000 $82,000 March Sales April Sales May Sales June Sales Total cash collections $0 May $0 $246,000 $102,000 $0 $348,000 June $0 $0 $306,000 $134,000 $440,000 Quarter $222,000 $328,000 $408,000 $134,000 $1,092,000 $0 $304,000 Requirement 1c. : Merchandise purchase budget Budgeted Sales Units Add: Desired ending inventory Total needs Less: Likely beginning inventory Required purchases (in units) Unit cost Required dollar purchases Davis Dry Goods Merchandise Purchase Budget April 41,000 45,900 86,9001 36,900 50,000 $5.00 $250,000 May 51,000 60,300 111,300 45,900 65,400 $5.00 $327,000 June 67,000 41,400 108,4001 60,300 48,100 $5.00 $240,500 Quarter 159,000 41,400 200,400 36,900 163,500 $5.00 $817,500

Requirement 1d. : Schedule of expected cash disbursements for merchandise purchases, by month and in total June $0 Accounts Payable April purchases May purchases June purchases Total cash payments Davis Dry Goods Budgeted Cash Disbursements for Merchandise Purchases April May $101,500 $0 $125,000 $125,000 $0 $163,500 $o $226,500 $288,500 Quarter $ 101,500 $250,000 $327,000 $120,250 $798,750 $0 $163,500 $120,250 $283,750 $0 Requirement le. : Selling and administrative budget, by month and in total Davis Dry Goods Selling and administrative budget April May June Quarter $41,000 $51,000 $67,000 $159,000 Selling expenses: Sales commissions Administrative expenses: Wages and salaries Utilities Insurance Depreciation Miscellaneous Total selling and administrative expenses $23,500 $15,300 $1,200 $1,500 $3,100 $85,600 $23,500 $15,300 $1,200 $1,500 $3,1001 $95,600 $23,500 $15,300 $1,2001 $1,500 $3,1001 $111,600 $70,500 $45,900 $3,600 $4,500 $9,300 $292,800 Requirement 2: Cash Budget May $10,600 $348,000 $358,600 June $ 10,200 $440,000 $450,200 Davis Dry Goods Cash Budget For the Three Months Ending June 30 April Beginning cash balance $19,000 Add: Collections from customers $304,000 Total cash available $323,000 Less: Cash disbursements: Merchandise purchases $226,500 Sales commissions $41,000 Wages and salaries $23,500 Utilities $15,3001 Miscellaneous $3,100 Land purchases $0 Dividends paid $9,000 Total cash disbursements $318,400 Excess (deficiency) of cash available over disbursements $4,600 Financing Borrowings $6,000 Repayments $0 Interest Total financing $6,000 Ending cash balance $10,600 $288,500 $51,000 $23,500 $15,300 $3,100 $26,000 $0 $407,400 ($48,800) $283,750 $ 67,000 $23,500) $15,300 $3,100 $0 $o $392,650 $57,550 $0 $59,000 $0 $0 $59,000 $10,200 ($46,000) ($1,360) $47,360) $10,190

Requirement 3 : Budgeted Income Statement for the three-month period ending June 30 $1,272,000 $954,000 $318,000 Davis Dry Goods Budgeted Income Statement For the Three-months Ended June 30, 2018 Sales Revenue Variable expenses: Cost of Goods sold (159,000 units x $5) $795,000 Sales Commissions (159,000 units x $1) $159,000 Total Variable expenses Contribution Margin Fixed expenses: Wares and salaries $70,500 Utilities $45,900 Insurance ($1,200 x 3month) $3,600 Depreciation ($1,500 x 3month) $4,500 Miscellaneous $9,300 Interest expense $1,3601 Total Fixed expenses Net Income | $135,1601 $182,840 Requirement 4 : Budgeted Balance Sheet as of June 30 Davis Dry Goods Budgeted Balance Sheet June 30, 2018 Assets Cash Accounts Receivable Inventory Prepaid Insurance ($14,400 - $3,600) Fixed Assets, net ($ 148,500 + $ 26,000 - $4,500) Total assets $10,190 $402,000 $207,000 $10,800 $170,000 $799,990 Liabilities and Stockholder's Equity Accounts Payable Borrowings (Notes Payable) Common stock Retained Earnings ($177,900 + $182,840 - $9,000) Total liabilities and stockholders' equity $120,250 $19,000 $309,000 $351,740 $799,990

To see the formulas, please see the below images:

1 Solution 3 Requirement la. : Sales budget, by month and in total 6 Sales Budget April 41000 May June Quarter -SUM(C7:E7) 51000 Budgeted sales units Selling price per unit Total sales 67000 18 =E7*E8 9 =C7*C8 =D7*D8 =F7*F8 10 Requirement 1b. : Schedule of Expected cash collections from sales, by month and in total 11 12 13 14 15 16 Davis Dry Goods Schedule of Expected Cash collections April May 222000 lo =C9*25% =C9*75% o =D925% l 17 18 19 20 21 March Sales April Sales May Sales June Sales Total cash collections June o 0 | =D9*75% =E9*25% SUM(E16:E19) Quarter |-SUM(C16:516) |-SUM(C17:E17) -SUM(C18:E18) -SUM(C19:19) SUM(F16:F19) | T-SUM(C16:C19) | SUM(D16:019) Requirement 1c. : Merchandise purchase budget 24 25 26 June =E7 28 27 Budgeted Sales Units Add: Desired endine inventory 29 Total needs Less: Likely beginning inventory 31 Required purchases in units) 32 Unit cost 33 Required dollar purchases Davis Dry Goods Merchandise Purchase Budget April May =C7 =D7 |=D7*90% =E7*90% =C27+C28 =D27+D28 |=C279096 =D27*90% =C29-C30 =D29-D30 -46000*90% =E27+E28 |=E27*90% =E29-E30 Quarter 1 SUM(C27:E27) |=E28 =F27+F28 =C30 =F29-F30 30 =31*C32 =D31*D32 =E31*E32 =F31*F32 34

35 Requirement 1d. : Schedule of expected cash disbursements for merchandise purchases, by month and in total DAD Accounts Payable April purchases May purchases June purchases Total cash payments Davis Dry Goods Budgeted Cash Disbursements for Merchandise Purchases April May June 101500 lo lo =C33/2 2 =C33/ 0 =D33/2 =D33/2 1E33/2 |-SUM(C40:043) SUM(D40:043) =SUM(E40:E43) Quarter -SUM(C40:E40) | -SUM(C41:E41) =SUM(C42:E42) | -SUM(C43:E43) SUM(F40:F43) Requirement le.: Selling and administrative budget, by month and in total Davis Dry Goods Selling and administrative budget April May June Quarter =C7*1 =D7*1 =E7*1 -SUM(C52:E52) 53 54 55 Selling expenses: Sales commissions Administrative expenses: Wages and salaries Utilities Insurance Depreciation Miscellaneous Total selling and administrative expenses 56 57 58 23500 123500 15300 15300 1200 1200 1500 1500 3100 3100 =SUM(C52:C58) =SUM(D52:058) 23500 15300 1200 1500 3100 =SUME52:E58) =SUM(C54:E54) | =SUM(C55:E55) | -SUM(C56:E56) -SUM(C57:E57) =SUM(C58:E58) | SUM(F52:F58) 59

61 Requirement 2: Cash Budget 67 June D95 =E20 I-SUM/E67:E68) 69 70 71 1 72 73 74 Davis Dry Goods Cash Budget For the Three Months Endine June 30 April May Beginning cash balance 19000 =C85 Add: Collections from customers =C20 =D20 Total cash available | -SUM(C67:C68) T-SUM(D67:D68) Less: Cash disbursements: Merchandise purchases EC44 44 Sales commissions =c7*1 =D7*1 Wages and salaries 23500 23500 Utilities 15300 15300 Miscellaneous 3100 3100 Land purchases 26000 Dividends paid 9000 Total cash disbursements -SUM(C71:C77) SUM(D71:077) Excess (deficiency) of cash available over disbursements =C69-078 =D69-D78 Financing: Borrowines 6000 1=59000 Repayments lo Interest Total financine -SUM(C81:C83) SUM(D81:083) Ending cash balance =C79+C84 =D79+D84 =E44 =E7*1 23500 15300 3100 lo 75 76 77 78 79 I-SUM(E71:E77) =E69-E78 80 81 82 83 84 85 - 46000 =-(C81396HD81296 |--SUM(E81:E83) =E79+E84 CO2) 86

87 Requirement 3 : Budgeted Income Statement for the three-month period ending June 30 =F9 93 |-SUM(C94:C95) =D92-096 Davis Dry Goods Budgeted Income Statement For the Three-months Ended June 30, 2018 Sales Revenue Variable expenses: 94 Cost of Goods sold (159,000 units x $5) =F7*5 95 Sales Commissions (159,000 units x $1) =F7*1 96 Total Variable expenses 97 Contribution Margin 98 Fixed expenses: 99 Wages and salaries =C73+D73+E73 100 Utilities =C74+D74+E74 101 Insurance ($ 1,200 x 3month) =120003 102 Depreciation ($1,500 x 3month) =1500*3 Miscellaneous =C75+D75+E75 104 Interest expense --E83 105 Total Fixed expenses 106 Net Income 107 103 ESUM(C99:C104) =D97-D105

C D 108 Requirement 4: Budgeted Balance Sheet as of June 30 109 110 Davis Dry Goods 111 Budgeted Balance Sheet 112 June 30, 2018 113 Assets 114 Cash 115 Accounts Receivable 116 Inventory 117 Prepaid Insurance ($14,400-$3,600) 118 Fixed Assets, net ($148,500+$26,000 - $4,500) 119 Total assets 120 121 Liabilities and Stockholder's Equity 122 Accounts Payable Borrowings (Notes Payable) 124 Common stock 125 Retained Earnings ($177,900 + $182,840-$9,000) 126 Total liabilities and stockholders' equity =E85 -E9*7596 =E28 5 =14400-C101 =148500+D76-C102 SUM(D114:D118) =E33-E43 =C81+D81+E82 309000 =177900+D106-C77 | =SUM(D122:D125) 127