PLEASE WRITE THE FORMULAS

0 B D E G H L M N 1 Cash Budget Problem Two 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 4 You have been given the following data for ACME Company: 5 5 Expected gross sales for May through December, respectively, are $300,000, $350,000, $450,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 50% of the expected sales during the following month. 15 The fim 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 10% of the current month's expected sales. 20 The monthly advertising expense amounts to 5% of sales. 22 23 R&D expenditures are expected to be allocated to August, September, and October at the rate of 15% of sales in those months. 25 During December a prepayment of insurance for the following year will be made in the amount of $20,000. 27 During the months of July through December, the fim expects to have miscellaneous expenditures of $5,000, $15,000, $20,000, $25,000, $30,000, and $20,000, respectively. 29 Taxes will be paid in September in the amount of $30,000 and in December in the amount of $35,000. 31 The beginning cash balance in July is $15,000. 33 The target cash balance is $15,000. 35 TO DO 36 a. Prepare a cash budget for July 2016 through December 2016 by creating a combined spreadsheet 37 that incorporates spreadsheets similar to those in Tables 4.8, 4.9, and 4.10. Divide your spreadsheet into three sections: 38 1 Total cash receipts 39 2 Total cash disbursements 40 3 Cash budget covering the period of July through December 41 The cash budget should reflect the following: 42 1 Beginning and ending monthly cash balances 43 2 The required total financing in each month required 44 3 The excess cash balance in each month with excess 45 b. Based on your analysis, briefly describe the outlook for this company over the next 6 months. 46 Discuss its specific obligations and the funds available to meet them. 47 What could the fim 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? 48 49 50 Cash Budget July through December 2016 51 %'s June July August September October November December 52 Sales Forcasts (000's) 300 350 450 500 53 Cash Sales 12% 36 42 54 60 54 Less: Discount : 3% 1,08 1,26 1,62 1,8 55 Collections of A/R 56 Lagged one month 75% 225 262,5 337,5 57 Lagged two months 13% 39 45,51 ca 58 Total Cash Receipts 34,92 265,741 353,88 441,21 59 60 Purchases (% of Sales) 50% 175 225 2501 0 61 Cash Purchases 62 Payment of A/P 63 Lagged one month 100% 175 225 250 64 Wages 10% 30 351 45 50 65 Advertising Expense 5% 151 17,5 22,5 25 66 R&D Expenditures 15% 75 67 Prepaid Insurance 68 Other expenses 5 51 15 69 Taxes 70 Total Cash Disbursement 45 227,5 2975 415 71 72 Cash Budget 73 Total Cash Receipts 353,881 441,2 74 Less: Total Cash Disbursements 297,5 415 75 New Cash Flow 56,38 26,2 76 Add: Beginning Balance 15 71,38 77 Ending Balance 71,38 97,58 78 Less: Minimum Cash Balance 15 15 79 Required total financing (notes payable) 0 0 80 Excess Cash balance (marketable securities) 56,38 82,58 81 ACME Company May 0 B D E G H L M N 1 Cash Budget Problem Two 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 4 You have been given the following data for ACME Company: 5 5 Expected gross sales for May through December, respectively, are $300,000, $350,000, $450,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 50% of the expected sales during the following month. 15 The fim 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 10% of the current month's expected sales. 20 The monthly advertising expense amounts to 5% of sales. 22 23 R&D expenditures are expected to be allocated to August, September, and October at the rate of 15% of sales in those months. 25 During December a prepayment of insurance for the following year will be made in the amount of $20,000. 27 During the months of July through December, the fim expects to have miscellaneous expenditures of $5,000, $15,000, $20,000, $25,000, $30,000, and $20,000, respectively. 29 Taxes will be paid in September in the amount of $30,000 and in December in the amount of $35,000. 31 The beginning cash balance in July is $15,000. 33 The target cash balance is $15,000. 35 TO DO 36 a. Prepare a cash budget for July 2016 through December 2016 by creating a combined spreadsheet 37 that incorporates spreadsheets similar to those in Tables 4.8, 4.9, and 4.10. Divide your spreadsheet into three sections: 38 1 Total cash receipts 39 2 Total cash disbursements 40 3 Cash budget covering the period of July through December 41 The cash budget should reflect the following: 42 1 Beginning and ending monthly cash balances 43 2 The required total financing in each month required 44 3 The excess cash balance in each month with excess 45 b. Based on your analysis, briefly describe the outlook for this company over the next 6 months. 46 Discuss its specific obligations and the funds available to meet them. 47 What could the fim 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? 48 49 50 Cash Budget July through December 2016 51 %'s June July August September October November December 52 Sales Forcasts (000's) 300 350 450 500 53 Cash Sales 12% 36 42 54 60 54 Less: Discount : 3% 1,08 1,26 1,62 1,8 55 Collections of A/R 56 Lagged one month 75% 225 262,5 337,5 57 Lagged two months 13% 39 45,51 ca 58 Total Cash Receipts 34,92 265,741 353,88 441,21 59 60 Purchases (% of Sales) 50% 175 225 2501 0 61 Cash Purchases 62 Payment of A/P 63 Lagged one month 100% 175 225 250 64 Wages 10% 30 351 45 50 65 Advertising Expense 5% 151 17,5 22,5 25 66 R&D Expenditures 15% 75 67 Prepaid Insurance 68 Other expenses 5 51 15 69 Taxes 70 Total Cash Disbursement 45 227,5 2975 415 71 72 Cash Budget 73 Total Cash Receipts 353,881 441,2 74 Less: Total Cash Disbursements 297,5 415 75 New Cash Flow 56,38 26,2 76 Add: Beginning Balance 15 71,38 77 Ending Balance 71,38 97,58 78 Less: Minimum Cash Balance 15 15 79 Required total financing (notes payable) 0 0 80 Excess Cash balance (marketable securities) 56,38 82,58 81 ACME Company May