question above

answer section

in first formula amount is here

REQUIREMENT 1. You have been asked to prepare a cash budget for the next four months to see if the loan can be repaid. Review the worksheet CASHBUD that follows these requirements. The problem data have already been entered in the Data Section of the worksheet. 2. Open the tile CASHBUD from the website for this book at cengagebrain.com. Enter the eight formulas where indicated on the worksheet. Enter your name in cell A1. Save the file as CASHBUD2. Print the worksheet when done. Also print your formulas. Check figure: Ending cash balance for April (cell E54), $7,772 3. Can the $10,000 note be repaid on May 1? Explain. 4. How do the other months look? Do you anticipate any problems? Explain. WHAT-IF ANALYSIS 5. The following four suggestions have been made to improve the company's cash position. Evaluate the effect on cash flow for each of the four suggestions. After evaluating each suggestion, enter the projected cash balances in the spaces provided. Consider each suggestion separately. Reset cells to their initial values after each new suggestion. a. Seek agreement with suppliers to extend the credit period to 30 days. This would mean that all current monthly purchases would be paid for in the following month. b. Raise the unit price from $28 to $30. A price increase will reduce unit sales by 10% each month. Unit purchases will also be reduced by 10%. C. Put the company's two salespeople on straight commission. This would reduce fixed marketing and administrative costs to $1,500 per month and raise variable marketing and administrative costs to $7 per unit. d. Increase the cash discount from 5% to 10%. It is anticipated that this would increase the percentage of customers paying within the discount period to 85%, and those paying the month after the discount period would drop to 8%. Five percent would pay in the following month and 2% would still be uncollectible. A F "AS-BUD Cash Budgeting Data Section $14,000 $28.00 1,200 2,400 2,500 2,700 5% 50% 25% 19% 1 2 3 4 5. 6 7 8 a 1n 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 21 22. 23 24 25 36 37 28 29 an 41 42 43 44 45 46 47 48 49 Beginning cash balance, January 1 Sales: Unit sales price January estimated units February estimated units March estimated units April estimated units Collections: Cash discount (%) % Collected current month % Collected next month % Collected following month %Uncollectible Purchases: Unit purchase cost January planned purchases (units) February planned purchases (units) March planned purchases (units) April planned purchases (units) % Paid in month purchased % Paid in month after purchase Marketing & administrative: Fixed cash expenses per month Depreciation per month Variable cost per unit sold $12 2,400 2,600 2.500 3,100 60% $5,000 $2,000 $6 Answer Section Sweet Pleasures Inc. Cash Budget January FORMULA1 February SO March (S6,200) April ($6,630) FORMULA2 FORMULA FORMULA4 31,920 16,800 33,250 12,768 17,500 35,910 $59,548 SO $31,920 $43,850 Cash balance, beginning Add receipts: Collections January sales Collections February sales Collections March sales Collections April sales Total available Less disbursements: Purchases made in January Purchases made in February Purchases made in March Purchases made in April Fixed marketing & admin Variable marketing & admin. Total disbursements Cash balance, ending FORMULAS FORMULA6 18,720 $12,480 18,000 51 52 53 FORMULAZ FORMULAR SO SO 5,000 14,400 $38,120 (S6,200) 5,000 15,000 $50,480 (S6,630) $12,000 22,320 5,000 16,200 $55,520 $4,028 54 57 58 59 60 61 62 62 64 65 66 Data Section ash balance, January 1 14,000 price stimated units estimatec units timated units Taled units S28 00 1.200 2,400 2,500 2.700 count(") tec current month leu rex month Ico following month ectible 5" 50% 25% 19% 6 nasc cast planned purchases junts planned purchases (unts anned purchases (unilsi Enca purchascs (units) -month purchased month after purchase cu ministrative an cxpcnscs per month tion per month cost per unit sold 512 2,400 2,600 2.500 3.100 60% 10% 56 000 52,000 S6 Answer Santion Sweel Pleasures la Cash Rudget January $14,000 February SO t. Leyinning FORMULA2 FORMULA3 FO 0 ns.anuary sales ns Fcbruary sales ns Manch sales ns April sales le SO SO aments es made in January s made in February Worksheet Chart FORMULAS FORMULA6 0