Please answer the following question in EXCEL.
Given the following information: Assume today is June 30. May June July Sales (in $200 $250 $300 millions) August September October $400 $500 $350 November December $250 $200 $50 Wages Rent Other Expenses $30 $15 $10 $40 $15 $15 $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. Given the following information: Assume today is June 30. May June July Sales (in $200 $250 $300 millions) August September October $400 $500 $350 November December $250 $200 $50 Wages Rent Other Expenses $30 $15 $10 $40 $15 $15 $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
