Working Capital Management

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 saleSecond month following month of saleThird month following month of saleUncollectible (bad debts)Total
80 percent15 percent4 percent1 percent1 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:

AprilMayJuneJulyAugustSeptember
UNITS
Actual31,50032,00032,500
Forecast38,00039,00040,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 CostsUnit cost Pattern of Payment
Direct materials (DM)$12.00Paid in month of production (DM ordered and received in month prior to use in production)
Direct labor (DL)$9.00Paid in month following month of production
Manufacturing overhead (MOH)$ 6.00Paid in month following month of production
Selling and administrative (S&A)$3.00Paid in month following month of production
Total$30.00Paid 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:  MaximumEarned
  Accuracy, completeness, and clear presentation of short-term cash flow forecast,  including related information input and computations 100points 
 Total points  100

For solution towards the above problem email : help@assignmentstore.com or chat with us live.