Step 36 of 40: Cash Budget Formulas A 1 2 Cash Balance, Beginning 3 ollections 1 5 YOUR TURN: Fill in January- 6 December and the word Recap 7 along with your Spreadsheet 8 ID in cell N1 on the Cash 9 Budget worksheet 10 January 2 Cash Balance, Beginning $20.000.00 Collections 3.Cash Avallable YOUR TURN: Enter 7 formulas for cells B3:13. 5 YOUR TURN: Create formulas that carry over the operating expense names in column A and uso VLOOKUP to return the amounts in cells 89:M11. 7 vericory POCUSES 73090 8 Commissions 12000 9 10 11 Cash Budget Cash Budget B YOUR TURN. Create formulas that carry over the inventory payments and commissions in cells B7:M8. 1 2 Cash Balance, Beginning 3 Collections 5 2 YOUR TURN; Enter a formula 6 L here that references the 7 Minimum Cash Balance from 8 the Inputs worksheet. 9 10 6 Less Disbursements Inventory Purchases 8 Commissions mimo Supporting Schedule: B el 13 Total Disbursements 19 xcess (deficiency) of receipover disburs (6 16 YOUR TURN: Add a formulas 17 for subtotals and ending balances for columns B:M, as 18 well as beginning balances for 20 columns C:M. 21 22 Cash Balance, Ending Cash Budget 10 Cash Budget + Cash Budget - BAZE BTUEA-$+% 247 re to 6 Conditional loans Food LE My Math 70.000 20 08 00 000 TE DET tone HE DOS 1 Total Ave tem LOSOS S 01 12. 5.2.000.000 TL 1 SOS CHOOT 10.000.000 3 ws Slow WODOS OS OOO!!! LOGO 1 1 10. 7 he 10 O How to SANDO TE O Tallinn D Math All May Aura AP Art Income Sales Total income Cost of Goods Sold 110,000 20 1.00 100 CO SEE 1000 ARE 120.000 12000 000 11.000 ERO 1000 112.00 135.000 1000 1 110.000 13 O 140.000 06 13.000 1,6 non 1. 12.00 (1210 IRO O AO 112 1267001 300 11.3.100 12.00 122.0001 100 07.0 1160 Total Cost of Good * Gross Profit Operating Expenses 1.400 112.000 (2 50.700 LO LOVY 0.00 ISO 100 51.100 1000 00 114 17 5.00 016 1.000 TO 59,100 15.00 191.00 39,500 S10 100 5,500 $2,800 1992 1,300 COETS 13.00 000 11600 16. 11.00 too 116.000 0.000 50 103,1001 XXI O 14.000 000 119.500 15001 7,000 11.00 142.500 400 Other Total Operating pances 1 Net Income 19 30 100T 116.000 32000 110.500 142 500 1200 16.000 1200 OG 142 SOD 10.000 II 7.000 119.500 10250 11.NO 11 7.2003 SOO 2,500 13.000 loos 1.COM 7,600 9000 142,5001 16.00 1150 142.500 34200 42.100) 9460 To 1 SOOS 19.500 13.700 42,0 15,00 00 . 100 135.300 COES WA 0001 10 HI M M COM M 333 19 WP Dec Pay M W WM 5400 37.40.000.00 100 20.00 March May 100 27-1 2.10.00 17,00 GREE 11 Od ww De taly DOS 0.00 000 67. 700.00 15.000 354000 1.000.000 TE ] www Mar LOT SITE on RE OS MEET rect so 10500 60 120.000 100.000 10 200 11.00 ino IL 11.50 11 BOB 12 om 12.00 6.00 11,000 15 1.000.000 DO! 13. 11 11,00 341,000.0 14 One ANE 2 0 NE SON NG sou ST NOS 1 My NE 21 234 SON 25 SON SON Oct 85 2 ber 29 50 Dec MY AU 5 Super Odci ter December 23tions February Marc 10000 DO 10.000.00 0.000000 LA February OOSTE 30.10.00 63,000.00 0.00 1.500.00 M 11.000.00 170.00 56,000.00 115000 wa 3.000.00 10.00 1000 DO VE DODOS Y 00000 WWE 000SC Agust Mette 1.790.00 10.000.000.000,00 TL 50.00 Cash Budget naratinn Rudnet D 1 Budget Assumptions nt Month's Sales in Units 3 Unt Sales Growth Per Month 4 Unit Sales Price 10,000 250 12.00 LEFT/FIND Functions First Month's Sales in Units Assumption Unit Sales Growth Pet Month Assumption Unit Sales Price Assumption $ 7 Gross Margin a Commission Ending inventory SON 10 BON THUM Function Gross Martin Commission Ending Inventory 11 12 Minimum Cash Balance 13 interest Rate s 20,000.00 12N SUBSTITUTE Fonction Minimum Cash Balance Assumption Interest Rate Assumption Collections 25 50 25 Payments 6ON LON ON CONCATENATE/RIGHT Function Periodi Period 2 Period 3 UPPER Function LOWER Function PERIOD period PERIOD 2 period 2 PERIOD 3 perioda 15 Month 16 Month 17 Month Ta Mantha 29 Co Operating Expenses Payroll 2. Utilities Other 1 Per Month S 16,000 S 7.000,0 S 19,500.0 MID Function 7200 Payroll 7500. Utilities 7900. Other Step 36 of 40: Cash Budget Formulas A 1 2 Cash Balance, Beginning 3 ollections 1 5 YOUR TURN: Fill in January- 6 December and the word Recap 7 along with your Spreadsheet 8 ID in cell N1 on the Cash 9 Budget worksheet 10 January 2 Cash Balance, Beginning $20.000.00 Collections 3.Cash Avallable YOUR TURN: Enter 7 formulas for cells B3:13. 5 YOUR TURN: Create formulas that carry over the operating expense names in column A and uso VLOOKUP to return the amounts in cells 89:M11. 7 vericory POCUSES 73090 8 Commissions 12000 9 10 11 Cash Budget Cash Budget B YOUR TURN. Create formulas that carry over the inventory payments and commissions in cells B7:M8. 1 2 Cash Balance, Beginning 3 Collections 5 2 YOUR TURN; Enter a formula 6 L here that references the 7 Minimum Cash Balance from 8 the Inputs worksheet. 9 10 6 Less Disbursements Inventory Purchases 8 Commissions mimo Supporting Schedule: B el 13 Total Disbursements 19 xcess (deficiency) of receipover disburs (6 16 YOUR TURN: Add a formulas 17 for subtotals and ending balances for columns B:M, as 18 well as beginning balances for 20 columns C:M. 21 22 Cash Balance, Ending Cash Budget 10 Cash Budget + Cash Budget - BAZE BTUEA-$+% 247 re to 6 Conditional loans Food LE My Math 70.000 20 08 00 000 TE DET tone HE DOS 1 Total Ave tem LOSOS S 01 12. 5.2.000.000 TL 1 SOS CHOOT 10.000.000 3 ws Slow WODOS OS OOO!!! LOGO 1 1 10. 7 he 10 O How to SANDO TE O Tallinn D Math All May Aura AP Art Income Sales Total income Cost of Goods Sold 110,000 20 1.00 100 CO SEE 1000 ARE 120.000 12000 000 11.000 ERO 1000 112.00 135.000 1000 1 110.000 13 O 140.000 06 13.000 1,6 non 1. 12.00 (1210 IRO O AO 112 1267001 300 11.3.100 12.00 122.0001 100 07.0 1160 Total Cost of Good * Gross Profit Operating Expenses 1.400 112.000 (2 50.700 LO LOVY 0.00 ISO 100 51.100 1000 00 114 17 5.00 016 1.000 TO 59,100 15.00 191.00 39,500 S10 100 5,500 $2,800 1992 1,300 COETS 13.00 000 11600 16. 11.00 too 116.000 0.000 50 103,1001 XXI O 14.000 000 119.500 15001 7,000 11.00 142.500 400 Other Total Operating pances 1 Net Income 19 30 100T 116.000 32000 110.500 142 500 1200 16.000 1200 OG 142 SOD 10.000 II 7.000 119.500 10250 11.NO 11 7.2003 SOO 2,500 13.000 loos 1.COM 7,600 9000 142,5001 16.00 1150 142.500 34200 42.100) 9460 To 1 SOOS 19.500 13.700 42,0 15,00 00 . 100 135.300 COES WA 0001 10 HI M M COM M 333 19 WP Dec Pay M W WM 5400 37.40.000.00 100 20.00 March May 100 27-1 2.10.00 17,00 GREE 11 Od ww De taly DOS 0.00 000 67. 700.00 15.000 354000 1.000.000 TE ] www Mar LOT SITE on RE OS MEET rect so 10500 60 120.000 100.000 10 200 11.00 ino IL 11.50 11 BOB 12 om 12.00 6.00 11,000 15 1.000.000 DO! 13. 11 11,00 341,000.0 14 One ANE 2 0 NE SON NG sou ST NOS 1 My NE 21 234 SON 25 SON SON Oct 85 2 ber 29 50 Dec MY AU 5 Super Odci ter December 23tions February Marc 10000 DO 10.000.00 0.000000 LA February OOSTE 30.10.00 63,000.00 0.00 1.500.00 M 11.000.00 170.00 56,000.00 115000 wa 3.000.00 10.00 1000 DO VE DODOS Y 00000 WWE 000SC Agust Mette 1.790.00 10.000.000.000,00 TL 50.00 Cash Budget naratinn Rudnet D 1 Budget Assumptions nt Month's Sales in Units 3 Unt Sales Growth Per Month 4 Unit Sales Price 10,000 250 12.00 LEFT/FIND Functions First Month's Sales in Units Assumption Unit Sales Growth Pet Month Assumption Unit Sales Price Assumption $ 7 Gross Margin a Commission Ending inventory SON 10 BON THUM Function Gross Martin Commission Ending Inventory 11 12 Minimum Cash Balance 13 interest Rate s 20,000.00 12N SUBSTITUTE Fonction Minimum Cash Balance Assumption Interest Rate Assumption Collections 25 50 25 Payments 6ON LON ON CONCATENATE/RIGHT Function Periodi Period 2 Period 3 UPPER Function LOWER Function PERIOD period PERIOD 2 period 2 PERIOD 3 perioda 15 Month 16 Month 17 Month Ta Mantha 29 Co Operating Expenses Payroll 2. Utilities Other 1 Per Month S 16,000 S 7.000,0 S 19,500.0 MID Function 7200 Payroll 7500. Utilities 7900. Other
