You Have Been Assigned The Task Of Putting Together A Statement For The Acme Company That Shows Its Expected Inflows

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

You have been assigned the task of putting together a statement for the ACME Company that shows its expected inflows and outflows of cash over the months of July 2010 through December 2010.

You have been given the following data for ACME Company:

1. Expected gross sales for May through December, respectively, are $300,000, $290,000, $425,000, $500,000, $600,000, $625,000, $650,000, and $700,000.

2. 12% of the sales in any given month are collected during that month. However; the firm has a credit policy of 3/10 net 30, so factor a 3% discount into the current month's sales collection.

3. 75% of the sales in any given month are collected during the following month after the sale.

4. 13% of the sales in any given month are collected during the second month following the sale.

5. The expected purchases of raw materials in any given month are based on 60% of the expected sales during the following month.

6. The firm pays 100% of its current month's raw materials purchases in the following month.

7. Wages and salaries are paid on a monthly basis and are based on 6% of the current month's expected sales.

8. Monthly lease payments are 2% of the current month's expected sales.

9. The monthly advertising expense amounts to 3% of sales.

10. RScD expenditures are expected to be allocated to August, September, and October at the rate of 12% of sales in those months.

11. During December a prepayment of insurance for the following year will be made in the amount of $24,000.

12. During the months of July through December; the firm expects to have miscellaneous expenditures of $15,000, $20,000, $25,000, $30,000, $35,000, and $40,000, respectively.

13. Taxes will be paid in September in the amount of $40,000 and in December in the amount of $45,000.

14. The beginning cash balance in July is $15,000.

15. The target cash balance is $15,000.

16. The firm can invest its surplus cash to earn a 6% annual return. To Do a. Prepare a cash budget for July 2010 through December 2010 by creating a combined spreadsheet that incorporates spreadsheets similar to those in Tables 3.8, 3.9, and 3.10. Divide your spreadsheet into three sections:

(1) Collections from sales and payments to purchase inventory

(2) Operating expenditures over the time period

(3) Cash budget covering the period of July through December The cash budget should reflect the following:

(1) Beginning and ending monthly cash balances

(2) The month(s) in which there will be a cash deficit

(3) The month(s) which there will be a cash surplus . (4) The cumulative cash deficit and/or cash surplus b. Based on your analysis, briefly describe the outlook for this company over the next 6 months. Discuss its specific obligations and the funds available to meet them. What could the firm do in the case of a cash deficit? (Where could it get the money?) What should the firm do if it has a cash surplus?

Was this article helpful?

0 0
Stocks and Shares Retirement Rescue

Stocks and Shares Retirement Rescue

Get All The Support And Guidance You Need To Be A Success At Investing In Stocks And Shares. This Book Is One Of The Most Valuable Resources In The World When It Comes To

Get My Free Ebook

Post a comment