Do work in Excel: Here is example from class:

Given the following information: Assume today is June 30, May June July Sales (in $200 $250 $300 millions) August September October November December $400 $500 $350 $250 $200 Wages Rent Other Expenses $30 $15 $10 $40 $15 $15 $50 $15 $20 $40 $15 $15 $30 $15 $10 $30 $15 $10 All sales are made on terms that allow a 2 percent cash discount for payments made within 10 days, and, if the discount is not taken, the full amount is due in 40 days. However, like most companies, your company finds that some of your customers delay payment up to 90 days. Experience has shown that payment on 20 percent of the dollar sales is made during the month in which the sale is made - these are the discount sales. On 70 percent of sales, payment is made during the month immediately following the month of sale, and payment is made on 10 percent of sales in the second month following the month of sale. The cost of your company's materials average 70 percent of the sales prices of the finished products. These purchases are generally made one month before your firm expects to sell the finished products, but your company's purchase terms with its suppliers allow it to delay payments for 30 days. Accordingly, if July sales are forecasted at $300 million, then purchases during June will amount to $210 million, and this amount will actually be paid in July. Your company must make estimated tax payments of $30 million on September 15 and $20 million on December 15, while a $100 million payment for a new plant must be made in October Determine: Your firm's monthly cash surpluses or (shortfalls) for the next six months (July, August, September, October, November, and December). Use the format demonstrated in the class example. TT Select Draw with Eraser Lasso Touch Select Tools Add Pen - Ink to Ink to Shape Math Convert Pens A2 fir D E F G H I J K L M . N o Q R $3,780 A B 13 Payments 14 1st Month (10%) 15 2 nd Month (90%) 16 Total Payments 17 $4,007 $34,020 $4,675 $4,675 $5,342 $6,010 $6,678 $7,346 $36,061 $42,071 $42,071 $48,082 $54,092 $60,102 $40,736 $46,746 $47,414 $54,092 $60,770 $67,448 L 18 19 Jan Feb Mar Apr $65,184 $69,741 $76,797 $81,249 May June $91,266 $102,396 Total Receipts W 20 21 22 23 24 25 26 27 28 29 30 Payments Purchases $40,736 $46,746 $47,414 $54,092 $60,770 $67,448 Labor Expense $8,014 $9,349 $9,349 $10,685 $12,020 $13,356 Selling and Admin $6,678 $7,791 $7,791 $8,904 $10,017 $11,130 Working Capital Inc. $942 $943 $942 $943 $942 $943 Fixed Asset Acquisition $14,625 $14,625 Interest Expense $1,855 $1,855 $1,855 $1,855 $1,855 $1,855 Taxes $8,904 $12,020 Dividends $11,875 $11,875 Total Payments $58,225 $66,684 $93,851 $85,383 $85,604 $133,252 Bold Estimates are given You would have to identify these estimates in real life. 31 32 33 34 Net Cash Flow $6,959 $3,057 -$17,054 -$4,134 $5,662 -$30,856 Worksheet Cash budget Given the following information: Assume today is June 30, May June July Sales (in $200 $250 $300 millions) August September October November December $400 $500 $350 $250 $200 Wages Rent Other Expenses $30 $15 $10 $40 $15 $15 $50 $15 $20 $40 $15 $15 $30 $15 $10 $30 $15 $10 All sales are made on terms that allow a 2 percent cash discount for payments made within 10 days, and, if the discount is not taken, the full amount is due in 40 days. However, like most companies, your company finds that some of your customers delay payment up to 90 days. Experience has shown that payment on 20 percent of the dollar sales is made during the month in which the sale is made - these are the discount sales. On 70 percent of sales, payment is made during the month immediately following the month of sale, and payment is made on 10 percent of sales in the second month following the month of sale. The cost of your company's materials average 70 percent of the sales prices of the finished products. These purchases are generally made one month before your firm expects to sell the finished products, but your company's purchase terms with its suppliers allow it to delay payments for 30 days. Accordingly, if July sales are forecasted at $300 million, then purchases during June will amount to $210 million, and this amount will actually be paid in July. Your company must make estimated tax payments of $30 million on September 15 and $20 million on December 15, while a $100 million payment for a new plant must be made in October Determine: Your firm's monthly cash surpluses or (shortfalls) for the next six months (July, August, September, October, November, and December). Use the format demonstrated in the class example. TT Select Draw with Eraser Lasso Touch Select Tools Add Pen - Ink to Ink to Shape Math Convert Pens A2 fir D E F G H I J K L M . N o Q R $3,780 A B 13 Payments 14 1st Month (10%) 15 2 nd Month (90%) 16 Total Payments 17 $4,007 $34,020 $4,675 $4,675 $5,342 $6,010 $6,678 $7,346 $36,061 $42,071 $42,071 $48,082 $54,092 $60,102 $40,736 $46,746 $47,414 $54,092 $60,770 $67,448 L 18 19 Jan Feb Mar Apr $65,184 $69,741 $76,797 $81,249 May June $91,266 $102,396 Total Receipts W 20 21 22 23 24 25 26 27 28 29 30 Payments Purchases $40,736 $46,746 $47,414 $54,092 $60,770 $67,448 Labor Expense $8,014 $9,349 $9,349 $10,685 $12,020 $13,356 Selling and Admin $6,678 $7,791 $7,791 $8,904 $10,017 $11,130 Working Capital Inc. $942 $943 $942 $943 $942 $943 Fixed Asset Acquisition $14,625 $14,625 Interest Expense $1,855 $1,855 $1,855 $1,855 $1,855 $1,855 Taxes $8,904 $12,020 Dividends $11,875 $11,875 Total Payments $58,225 $66,684 $93,851 $85,383 $85,604 $133,252 Bold Estimates are given You would have to identify these estimates in real life. 31 32 33 34 Net Cash Flow $6,959 $3,057 -$17,054 -$4,134 $5,662 -$30,856 Worksheet Cash budget