Hi There! looking to see if I can use the tutor credits I have to get the answers here :) Thanks!

Instructions: Using the company information provided below, complete the short-term cash flow forecast in following tab in this MS Excel Workbook: The background paper, Working Capital Management, provides useful guidance for completing this assignment. Company information: Arrow & Samuelson Company is a U.S.-based manufacturing company that produces a single product. Its fiscal year ends on December 31 Managers require a cash flow forecast for the three months July through September. The company's terms of sale are \"net 30.\" However, on average, customers' payments follow the pattern below: Month following month of sale Percent of sales collected in: Second Third month month following following Uncollecti month of month of ble (bad sale sale debts) 80 percent 15 percent 4 percent 1 percent Total 100 percent At June 30, the balance of the company's accounts receivable is $2,370,900. The company's VP-Sales and Marketing providing the following information about actual and forecast sales, in terms of units and revenues: April May June July August September - - - Units Actual 31,500 32,000 32,500 - - - Actual ### ### ### - - - Forecast - - - ### ### ### Forecast 38,000 39,000 40,000 Revenues Managers determined that the variable costs per unit of its product, set forth in the company's original operating budget for the fiscal year, are an appropriate basis for forecasting total variable product cost cash outflows. Variable costs Direct materials (DM) Unit cost Pattern of Payment $ 12.00 Paid in month of production (DM ordered and received in month prior to use in production) Direct labor (DL) 9.00 Paid in month following month of production Manufacturing overhead (MOH) 6.00 Paid in month following month of production Selling and administrative (S&A) 3.00 Paid in month following month of production Total $ 30.00 Managers also concluded that total fixed costs contained in the company's original operating budget for the year are an appropriate basis for forecasting fixed cost cash outflows for the July - September period. Fixed costs include fixed MOH, S&A, and research and developmen (R&D) costs and total $710,000 per month, excluding depreciation and amortization expense. Pursuant to its previously approved capital budget for the current year, the company will acquire replacement production equipment in September, costing $500,000 (the amount of the initial investment cash outflow). The company's controller provided the amount and dates of required estimated U.S. Federal and state income tax payments, based on an estimate of the company's taxable income for the current year. \"Estimated tax\" payments of $145,000 are due in September and December. At June 30, the balance of the company's total outstanding debt is $8,800,000, according to the company's general ledger. The forecast assumes that any incremental borrowing or debt repayments occur at the end of affected months. The weighted average annual interest rate on the company's total debt as of that date is 0.080 (i.e., 8.0 percent). At June 30, the balance of the company's cash and short-term investments is $580,100, according to the company's general ledger. Managers desire a balance of cash and highly liquid, short-term investments of $750,000, based on their historical experience with daily volatility of, and imbalances between, cash inflows and cash outflows. The facilitator will grade this assignment, assigning up to 100 points for it as follows Maximum Earned Accuracy, completeness, and clear presentation of short-term cash flow forecast, including related information input and computations Total points 100 100 points - Arrow & Samuelson Company Monthly Cash Forecast For the three-month period July - September 20X2 Sales: Units Actual (A) Current forecast (B) Revenue ($US) Actual Current forecast Apr May NA NA NA NA Jun Jul Aug Sep Total NA NA NA NA NA NA NA NA NA NA (C) Accounts receivable (AR) at June 30 Cash inflows Forecast collection of sales for month of: April (actual sales) May (actual sales) June (actual sales) July (forecast sales) August (forecast sales) September (forecast sales) (D) Total collections on sales $ - $ - $ - Cash outflows Variable cost (VC): Per unit Direct materials (DM) Direct labor (DL) Manufacturing overhead (MOH) (E) Selling and administrative (S&A) Total variable costs (F) Total fixed costs (FC), excluding depreciation (G) Interest on bank notes outstanding Computed at (S), below (H) Estimated income tax payments (I) Initial investments (refer to capital budget) (J) Net cash inflows (outflows) (K) (L) (M) (N) (O) (P) Cash and liquid, short-term investments Balance at beginning of month Balance at end of month before financing activities Desired balance Excess (deficiency) Applied to reduce outstanding short-term debt Incremental short-term borrowing required Total debt outstanding (Q) Beg.-of-month balance (R) Weighted-average annual interest rate on debt at June 30 (S) Forecast interest on debt (cash outflow) [Note 1] Note 1 - For greater ease of preparation, round computed interest amounts in each forecast interval to the nearest thou