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: |
Percent of sales collected in:
| Month following month of sale | Second month following month of sale | Third month following month of sale | Uncollectible (bad debts) | Total |
| 80 percent | 15 percent | 4 percent | 1 percent | 1 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 | – | – | – |
| Forecast | – | – | – | 38,000 | 39,000 | 40,000 |
| REVENUES | ||||||
| Actual | ###### | ###### | ###### | – | – | – |
| Forecast | – | – | – | ###### | ###### | ###### |
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 | Unit cost | Pattern of Payment |
| Direct materials (DM) | $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 | Paid in month following month of production |
| 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 development (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 | 100 | points | |
| Total points | 100 |
For solution towards the above problem email : help@d2qud7ra4nbef7.cloudfront.net or chat with us live.
