Question

Project 2 - Case 3 40 points 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 58 each Recent and forecasted sales in units are as follows: January (actual) February (actual) March (actual) April 24,000 25.000 35,000 39,000 52.000 June July August September 63.000 43.000 45.000 36,000 May 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. Variable: Sales commissions $ 1 per tie Fixed Wages and salaries Utilities Insurance Depreciation Miscellaneous $ 31.200 $ 23,100 S 1.100 $ 1.500 $ 3,400 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 39,000 Quarter 154,000 Budgeted sales units Selling price per unit Total sales May 52,000 $8 $416,000 June 63,000 $8 $504,000 $8 $8 $312,000 $1,232,000 Requirement 1b. : Schedule of Expected cash collections from sales, by month and in total June Sol March Sales April Sales May Sales June Sales Total cash collections Davis Dry Goods Schedule of Expected Cash collections April $210,000 $78,000 $O $0 $288,000 May $0 $234,000 $104,000 $0 $312,000 $126,000 $438,000 Quarter $210,000 $312,000 $416,000 $126,000 $1,064,000 SO $338,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 39,000 46,800 85,800 35,100 50,700 $5.00 $253,500 May 52,000 56,700 108,700 46,800 61,900 $5.00 $309,500 June 63,000 38,700 101,700 56,700 45,000 $5.00 $225,000 Quarter 154,000 38,700 192,700 35,100 157,600 $5.00 $788,000

Requirement 1d. : Schedule of expected cash disbursements for merchandise purchases, by month and in total June | SO $0 Davis Dry Goods Budgeted Cash Disbursements for Merchandise Purchases April May $96,500 $126,750 $126,750 $154,750 $o $223,250 $281,500 Accounts Payable April purchases May purchases June purchases Total cash payments $0 Quarter $96,500 $253,500 $309,500 $112,500 $772,000 So $154,7501 $112,500 $267,250 Requirement le. : Selling and administrative budget, by month and in total Davis Dry Goods Selling and administrative budget April May June | Quarter $39,000 $52,000 $63,000 $154,000 Selling expenses: Sales commissions Administrative expenses: Wages and salaries Utilities Insurance Depreciation Miscellaneous Total selling and administrative expenses $31,200 $23,100 $1,100 $1,500 $3,4001 $99,300 $31,200 $23,100 $1,100 $1,500 $3,400 $112,300 $31,2001 $23,100 $1,100 $1,500 $3,400 $123,300 $93,600 $69,300 $3,300 $4,500 $10,200 $334,900

Requirement 2: Cash Budget May $10,050 $338,000 $348,050 June $10,850 $438,000 $448,850 Davis Dry Goods Cash Budget For the Three Months Ending June 30 April Beginning cash balance $13,000 Add: Collections from customers $288,000 Total cash available $301,000 Less: Cash disbursements: Merchandise purchases $223,250 Sales commissions $39,000 Wages and salaries $31,200 Utilities $23,100 Miscellaneous $3,400 Land purchases Dividends paid $10,000 Total cash disbursements $329,9501 Excess (deficiency) of cash available over disbursements ($28,950) Financing Borrowings $39,000 Repayments $0 Interest $0 Total financing $39,000 Ending cash balance $10,050 $281,500 $52,000 $31,200 $23,100 $3,400 $21,000 $267,250 $63,000 $31,200 $23,100 $3,400 $412,200 ($64,150) $387,950 $60,900 $75,000 $0 $0 $75,000 $10,850 ($ 48,000) ($2,670) ($50,670) $10,230

Requirement 3 : Budgeted Income Statement for the three-month period ending June 30 $1,232,000 $924,000 $308,000 Davis Dry Goods Budgeted Income Statement For the Three-months Ended June 30, 2018 Sales Revenue | Variable expenses: Cost of Goods sold (154,000 units x $5) $770,000 Sales Commissions (154,000 units x $1) $154,000 Total Variable expenses Contribution Margin Fixed expenses: Wages and salaries $93,600 Utilities $69,300 Insurance $3,300 Depreciation $4,500 Miscellaneous $10,200 Interest expense $2,670 Total Fixed expenses Net Income I $183,570 $124,430 Requirement 4 : Budgeted Balance Sheet as of June 30 Davis Dry Goods Budgeted Balance Sheet June 30, 2018 Assets Cash Accounts Receivable Inventory (38,700 units x $5) Prepaid Insurance ($13,200 - $3,300) Fixed Assets, net ($172,600 + $ 21,000 - $4,500) Total assets $10,230 $378,000 $193,500 $9,900 $189,100 $780,730 Liabilities and Stockholder's Equity Accounts Payable Borrowings (Notes Payable) Common stock Retained Earnings ($177,800 +$124,430 - $10,000) Total liabilities and stockholders' equity $112,500) $66,000 $310,000 $292,230 $780,730
To see the formulas, please see the below images:

