Question

Project 2 - Case 4 40 points Davis Dry Goods distributes sikties. 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) 22.000 33,000 37,000 41,000 51,000 June July August September 67,000 46,000 37.000 34.000 April 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 S6 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: $1 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,100 995,600 $23,500 $15,300 $1,200 $1,500 $3,100 $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,2001 $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 Dividends paid $9,000 Total cash disbursements $318,400 Excess (deficiency) of cash available over disbursements $4,600 Financing Borrowings $6,000 Repayments Interest $0 Total financing $6,000 Ending cash balance $10,600 $288,500 $51,000 $23,500 $15,3001 $3,100 $26,000 $283,750 $67,000 $23,500 $15,300 $3,100 $o/ SO so/ $407,400 ($48,800) $392,650 $57,550 $0 $59,000 $0 $o/ ($46,000) ($1,360) ($47,360) $10,190 $59,000 $10,200

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: Wages 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,160 $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 (41,400 units x $5) 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 1=37000*8*75% o =C9*25% =C9*75% =D925% March Sales April Sales May Sales June Sales Total cash collections 17 18 19 20 21 June lo o | =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 26 June =E7 27 Budgeted Sales Units 28 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*9096 I=C29-C30 =D29-D30 -46000*90% =E27+E28 |=E27*90% =E29-E30 Quarter 1 SUM(C27:E27) |=E28 =F27+F28 =C30 =F29-F30 30 1=C31C32 =D31D32 =E31*E32 34

34 35 Requirement 1d. : Schedule of expected cash disbursements for merchandise purchases, by month and in total 36 37 38 39 41 42 43 Accounts Payable April purchases May purchases June purchases Total cash payments Davis Dry Goods Budgeted Cash Disbursements for Merchandise Purchases April May June 101500 0 |=C33/2 =C33/2 =D33/2 =D33/2 =E33/2 -SUM(C40:C43) SUM(D40:043) -SUM(E40:143) Quarter I-SUM(C40:E40)| -SUM(C41:E41) |-SUM(C42:E42) |-SUM(C43:E43) SUM(F40:F43) 46 Requirement le.: Selling and administrative budget, by month and in total 49 Davis Dry Goods Selline and administrative budeet April May 50 June Quarter =C7*1 =D7*1 =E701 SUM(C52:52) 54 Selling expenses: Sales commissions Administrative expenses: Wapes and salaries Utilities Insurance Depreciation 2 55 56 3500 23500 15300 15300 1200 1200 1500 1500 3100 3100 =SUM(C52:C58) =SUM(D52:D58) 23500 15300 1200 1500 3100 SUM(E52:E58) =SUMÍC54-754) -SUM(C55:E55) -SUM(C56:E56) -SUM(C57:E57) || =SUM(C58:E58) | SUM(F52:F58) | 57 58 59 60 Miscellaneous Total selling and administrative expenses

C D E Requirement 2: Cash Budget June D85 E20 SUM(E67:E68) c20 70 71 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 =D20 Total cash available =SUM(C67:C68) T-SUM(D67:D68) Less: Cash disbursements: Merchandise purchases -C44 =D44 Sales commissions =C71 =D7*1 Wages and salaries 23500 23500 Utilities 15300 15300 Miscellaneous 3100 3100 Land purchases 260000 | Dividends paid Total cash disbursements =SUM(C71:C77) T-SUM(D71:077) Excess (deficiency) of cash available over disbursements =C69-078 |=D69-078 Financing Borrowines 6000 =59000 Repayments Interest ITotal financine =SUM(C81:C83) T-SUM(D81:083) Ending cash balance =C79+C84 |=D79+D84 =F44 =E7*1 23500 15300 3100 75 76 9000 77 78 79 -SUM(E71:E77) =E69-E78 80 81 lo lo 83 84 85 |-46000 =-(081396HD81296 =SUM(E81:E83) =E79+E84

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

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

We were unable to transcribe this image

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,100 995,600 $23,500 $15,300 $1,200 $1,500 $3,100 $111,600 $70,500 $45,900 $3,600 $4,500 $9,300 $292,800

We were unable to transcribe this image

91 Requirement 4: Budgeted Balance Sheet as of June 30 Budgeted Balance Sheet June 30 Assets Cash 97 Accounts Receivable 98 Inventory (12,000 units x $4) 99 Prepaid Insurance ($21,000-$9,000) 100 Property and equipment (net) ($950,000+$56,000-$42,0 101 Total assets 102 103 Liabilities and Stockholder's Equity Accounts Payable 105 Dividends Payable 106 Common stock 107 Retained Earnings ($580,000+$154,700 - $15,000) 108 Total liabilities and stockholders' equity =F68 =F9-SUM(F17:F19) -E274 =21000-C85 -950000+F59-C86 =SUM(D96:D100) =E32-E41 15000 800000 =580000+D89-D105 SUM(D104:D107)

Requirement 4 : Budgeted Balance Sheet as of June 30 Davis Dry Goods Budgeted Balance Sheet June 30, 2018 Assets Cash Accounts Receivable Inventory (41,400 units x $5) 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

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 1=37000*8*75% o =C9*25% =C9*75% =D925% March Sales April Sales May Sales June Sales Total cash collections 17 18 19 20 21 June lo o | =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 26 June =E7 27 Budgeted Sales Units 28 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*9096 I=C29-C30 =D29-D30 -46000*90% =E27+E28 |=E27*90% =E29-E30 Quarter 1 SUM(C27:E27) |=E28 =F27+F28 =C30 =F29-F30 30 1=C31C32 =D31D32 =E31*E32 34

34 35 Requirement 1d. : Schedule of expected cash disbursements for merchandise purchases, by month and in total 36 37 38 39 41 42 43 Accounts Payable April purchases May purchases June purchases Total cash payments Davis Dry Goods Budgeted Cash Disbursements for Merchandise Purchases April May June 101500 0 |=C33/2 =C33/2 =D33/2 =D33/2 =E33/2 -SUM(C40:C43) SUM(D40:043) -SUM(E40:143) Quarter I-SUM(C40:E40)| -SUM(C41:E41) |-SUM(C42:E42) |-SUM(C43:E43) SUM(F40:F43) 46 Requirement le.: Selling and administrative budget, by month and in total 49 Davis Dry Goods Selline and administrative budeet April May 50 June Quarter =C7*1 =D7*1 =E701 SUM(C52:52) 54 Selling expenses: Sales commissions Administrative expenses: Wapes and salaries Utilities Insurance Depreciation 2 55 56 3500 23500 15300 15300 1200 1200 1500 1500 3100 3100 =SUM(C52:C58) =SUM(D52:D58) 23500 15300 1200 1500 3100 SUM(E52:E58) =SUMÍC54-754) -SUM(C55:E55) -SUM(C56:E56) -SUM(C57:E57) || =SUM(C58:E58) | SUM(F52:F58) | 57 58 59 60 Miscellaneous Total selling and administrative expenses

C D E Requirement 2: Cash Budget June D85 E20 SUM(E67:E68) c20 70 71 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 =D20 Total cash available =SUM(C67:C68) T-SUM(D67:D68) Less: Cash disbursements: Merchandise purchases -C44 =D44 Sales commissions =C71 =D7*1 Wages and salaries 23500 23500 Utilities 15300 15300 Miscellaneous 3100 3100 Land purchases 260000 | Dividends paid Total cash disbursements =SUM(C71:C77) T-SUM(D71:077) Excess (deficiency) of cash available over disbursements =C69-078 |=D69-078 Financing Borrowines 6000 =59000 Repayments Interest ITotal financine =SUM(C81:C83) T-SUM(D81:083) Ending cash balance =C79+C84 |=D79+D84 =F44 =E7*1 23500 15300 3100 75 76 9000 77 78 79 -SUM(E71:E77) =E69-E78 80 81 lo lo 83 84 85 |-46000 =-(081396HD81296 =SUM(E81:E83) =E79+E84

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

We were unable to transcribe this image