PLEASE WRITE THE FORMULAS

A B D E F G H 1 J K L M 1 1 Cash Budget Problem One 2 You have been assigned the task of putting together a statement for the ACME Company 3 that shows its expected inflows and outflows of cash over the months of July 2016 through December 2016. 4 You have been given the following data for ACME Company: - 5 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. 7 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. 9 75% of the sales in any given month are collected during the following month after the sale. 11 13% of the sales in any given month are collected during the second month following the sale. 13 The expected purchases of raw materials in any given month are based on 60% of the expected sales during the following month. CAP 15 The firm pays 100% of its current month's raw materials purchases in the following month. 17 Wages and salaries are paid on a monthly basis and are based on 6% of the current month's expected sales. 19 Monthly lease payments are 2% of the current month's expected sales. 21 The monthly advertising expense amounts to 3% of sales. 23 20 24 R&D expenditures are expected to be allocated to August September, and October at the rate of 12% of sales in those months. 26 During December a prepayment of insurance for the following year will be made in the amount of $24,000. 28 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. 30 Taxes will be paid in September in the amount of $40,000 and in December in the amount of $45,000. 32 The beginning cash balance in July is $15,000. 34 The target cash balance is $15,000. 36 TO DO 37 a. Prepare a cash budget for July 2016 through December 2016 by creating a combined spreadsheet 38 that incorporates spreadsheets similar to those in Tables 4.8, 4.9, and 4. 10. Divide your spreadsheet into three sections: 39 1 Totalcash receipts 40 2 Totalcash disbursements 41 3 Cash budget covering the period of July through December 42 The cash budget should reflect the following: 43 1 Beginning and ending monthly cash balances 44 2 The required total financing in each month required 45 3 The excess cash balance in each month with excess 46 b. Based on your analysis, briefly describe the outlook for this company over the next 6 months. 47 Discuss its specific obligations and the funds available to meet them. 48 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? 49 50 50 ACME Company 51 31 Cash Budget July through December 2016 52 May June July August September October November December 53 Sales Forcasts (000's) 300 290 425 500 54 39 Cash Sales 12% 36 34,8 51 60 55 Less: Discount 3% 1,08 1,044 1,53) 1,8 56 30 Collections of A/R 57 Lagged one month 75% 225 217,5 318,75 58 Lagged two months 13% 39 37,7 59 3 Total Cash Receipts 34,92 258,756 305,97 414,65 60 61 0 Purchases (% of Sales) 60% 174 255 300 0 02 62 Cash Purchases 63 Payment of A/P 64 Lagged one month 100% 174 255 300 65 Wages 6% 18 174 25,5 30 66 00 2% Lease payments 6 5,8 8,5 10 67 Advertising Expense 3% 9 8,7 12,75 15 68 R&D Expenditures 12% 601 60 69 Prepaid Insurance 70 Other expenses 15 20 71 Taxes 72 Total Cash Disbursement 33 205,9) 316,75 435 73 74 Cash Budget: 75 Total Cash Receipts 305,97 414,65 76 Less: Total Cash Disbursements 316,75 435 77 New Cash Flow -10,78 -20.35 78 Add: Beginning Balance 15 4,22 79 Ending Balance 4,22 -16.13 80 Less: Minimum Cash Balance 15 15 81 Required totalfinancing (notes payable) -10,78 -31, 13 82 Excess Cash balance (marketable securities) 0 A B D E F G H 1 J K L M 1 1 Cash Budget Problem One 2 You have been assigned the task of putting together a statement for the ACME Company 3 that shows its expected inflows and outflows of cash over the months of July 2016 through December 2016. 4 You have been given the following data for ACME Company: - 5 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. 7 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. 9 75% of the sales in any given month are collected during the following month after the sale. 11 13% of the sales in any given month are collected during the second month following the sale. 13 The expected purchases of raw materials in any given month are based on 60% of the expected sales during the following month. CAP 15 The firm pays 100% of its current month's raw materials purchases in the following month. 17 Wages and salaries are paid on a monthly basis and are based on 6% of the current month's expected sales. 19 Monthly lease payments are 2% of the current month's expected sales. 21 The monthly advertising expense amounts to 3% of sales. 23 20 24 R&D expenditures are expected to be allocated to August September, and October at the rate of 12% of sales in those months. 26 During December a prepayment of insurance for the following year will be made in the amount of $24,000. 28 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. 30 Taxes will be paid in September in the amount of $40,000 and in December in the amount of $45,000. 32 The beginning cash balance in July is $15,000. 34 The target cash balance is $15,000. 36 TO DO 37 a. Prepare a cash budget for July 2016 through December 2016 by creating a combined spreadsheet 38 that incorporates spreadsheets similar to those in Tables 4.8, 4.9, and 4. 10. Divide your spreadsheet into three sections: 39 1 Totalcash receipts 40 2 Totalcash disbursements 41 3 Cash budget covering the period of July through December 42 The cash budget should reflect the following: 43 1 Beginning and ending monthly cash balances 44 2 The required total financing in each month required 45 3 The excess cash balance in each month with excess 46 b. Based on your analysis, briefly describe the outlook for this company over the next 6 months. 47 Discuss its specific obligations and the funds available to meet them. 48 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? 49 50 50 ACME Company 51 31 Cash Budget July through December 2016 52 May June July August September October November December 53 Sales Forcasts (000's) 300 290 425 500 54 39 Cash Sales 12% 36 34,8 51 60 55 Less: Discount 3% 1,08 1,044 1,53) 1,8 56 30 Collections of A/R 57 Lagged one month 75% 225 217,5 318,75 58 Lagged two months 13% 39 37,7 59 3 Total Cash Receipts 34,92 258,756 305,97 414,65 60 61 0 Purchases (% of Sales) 60% 174 255 300 0 02 62 Cash Purchases 63 Payment of A/P 64 Lagged one month 100% 174 255 300 65 Wages 6% 18 174 25,5 30 66 00 2% Lease payments 6 5,8 8,5 10 67 Advertising Expense 3% 9 8,7 12,75 15 68 R&D Expenditures 12% 601 60 69 Prepaid Insurance 70 Other expenses 15 20 71 Taxes 72 Total Cash Disbursement 33 205,9) 316,75 435 73 74 Cash Budget: 75 Total Cash Receipts 305,97 414,65 76 Less: Total Cash Disbursements 316,75 435 77 New Cash Flow -10,78 -20.35 78 Add: Beginning Balance 15 4,22 79 Ending Balance 4,22 -16.13 80 Less: Minimum Cash Balance 15 15 81 Required totalfinancing (notes payable) -10,78 -31, 13 82 Excess Cash balance (marketable securities) 0