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 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.

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