1 Solution 3 Requirement la. : Sales budget, by month and in total 5 6 Sales Budget April 39000 June 7 May 52000 63000 Quarter -SUM(C7:E7) 8 Budgeted sales units Selling price per unit Total sales =C7c8 =D7*D8 =E7*E8 |=F7*F8 9 10 11 12 Requirement 1b.: Schedule of Expected cash collections from sales, by month and in total June 14 15 16 17 Davis Dry Goods Schedule of Expected Cash collections April May =35000*8*75% 0 =C92595 =C9*75% =D925% lo 18 Mays March Sales April Sales I May Sales June Sales Total cash collections Quarter |-SUM(C16:E16) -SUM(C17:E17) =SUM(C18:E18) | -SUM(C19:E19) || | =SUM(F16:F19) 19 20 =D9*7596 =E9*25% =SUM(E16:19) | =SUM(C16:C19) =SUM(D16:D19) 21 22 Requirement 1c. : Merchandise purchase budget 23 24 25 26 June 27 Budgeted Sales Units 28 Add: Desired ending inventory 29 Total needs 30 Less: Likely beginning inventory 31 Required purchases (in units) 32 Unit cost 33 Required dollar purchases 34 Davis Dry Goods Merchandise Purchase Budget April May =C7 =D7 =D709096 =E79096 =C27+C28 =D27+D28 =C279096 =D2709096 =C29-C30 =D29-D30 =E7 -42000"9096 =E27+E28 27.00 =E29-E30 Quarter | =SUM(C27:E27) -E28 =F27+F28 =C30 =F29-F30 =C314032 |=D31*D32 =E31 32 =F31*F32

35 Requirement ld. : Schedule of expected cash disbursements for merchandise purchases, by month and in total 36 37 38 39 40 Davis Dry Goods Budgeted Cash Disbursements for Merchandise Purchases April May June Accounts Payable 96500 lo lo 41 April purchases =C33/2 =C33/2 42 May purchases =D33/2 =D33/2 43 June purchases =E33/2 44 Total cash payments | =SUM(C40:C43) =SUM D40:D43) =SUM(E40:E43) 45 46 Requirement le.: Selling and administrative budget, by month and in total Quarter |-SUM(C40:E40) | -SUM(C41:E41) | -SUM(C42:E42) -SUM(C43:E43) | | =SUM(F40:F43) 48 Davis Dry Goods Selling and administrative budget April May 50 June Quarter 1 =C7 7.1 =E71 I-SUM(C52:E52) AY 51 Selling expenses: Sales commissions Administrative expenses: Wages and salaries Utilities Insurance 57 Depreciation Miscellaneous 59 ITotal selline and administrative expenses 31200 31200 23100 23100 1100 1100 1500 1500 3400 3400 =SUM(C52:C58) =SUM(D52:D58) 31200 23100 1100 1500 3400 =SUME52:E58) -SUM(C54:E54) -SUM(C55:E55) -SUM(C56:E56) =SUM(C57:E57) | -SUM(C58:E58) =SUM(F52:F58) 58

Requirement 2 : Cash Budget sa sagg June =D85 -E20 -SUM(E67:E68) 69 70 71 72 =E44 =E52 =F54 73 74 Davis Dry Goods Cash Budget For the Three Month For the Three Months Ending June 30 April May Beginning cash balance 13000 EC95 Add: Collections from customers =C20 -D20 Total cash available -SUM(C67:C68) T-SUM(D67:D68) Less: Cash disbursements: Merchandise purchases =C44 =D44 Sales commissions =C52 |=D52 Wages and salaries -C54 =D54 Utilities =C55 =D55 Miscellaneous =C58 =D58 Land purchases 21000 Dividends paid 10000 Total cash disbursements -SUM(C71:C77) -SUM(D71:D77) Excess (deficiency) of cash available over disbursements =C69-C78 =D69-D78 Financing: Borrowings 39000 75000 Repayments Interest Total financing =SUM(C81:C83) T-SUM(D81:083) Ending cash balance =C79+C84 | =D79+D84 1=E55 TEE58 75 76 77 78 79 80 81 -SUM(E71:E77) =E69-E78 82 83 -48000 --C8139-081295) SUM(E81:E83) =E79+E84 84 85 86

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

| 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 (38,700 units x $5) 117 Prepaid Insurance ($13,200-$3,300) 118 Fixed Assets, net ($172,600 + $ 21,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,800 +$124,430 - $10,000) 126 Total liabilities and stockholders' equity 127 =E85 =E9*75% |=E2805 |=13200-C101 |=172600+D76-C102 SUMD114:D118) 123 =E33-E43 =C81+D81+E82 310000 =177800+D106-C77 =SUM(D122:D125)