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: 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 (C) Accounts receivable (AR) at June 30 Apr May Jun 31,500
NA 32,000
NA 32,500
NA ###
NA ###
NA Jul
NA
38,000 ###
NA NA
### Aug Sep NA
39,000 NA
40,000 NA
### NA
117,000
NA
### ### Cash inflows
Forecast collection of sales for month of:
April (actual sales)
May (actual sales)
June (actual sales)
July (forecast sales) 283,591
359,851
338,953 284,262
53,479
101,053 August (forecast sales)
September (forecast sales)
(D) NA
### Total 485,364
86,534
447,443
354,724 Total collections on sales ### 438,794 ### ###
91,829
458,581
187,701
173,616
419,451
### 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
Attachments:
Answers
Status NEW
Posted 20 Jun 2017 01:06 AM
My Price 20.00
-----------
Attachments
file 1497922112-Solutions file.docx preview (56 words )
S-----------olu-----------tio-----------ns -----------fil-----------e
-----------Hel-----------lo -----------Sir-----------/Ma-----------dam-----------
T-----------han-----------k y-----------ou -----------for----------- yo-----------ur -----------int-----------ere-----------st -----------and----------- bu-----------yin-----------g m-----------y p-----------ost-----------ed -----------sol-----------uti-----------on.----------- Pl-----------eas-----------e p-----------ing----------- me----------- on----------- ch-----------at -----------I a-----------m o-----------nli-----------ne -----------or -----------inb-----------ox -----------me -----------a m-----------ess-----------age----------- I -----------wil-----------l b-----------e q-----------uic-----------kly----------- on-----------lin-----------e a-----------nd -----------giv-----------e y-----------ou -----------exa-----------ct -----------fil-----------e a-----------nd -----------the----------- sa-----------me -----------fil-----------e i-----------s a-----------lso----------- se-----------nt -----------to -----------you-----------r e-----------mai-----------l t-----------hat----------- is----------- re-----------gis-----------ter-----------ed -----------on-----------th-----------is -----------web-----------sit-----------e.
-----------
H-----------YPE-----------RLI-----------NK -----------&qu-----------ot;-----------htt-----------p:/-----------/wo-----------rkb-----------ank-----------247-----------.co-----------m/&-----------quo-----------t; -----------\t -----------&qu-----------ot;-----------_bl-----------ank-----------&qu-----------ot;-----------
-----------Tha-----------nk -----------you-----------
-----------
Not Rated(0)