Finish filling in the Cash Budget Worksheet for Spring Valley.

You'll need this following information to complete:

Depreciation charges amounted to $3,000 per month, as did miscellaneous cash expenses.On the horizon, however, was a special outlay for pollution control equipment that would be necessitated by new regulations soon to be imposed by the Wabash Valley Watershed Authority on emissions from sawmill operations.These would call for the installation of a woodchip recycling facility costing$80,000 in mid?November.Terms from the supplier were net, 30 days. Depreciation on the equipment would come to approximately$2,000 per month, starting with November. In December, the company planned to retire from itsbooks a fully depreciated de?barking crib which had originally cost $30,000, and which was no longer used in the firm's operations.As matter of policy, Mr. Firr felt that SPRIVORPCO should maintaina minimum cash balance of $50,000in its account for normal transactions needs.His brother, Douglas, who was the loan officer at Tippecanoe Trust Company (TIPTCO), indicated that this sum would satisfy the bank's desires for compensating balances.A cash dividendof $20,000 to common stockholders was contemplated for December.Mr. Firr hoped to be able to make the payment, since he regarded it as valuable "seed money" for attracting possible new equity capital to the business in the future.On the basis of operating results for the first six months of 2002, acharge of 75 percent of sales as cost of goods sold seemed appropriate for the second half of the year in drawing up any pro forma income statements.Traditionally, cost of goods sold for SPRIVORPCO encompassedonly timber purchases and laborcosts. Other expenses, like those for sales and administration, were separated out in the financial statements.

Attached is a partly completed excel document. I"ll also provide the word document with more details that includes the above information.

