Spreadsheet Exercise: Chapter 4 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 2023 through December 2023. You have been given the following data for ACME Company: 1 Expected gross sales for May through December, respectively, are $300,000, S290,000, $425,000, 5500,000, $600,000, S625,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,5 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 R&D 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. To Do a. Prepare a cash budget for July 2023 through December 2023 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 sections: (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 (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 has a cash surplus? Solution a. Prepare a cash budget for July 2023 through December 2023 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 sections: (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 June July August September October 425.00$ 500.00 $ 600.00 $ 625.00 November December S 650.00 $ 700.00 290.00 $ Cash Budget July through December 2023 May Sales Forecasts (000's) $ 300.00 $ Cash Sales 12% Less: Discount 3% Collections of A/R Lagged one month 75% Lagged two months 13% Total Cash Receipts 60% 0% $ $ $ Purchases (% of Sales) Cash Purchases Payment of A/P Lagged one month Wages Lease Payments Advertising Expense 100% 6% 2% 3% 2% 3% 12% Lease Payments Advertising Expense R&D Expenditures Prepaid Insurance Other Expenses Taxes Total Cash Disbursements $ 15.00 $ 30.00 $ 20.00 $ $ 20.00 S 25.00 $ 40.00 65.00 S $ 35.00 $ S 35.00 S 24.00 40.00 45.00 109.00 $ $ $ 15.00 S 30.00 $ S $ $ $ 15.00 $ $ 20.00 $ $ 65.00 $ $ 35.00 $ 30.00 S 109.00 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) S 15.00 S 15.00 $ 15.00 $ 15.00 $ 15.00 $ 15.00 $ 15.00 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? ACME Corporation will need notes payable financing in months: ACME Corporation will invest in marketable securities in months: Points 8 Requirements 1 In cell range F46:M46, by using cell references to the given data, calculate the cash sales for the months of May thru December. Note: Be sure to use absolute cell reference where necessary to assist with the copy/paste function. 2 In cell range F47:M47, by using cell references to the given data, calculate the discount for the months of May thru December. Note: These values are expected as positive numbers which you will later subtract to compute the Total Cash Receipts. 8 7 3 In cell range G49:M49, by using cell references to the given data, calculate the collection of accounts receivable lagged one month for the months of June thru December. 7 6 8 7 3 In cell range G49:M49, by using cell references to the given data, calculate the collection of accounts receivable lagged one month for the months of June thru December. 4 In cell range H50:M50, by using cell references to the given data, calculate the collection of accounts receivable lagged two months for the months of May thru December. 5 In cell range F51:M51, by using cell references to the given data, calculate the total cash receipts for the months of May thru December. 6 In cell range F53:153, by using cell references to the given data, calculate the purchases for the months of May thru November. Note: Purchases are one month in advance of sales. 7 In cell range G56:M56, by using cell references to the values on row 53 and an absolute reference to cell E56, calculate the payment of A/P lagged one month for the months of May thru December 8 In cell range F57:M57, by using cell references to the given data, calculate the wages for the months of May thru December. 9 In cell range F58:M58, by using cell references to the given data, calculate the lease payments for the months of May thru December. 7 8 8 8 3 8 6 6 10 In cell range F59:M59, by using cell references to the given data, calculate the advertising expense for the months of May thru December. 11 In cell range 160:660, by using cell references to the given data, calculate the R&D expenditures for the months of August thru October. 12 In cell range F64:M64, by using cell references to the given data, calculate the total cash disbursements for the months of May thru December. 13 In cell range H69:M69, by using cell references to the given data, calculate the net cash flow for the months of July thru December. 14 In cell range H70:M70, by using cell references to the given data, calculate the beginning balance for the months of July thru December. Note: The beginning balance will be the closing balance from the previous month on row 71. The initial amounts will be at zero values until Step 18 is completed. 15 In cell range H71:M71, by using cell references to the given data, calculate the ending balance for the months of July thru December. 16 In cell range H73:M73, by using cell references to the given data and the Excel function IF, calculate the required total financing (notes payable) for the months of July thru December. For example, in cell H73, type=IF((H71-H72)0,H71-H72,""). 18 In cell range H78:M78, type only the names of the months in which ACME will need additional financing 6 6 6 19 In cell range H79:M79, type only the names of the months in which ACME will invest the excess cash available. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed