1 Cash Budgeting 2 3 Collections during month of sale 4 Collections during 1st month after sale 5 Collections during 2nd month after sale 6 General and administrative salaries 7 Lease payments 8 Depreciation charges 19 Miscellaneous expenses 10 Income tax payments (Sep & Dec) 11 New design studio payment (Oct) 12 Cash on hand on July 1 13 Minimum cash balance 14 Sales adjustment factor 15 16 17 Sales 18 Costs of labor and raw materials 19 B 10% 75% 15% $33,000 $11,000 $44,000 $4,950 $66,000 $180,000 $124,000 $95,000 0% May June July $160,000 $160,000 $320,000 $80,000 $80,000 $112,000 G August September October November $480,000 $640,000 $320,000 $320,000 $736.000 $272,000 $192,000 $128,000 13 14 15 16 December 17 $80,000 18 $80,000 19 January $160,000 A B C 20 a. Preparing a monthly cash budget for the last 6 months of 2021 21 The Cash Budget 22 May June 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 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 10 D July E F August September G October H November B C D E F b. Preparing monthly estimates of the required financing or excess funds July August September Required financing or excess funds e. Doing a sensitivity analysis that shows the effects of the change in sales and the collection terms Maximum loan required #N/A Change in sales (B14) Maximum loan required % Collections in 2nd month (B5) 45% 0% 30% 60% -100% -75% -50% -25% 0% 25% 50% 75% 100% 15% G H October November 75% 90% 1 Cash Budgeting 2 3 Collections during month of sale 4 Collections during 1st month after sale 5 Collections during 2nd month after sale 6 General and administrative salaries 7 Lease payments 8 Depreciation charges 19 Miscellaneous expenses 10 Income tax payments (Sep & Dec) 11 New design studio payment (Oct) 12 Cash on hand on July 1 13 Minimum cash balance 14 Sales adjustment factor 15 16 17 Sales 18 Costs of labor and raw materials 19 B 10% 75% 15% $33,000 $11,000 $44,000 $4,950 $66,000 $180,000 $124,000 $95,000 0% May June July $160,000 $160,000 $320,000 $80,000 $80,000 $112,000 G August September October November $480,000 $640,000 $320,000 $320,000 $736.000 $272,000 $192,000 $128,000 13 14 15 16 December 17 $80,000 18 $80,000 19 January $160,000 A B C 20 a. Preparing a monthly cash budget for the last 6 months of 2021 21 The Cash Budget 22 May June 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 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 10 D July E F August September G October H November B C D E F b. Preparing monthly estimates of the required financing or excess funds July August September Required financing or excess funds e. Doing a sensitivity analysis that shows the effects of the change in sales and the collection terms Maximum loan required #N/A Change in sales (B14) Maximum loan required % Collections in 2nd month (B5) 45% 0% 30% 60% -100% -75% -50% -25% 0% 25% 50% 75% 100% 15% G H October November 75% 90%