Please fill them up all sheets. thank you.

There are 4 sheets on the excel file

Cash Budget Problem One 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 2016 through December 2016. You have been given the following data for ACME Company: Expected gross sales for May through December, respectively, are $300,000, $290,000, $425,000, $500,000, $600,000 12% of the sales in any given month are collected during that month. However, the firm has a credit policy of 3/10 ne 75% of the sales in any given month are collected during the following month after the sale. 13% of the sales in any given month are collected during the second month following the sale. The expected purchases of raw materials in any given month are based on 60% of the expected sales during the follow The firm pays 100% of its current month's raw materials purchases in the following month. Wages and salaries are paid on a monthly basis and are based on 6% of the current month's expected sales. Monthly lease payments are 2% of the current month's expected sales. The monthly advertising expense amounts to 3% of sales. R&D expenditures are expected to be allocated to August, September, and October at the rate of 12% of sales in thos During December a prepayment of insurance for the following year will be made in the amount of $24,000. During the months of July through December, the firm expects to have miscellaneous expenditures of $15,000, $20,0 Taxes will be paid in September in the amount of $40,000 and in December in the amount of $45,000. The beginning cash balance in July is $15,000. The target cash balance is $15,000. TO DO a. Prepare a cash budget for July 2016 through December 2016 by creating a combined spreadsheet that incorporates spreadsheets similar to those in Tables 4.8, 4.9, and 4.10. Divide your spreadsheet into three sectio 1 Total cash receipts 2 Total cash disbursements 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 required total financing in each month required 3 The excess cash balance in each month with excess 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 ha Sales Forcasts (000's) Cash Sales Less: Discount Collections of A/R Lagged one month Lagged two months Total Cash Receipts Purchases (% of Sales) Cash Purchases Payment of A/P 12% 3% ACME Company Cash Budget July through December 2016 May June July August 300 290 425 500 36 34.8 51 60 1.08 1.044 1.53 1.8 75% 13% 60% 225 34.92 258.756 217.5 39 305.97 174 255 300 318.75 37.7 414.65 0 Lagged one month Wages Lease payments Advertising Expense R&D Expenditures Prepaid Insurance Other expenses Taxes Total Cash Disbursement 100% 6% 2% 3% 12% Cash Budget: Total Cash Receipts Less: Total Cash Disbursements New Cash Flow Add: Beginning Balance Ending Balance Less: Minimum Cash Balance Required total financing (notes payable) Excess Cash balance (marketable securities) 18 6 9 33 174 17.4 5.8 8.7 205.9 255 25.5 8.5 12.75 300 30 10 15 60 15 20 316.75 435 305.97 316.75 -10.78 15 4.22 15 -10.78 0 414.65 435 -20.35 4.22 -16.13 15 -31.13 0 One through December 2016. 000, $425,000, $500,000, $600,000, $625,000, $650,000, and $700,000. e firm has a credit policy of 3/10 net 30, so factor a 3% discount into the current month's sales collection. ng the sale. the expected sales during the following month. t month's expected sales. r at the rate of 12% of sales in those months. n the amount of $24,000. ous expenditures of $15,000, $20,000, $25,000, $30,000, $35,000, and $40,000, respectively. amount of $45,000. ed spreadsheet your spreadsheet into three sections: t 6 months. ey?) What should the firm do if it has a cash surplus? ember 2016 September October November December Cash Budget Problem Two 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 2016 through December 2016. You have been given the following data for ACME Company: Expected gross sales for May through December, respectively, are $300,000, $350,000, $450,000, $500,000, $600,000 12% of the sales in any given month are collected during that month. However, the firm has a credit policy of 3/10 ne 75% of the sales in any given month are collected during the following month after the sale. 13% of the sales in any given month are collected during the second month following the sale. The expected purchases of raw materials in any given month are based on 50% of the expected sales during the follow The firm pays 100% of its current month's raw materials purchases in the following month. Wages and salaries are paid on a monthly basis and are based on 10% of the current month's expected sales. The monthly advertising expense amounts to 5% of sales. R&D expenditures are expected to be allocated to August, September, and October at the rate of 15% of sales in thos During December a prepayment of insurance for the following year will be made in the amount of $20,000. During the months of July through December, the firm expects to have miscellaneous expenditures of $5,000, $15,00 Taxes will be paid in September in the amount of $30,000 and in December in the amount of $35,000. The beginning cash balance in July is $15,000. The target cash balance is $15,000. TO DO a. Prepare a cash budget for July 2016 through December 2016 by creating a combined spreadsheet that incorporates spreadsheets similar to those in Tables 4.8, 4.9, and 4.10. Divide your spreadsheet into three sectio 1 Total cash receipts 2 Total cash disbursements 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 required total financing in each month required 3 The excess cash balance in each month with excess 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 ha %'s Sales Forcasts (000's) Cash Sales Less: Discount Collections of A/R Lagged one month Lagged two months Total Cash Receipts Purchases (% of Sales) Cash Purchases Payment of A/P Lagged one month 12% 3% ACME Company Cash Budget July through December 2016 May June July August 300 350 450 500 36 42 54 60 1.08 1.26 1.62 1.8 75% 13% 50% 100% 225 34.92 265.74 262.5 39 353.88 337.5 45.5 441.2 175 225 250 0 175 225 250 Wages Advertising Expense R&D Expenditures Prepaid Insurance Other expenses Taxes Total Cash Disbursement 10% 5% 15% Cash Budget: Total Cash Receipts Less: Total Cash Disbursements New Cash Flow Add: Beginning Balance Ending Balance Less: Minimum Cash Balance Required total financing (notes payable) Excess Cash balance (marketable securities) 30 15 45 35 17.5 227.5 45 22.5 50 25 75 5 15 297.5 415 353.88 297.5 56.38 15 71.38 15 0 56.38 441.2 415 26.2 71.38 97.58 15 0 82.58 Two through December 2016. 000, $450,000, $500,000, $600,000, $625,000, $650,000, and $700,000. e firm has a credit policy of 3/10 net 30, so factor a 3% discount into the current month's sales collection. ng the sale. the expected sales during the following month. nt month's expected sales. r at the rate of 15% of sales in those months. n the amount of $20,000. ous expenditures of $5,000, $15,000, $20,000, $25,000, $30,000, and $20,000, respectively. amount of $35,000. ed spreadsheet your spreadsheet into three sections: t 6 months. ey?) What should the firm do if it has a cash surplus? ember 2016 September October November December Year 1 2 3 4 5 6 7 8 9 10 11 MACRS 10 Year Cycle Rate Base Amount Depreciation 10% 18% 14.40% 11.50% 9.20% 7.40% 6.60% 6.60% 6.50% 6.50% 3.30% 100% $425,000 $425,000 $425,000 $425,000 $425,000 $425,000 $425,000 $425,000 $425,000 $425,000 $425,000 Year 1 2 3 4 33% 45% 15.00% 7.00% Total Depreciation 100% MACRS 5 Year Cycle Year 1 2 3 4 5 6 Rate 20% 32% 19.00% 12.00% 12.00% 5.00% 100% Base Amount $425,000 $425,000 $425,000 $425,000 $425,000 $425,000 Total Depreciation MACRS 3 Year Cycle Rate Base Amount $425,000 $425,000 $425,000 $425,000 Total Depreciation MACRS 7 Year Cycle Depreciation Year 1 2 3 4 5 6 7 8 Rate Base Amount $425,000 $425,000 $425,000 $425,000 $425,000 $425,000 $425,000 $425,000 Total Depreciation ar Cycle Depreciation Michael Nugent: You can find the MACRS in your textbook ar Cycle Depreciation Pro Forma Income Statement Example 1 2015 Actual 2016 ProForma $100,000.0 $135,000.0 Sales Revenue Less Cost Of Goods Sold Fixed Cost Variable Cost Gross Profits Less Operating Expenses Fixed Cost Variable Cost Operating Profits Less Interest Expense Net Profit Before Taxes Less Taxes Net Profits After Taxes 40% 5% 0.15 $40,000.0 $40,000.0 $20,000.0 $40,000.0 $5,000.0 $5,000.0 $10,000.0 $1,000.0 $9,000.0 $1,350.0 $7,650.0 $5,000.0 #VALUE! $1,000.0 Pro Forma Income Statement Example 2 2015 Actual 2016 ProForma $325,000.0 $550,000.0 Sales Revenue Less Cost Of Goods Sold Fixed Cost Variable Cost Gross Profits Less Operating Expenses Fixed Cost Variable Cost Operating Profits Less Interest Expense Net Profit Before Taxes Less Taxes Net Profits After Taxes 40% 5% 0.15 $50,000.0 $130,000.0 $145,000.0 $50,000.0 $15,000.0 $16,250.0 $113,750.0 $5,000.0 $108,750.0 $16,312.5 $92,437.5 $15,000.0 $5,000.0