A 3 Collections during month of sale B D E F H 10% 4 Collections during 1st month after sale 75% 5 Collections during 2nd month after sale 15% 6 General and administrative salaries $33,000 7 Lease payments $11,000 8 Depreciation charges $44,000 9 Miscellaneous expenses $4,950 10 Income tax payments (Sep & Dec) $66,000 11 New design studio payment (Oc $180,000 12 Cash on hand on July 1 13 Minimum cash balance 14 Sales adjustment factor 15 16 17 Sales $124,000 $95,000 0% 18 Costs of labor and raw materials 19 May $160,000 $80,000 June $160,000 $80,000 July $320,000 $112,000 August September October November December $480,000 $640,000 $320,000 $320,000 $80,000 $736,000 $272,000 $192,000 $128,000 $80,000 January $160,00 20 a. Preparing a monthly cash budget for the last 6 months of 2021 21 The Cash Budget 22 23 Collections and purchases worksheet 24 Sales (gross) 25 Collections 26 During month of sale 27 During 1st month after sale 28 During 2nd month after sale 29 Total collections 30 Purchases 31 Labor and raw materials 32 Payments for labor and raw materials 33 34 Cash gain or loss for month 35 Collections 36 Payments for labor and raw materials 37 General and administrative salaries i 38 Lease payments 39 Miscellaneous expenses 40 Income tax payments 41 Design studio payment 42 Total payments 43 Net cash gain (loss) during month 44 45 Loan requirement or cash surplus 46 Cash at start of month 47 Cumulative cash 48 Target cash balance Cumulative surplus cash or loans outstanding to 49 maintain the target cash balance. 50 May June July August September October November December 50 51 b. Preparing monthly estimates of the required financing or excess funds 52 53 Required financing or excess funds 54 July August September October November December 35 e. Doing a sensitivity analysis that shows the effects of the change in sales and the collection terms 56 Maximum loan required WN/A $7 38 Maximum loan required Change in sales (B14) 59 60 0% 15% 45% % Collections in 2nd month (85) 30% 60% 75% 90% 61 -100% 62 -75% 63 -50% 64 -25% 65 0% 66 25% 67 50% 68 75% 69 100% 70 71 72 73