SPRING VALLEY FOREST PRODUCTS CORPORATION Upon returning from his annual two-week vacation in early July of 2002, the treasurer of the Spring Valley Forest Products Corporation, a Mr. Fred Firr, found the firm's audited balance sheet as of June 30 on his desk. Close scrutiny of the company's financial condition as reported in this document suggested to Mr. Firr that the cash flow picture for the enterprise was deteriorating. In times gone by, the firm had been able to maintain sizeable cash balances in its bank of account, Tippecanoe Trust Company, during the major portion of the fiscal year, and had found only modest seasonal borrowings necessary. Recently, however, a lengthening of credit terms to customers necessitated by intense competition in the company's dominant product lines had increased working capital needs quite substantially. Simultaneously, lower selling prices occasioned by the same competition had diminished profit margins. For these reasons, Mr. Firr-who had starred as a quarterback in high school, felt that a careful forecast of funds needs for the remainder of 2002 was in order. Spring Valley Forest Products (SPRIVORPCO) produced plywood paneling and moldings both for lumber wholesalers and major contractors in the north central Indiana area. The verdant Wabash River Valley had proven capable of supporting the lush growths of timber necessary to the company's production activities. Most lumber, however, was purchased from independent farmers and lumbermen rather than grown by the company itself. Raw material costs, therefore, were relatively high in relation to sales. The firm's products, marketed under the brand name SPRIV, enjoyed an unvarnished reputation for quality. In making his forecasts for the period July 1 through December 31, 2002, Mr. Firr estimated that the pattern of monthly sales was likely to be of the form: July $120,000 August 100,000 September 60,000 October 60,000 November 140,000 December 200,000 The bulge in sales in November and December was attributable in part to a large order that had just been placed by a regional building-supply chain for pre-finished interior paneling designed for the home handyman market. Shipment had been agreed upon for the latter part of the year. Of the total sales indicated, only 10 percent were expected to be for cash. Collections on the remainder were anticipated generally within 60 days of sale. In particular, recent experience had suggested that roughly one-half of credit sales were collected for during the month following the month of sale and that other one-half during the next subsequent month. Mr. Firr intended to use this pattern as the basis for his calculations. SPRIVORPCO'S woodlot management group had entered into commitments with various timber growers in anticipation of the sales expected. Specifically, they planned to purchase hardwood timber in bulk according to the following schedule: July August $50,000 50,000 September October 70,000 40,000 November December 30,000 30,000 The cash payments for these deliveries would occur, in all cases, one month later. Labor costs were forecast at the same level as purchases, and were expected to follow an identical month-by-month pattern. Because of the need to pay wages weekly, however, the cash outlays for labor costs ordinarily took place in the same month the costs were incurred, rather than with a time lag. The company's general, selling, and administrative expenses had been running at a rate of $10,000 per month during early 2002, and were predicted to continue at that scale in the near future. The associated cash payments, like those for labor costs typically occurred with no appreciable time delay. Depreciation charges amounted to $3,000 per month, as did miscellaneous cash expenses. On the horizon, however, was a special outlay for pollution control equipment that would be necessitated by new regulations soon to be imposed by the Wabash Valley Watershed Authority on emissions from sawmill operations. These would call for the installation of a woodchip recycling facility costing $80,000 in mid-November. Terms from the supplier were net, 30 days. Depreciation on the equipment would come to approximately $2,000 per month, starting with November. In December, the company planned to retire from its books a fully depreciated de-barking crib which had originally cost $30,000, and which was no longer used in the firm's operations. As matter of policy, Mr. Firr felt that SPRIVORPCO should maintain a minimum cash balance of $50,000 in its account for normal transactions needs. His brother, Douglas, who was the loan officer at Tippecanoe Trust Company (TIPTCO), indicated that this sum would satisfy the bank's desires for compensating balances. A cash dividend of $20,000 to common stockholders was contemplated for December. Mr. Firr hoped to be able to make the payment, since he regarded it as valuable "seed money" for attracting possible new equity capital to the business in the future. On the basis of operating results for the first six months of 2002, a charge of 75 percent of sales as cost of goods sold seemed appropriate for the second half of the year in drawing up any pro forma income statements. Traditionally, cost of goods sold for SPRIVORPCO encompassed only timber purchases and labor costs. Other expenses, like those for sales and administration, were separated out in the financial statements. In the event, SPRIVORPCO turned out to require a loan greater than its existing bank was willing to provide, Mr. Firr was confident he could obtain additional financing from a larger metropolitan bank with whom he had recently made certain preliminary contacts. That bank, Tulane Liberty Security (TULIBS), did a considerable volume of business with firms in the northern Indiana area already and seemed generally sympathetic to Mr. Firr's financial plight. Hence, he felt that, whatever the cash forecast showed, he could arrange the requisite loans by supplementing the support of TIPTCO through the TULIBS bank. Assignment: 1. Prepare a Pro Forma Income Statement for Spring Valley Forest Products for the interval, July 1, 2002 December 2002. 2. Prepare a Pro Forma Balance Sheet for December 31. 3. Prepare a Cash Budget for the month of December. 4. Derive, from the statements above, a Source-and-Use of Funds Statement for the full period 7-1-02 to 12-31-02. Assume the relevant corporate income tax rate to be 50%. Spring Valley Forest Products Balance Sheet: June 30, 2002 Cash Accounts Receivable Inventories Plant and Equipment Less: Accumulated Depreciation Net Plant and Equipment Other Assets $65,000 140,000 180,000 300,000 (75,000) 225,000 60,000 Total Assets $670,000 Accounts Payable Tax Accruals1 Miscellaneous Accruals Common Stock Retained Earnings 60,000 30,000 70,000 150,000 360,000 Total Liabilities and Owners Equity $670,000 1 Arising from income during the first six months of the year. Payable in full on September 15, 2002. Taxes accrued during the last half of 2002 will be payable on March 15, 2002. ProForma Worksheet Spring Valley Forest Products DO NOT CHANGE THIS DATA: MAKE YOUR CHANGES IN THE DATA SECTION OF THE CASH BUDGET WORKSHEET DATA TABLE Collections @ t=0 Cash 10.00% @ t=1 % of credit sale 50.0% @ t=2 % of credit sale 50.0% Payments @ t=0 Cash 0.0% @ t=1 % AP 100.0% S&A 10,000.00 Misc 3,000.00 Fixed Asset 80,000.00 Min Cash 50,000.00 Dividend 20,000.00 COGS 75% Depr $3,000.00 Depr New $2,000.00 Tax 50.0% Spring Valley Forest Products Balance Sheet - June 30 July 120,000 50,000 Spring Valley Forest Products Pro Forma Balance Sheet - Decembe Cash Accts Receivable Inventory 65,000 140,000 180,000 Cash Accts Receivable Inventory Plant And Equip Acc. Depr. Net Plant and Equipment Other 300,000 (75,000) 225,000 60,000 Plant And Equip Acc. Depr. Net Plant and Equipment Other Total Assets Accounts Payable 670,000 60,000 Page 1 Total Assets Accounts Payable ProForma Worksheet Tax Accruals Misc. Accruals Common Stock Retained Earnings Total Liab. & Equity 30,000 70,000 Tax Accruals Misc. Accruals 150,000 360,000 Common Stock Retained Earnings 670,000 Total Liab. & Equity External Funds Needed Total Liab. & Equity Page 2 ProForma Worksheet Aug Sept Oct Nov 100,000 60,000 60,000 140,000 50,000 70,000 40,000 30,000 Forest Products alance Sheet - December 31 Equipment Dec 200,000 SALES 30,000 Purchases 243000 but this way wouldn't change because It isn't 50,000 243,000 210,000 (1) min cash (2) 45% of nov sales + 90% o dec sales (3) end=beg+pur+labor-cogs 350,000 (67,000) 283,000 60,000 (4) end=beg+pur - writeoffs - sales (5) end = beg + depr expense - write offs (6) no change 846,000 30,000 (7) *1- amount paid this period times the purchases Page 3 ds Needed ProForma Worksheet 35,000 70,000 (8) from the income statemtn (9) no change 150,000 375,000 (11) no change (12) beg RE + NI - Devidends 660,000 186,000 846,000 EFN (13) assets - liabilit & equity Page 4 ProForma Worksheet putstanding 50% nov 50% dec 50% dec uldn't change because It isn't linked to data e purchases Page 5 ProForma Worksheet Page 6 A 1 2 B C D E F Spring Valley Forest Products Cash Budget 7/1 to 12/31 G H I J Name: A#: 3 DATA TABLE 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 Collections @ t=0 Cash @ t=1 % of credit sale @ t=2 % of credit sale Payments @ t=0 Cash @ t=1 % AP S &A Misc Fixed Asset Min Cash Dividend tax Month Sales Every blank cell in this problem must be filled in with a formula that ties to the data table OR to the numbers already in the cash budget. You should be able to input any new number in the data table and watch the EFN number correctly change. For example, if you changed the the cash collection to 15%, and the credit collections to 60% and 40% respectively, will your spread sheet give the correct EFN? 0.0% 100.0% $10,000.00 $3,000.00 $80,000.00 $50,000.00 $20,000.00 50% July You assignment will be to use the spreadsheet to answer questions on Canvas August September October November December 120,000 100,000 60,000 60,000 140,000 200,000 12,000 50,000 40,000 10,000 60,000 50,000 6,000 50,000 60,000 6,000 30,000 50,000 14,000 30,000 30,000 20,000 70,000 30,000 102,000 120,000 116,000 86,000 74,000 120,000 50,000 50,000 70,000 40,000 30,000 30,000 60,000 50,000 10,000 3,000 60,000 50,000 50,000 10,000 3,000 50,000 50,000 70,000 10,000 3,000 30,000 70,000 40,000 10,000 3,000 30,000 40,000 30,000 10,000 3,000 70,000 30,000 30,000 10,000 3,000 100,000 183,000 163,000 163,000 153,000 153,000 173,000 Receipts: Cash (10%) Collection t+1 (50%) Collection T+2 (50%) Total Receipts Purchases - given Receipt Schedule Disbursements: Purchases Paid in Month t = 0 Purchases Paid in Month t = 1 Labor S &A Misc Tax Dividend Fixed Assets Total Disbursements Disbursement Schedule Net Receipts 48 49 50 51 52 Beginning Cash 53 54 55 56 57 Loan Addition (Reduction) 58 59 60 61 62 63 Instructions: 10.00% 50.0% 50.0% Collection t=1 4 End of Month Cash Before Borrowing Cash Budget div = dec new asset = dec Ending Cash Cumulative Loan *=the other sheet efn =External Funds Needed 160600