1 Cash Budgeting 2 3 Collections during month of sale 10% 4 Collections during 1st month after sale 75% 5 Collections during 2nd month after sale 15% 6 General and administrative salaries $24,000 7 Lease payments $8,000 8 Depreciation charges $32,000 9 Miscellaneous expenses $1,200 10 Income tax payments (Sep & Dec) $68,000 11 New design studio payment (Oct) $170,000 12 Cash on hand on July 1 $122,000 13 Minimum cash balance $95,000 14 Sales adjustment factor 0% 15 16 May 17 Sales 18 Costs of labor and raw materials 19 $150,000 $75,000 June $150,000 $75,000 July August September October November December $300,000 $450,000 $600,000 $300,000 $300,000 $90,000 $735,000 $255,000 $180,000 $120,000 $75,000 $75,000 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 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 51 b. Preparing monthly estimates of the required financing or excess funds 52 53 Required financing or excess funds 54 August September October November December July August September October November December 55 e. Doing a sensitivity analysis that shows the effects of the change in sales and the collection terms 56 Maximum loan required NA 57 58 Change in sales (B14) 59 60 61 -100% 62 -75% 63 -50% 64 -25% 65 0% 66 25% 67 50% 68 75% 69 100% 70 71 72 73 Maximum loan required 0% 15% 45% % Collections in 2nd month (B5) 30% 60% 75% 90% January $150,000