1. Business
2. Accounting
3. Financial Accounting & Bookkeeping
4. The Financial Statements
5. 23044

This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

CBM Cash Flow and Financing Requirements
The Cash Flow & Financing Requirements Summary

Assume: Cash on hand March 1 = 50,000 and a minimum cash balance of 50,000 shall be on hand at all times
THE "DUMMY DATA" SHOWS YOU HOW/WHERE TO GET THE CORRECT ANSWER from the bottom Spreadsheet.

March April May June July August Sept Oct Nov

Cash Balance at Start 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 Cash Balance at Start
of Month of Month

Net Cash Gain (Loss) Net Cash Gain (Loss)
During Month -129,888 -127,588 -31,488 -213,738 -16,388 -6,138 -36,638 -13,088 -26,338 During Month

Cash Balance at End Cash Balance at End
of Month Before of Month Before
Financing -79,888 -77,588 18,512 -163,738 33,612 43,862 13,362 36,912 23,662 Financing

Minimum Cash Minimum Cash
Balance Required 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 Balance Required

Surplus Cash (Deficit) Surplus Cash (Deficit)
(Cell17 - Cell20) -129,888 -127,588 -31,488 -213,738 -16,388 -6,138 -36,638 -13,088 -26,338 (Cell17 - Cell20)

External Financing External Financing
Balance at Start of Balance at Start of
Month 0 129,888 257,476 288,964 502,702 519,090 525,228 561,866 574,954 Month

New Financing Req New Financing Req
(IF neg. amnt Cell 23) 129,888 127,588 31,488 213,738 16,388 6,138 36,638 13,088 26,338 (IF neg. amnt Cell 23)

Financing Repaymnts Financing Repaymnts
(IF pos. amnt Cell23) 0 0 0 0 0 0 0 0 0 (IF pos. amnt Cell23)

External Financing External Financing
Balance at End of Month 129,888 257,476 288,964 502,702 519,090 525,228 561,866 574,954 601,292 Balance at End of Month

Cash Balance at End Cash Balance at End
of Month After of Month After
Financing Financing
sum(cell17, cell30, cell33) 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 sum(cell17, cell30, cell33)

You will note that there is no "Cumulative Column" Why?
Because this report shows rolling figures (Start and End Balance) from month to month
so Grand Totals (Cumulative) are of no real benefit

You'll also note that I have pasted in the Row Headings in Column A and B into Column L and M
This helps me stay on track by minimizing the amount of horizontal scrolling I have to do.

REMEMBER: The above is dummy data: Use the bottom "DATA" to plug into the "DUMMY DATA SHEET ABOVE TO GET THE CORRECT ANSWERS.
Accrual of sales and expenses

March April May June July August September October November

Sales $250,000 275,000 320,000 450,000 575,000 700,000 825,000 350,000 285,000

accrual of direct manufacturing costs( raw materials and labor) $187,500 206,250 240,000 337,500 431,250 525,000 618,750 262,500

Administrative salaries $35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000 35,000

lease payments $15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000

depreciation charge $15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000

New Plant investment $95,000

Income Tax $55,000 55,000

Miscellaneous cost $10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000

1) Cash flow from sales

Sales 100%
out of which 10.00% collected same month
75.00% collected next month
15.00% collected in the month after next

March April May June July August September October November
Sales $250,000 $275,000 $320,000 $450,000 $575,000 $700,000 $825,000 $350,000 $285,000

same month 10% 25,000 27,500 32,000 45,000 57,500 70,000 82,500 35,000 28,500
=10%*25000 =10%*275000 =10%*320000 =10%*450000 =10%*575000 =10%*700000 =10%*825000 =10%*350000 =10%*285000

next month 75% 187,500 206,250 240,000 337,500 431,250 525,000 618,750 262,500
=75%*250000 =75%*275000 =75%*320000 =75%*450000 =75%*575000 =75%*700000 =75%*825000 =75%*350000

month after next 15% 37,500 41,250 48,000 67,500 86,250 105,000 123,750
=15%*250000 =15%*275000 =15%*320000 =15%*450000 =15%*575000 =15%*700000 =15%*825000

Total collection 25,000 215,000 275,750 326,250 443,000 568,750 693,750 758,750 414,750
=25000 =27500+187500 =32000+206250+37500 =45000+240000+41250 =57500+337500+48000 =70000+431250+67500 =82500+525000+86250 =35000+618750+105000 =28500+262500+123750

2) Cash outflow from direct costs:

Payments for direct manufacturing costs like raw materials and labor are made during the month that follows the one in which such costs have been incurred.

March April May June July August September October November

direct manufacturing costs( raw materials and labor) $187,500 206,250 240,000 337,500 431,250 525,000 618,750 262,500

Payment= $187,500 206,250 240,000 337,500 431,250 525,000 618,750 262,500

3) Other cash flows:

All other cash flows will be in the month in which the costs are incured

Note :
depreciation is a non cash expense and hence has not been included in the cash budget
($35,000) indicates negative figure

Cash Budget

Sources of Cash March April May June July August September October November

Collection from sales 25,000 215,000 275,750 326,250 443,000 568,750 693,750 758,750 414,750

Total Sources of Cash 25,000 215,000 275,750 326,250 443,000 568,750 693,750 758,750 414,750

Uses of Cash

Payments for direct Manufacturing Costs $0 $187,500 $206,250 $240,000 $337,500 $431,250 $525,000 $618,750 $262,500

Administrative salaries $35,000 $35,000 $35,000 $35,000 $35,000 $35,000 $35,000 $35,000 $35,000

lease payments $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000 $15,000

New Plant investment $95,000

Income Tax $55,000 $55,000

Miscellaneous cost $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000

Total Uses of Cash $60,000 $247,500 $266,250 $450,000 $397,500 $491,250 $640,000 $678,750 $322,500

Sources - Uses= ($35,000) ($32,500) $9,500 ($123,750) $45,500 $77,500 $53,750 $80,000 $92,250

b. Prepare an estimate of the required financing needs (or excess funds) for each month during the budget period.

March April May June July August September October November
Sources - Uses= ($35,000) ($32,500) $9,500 ($123,750) $45,500 $77,500 $53,750 $80,000 $92,250

Cash at start of period $50,000 $50,000 $50,000 $50,000 $50,000 $50,000 $50,000 $50,000 $125,000

Change in cash balance during the month ($35,000) ($32,500) $9,500 ($123,750) $45,500 $77,500 $53,750 $80,000 $92,250

Cash at end of period $15,000 $17,500 $59,500 ($73,750) $95,500 $127,500 $103,750 $130,000 $217,250

Minimum cash balance at all times= $50,000 $50,000 $50,000 $50,000 $50,000 $50,000 $50,000 $50,000 $50,000
Loan repaid $0 $0 $9,500 $0 $45,500 $77,500 $53,750 $5,000 $0
Cumulative financing required $35,000 $67,500 $58,000 $181,750 $136,250 $58,750 $5,000 $0 $0
=-(-35000)- =35000-(-32500) =67500-(9500) =58000-(-123750) =181750-(45500) =136250-(77500) =58750-(53750)
Cash at the end of period after financing/paying back the loan $50,000 $50,000 $50,000 $50,000 $50,000 $50,000 $50,000 $125,000 $217,250

Excess cash (from the minimum requirement) after paying back the loan $0 $0 $0 $0 $0 $0 $0 $75,000 $167,250

c. Based on you findings in part b, will the company need any outside financing? What is the minimum line of credit that CBM will need?

The company will need outside financing

Month Cumulative Financing Required Maximum loan will be at the end of June= $181,750 =35000+67500+181750
March $35,000
April $67,500
May $58,000
June $181,750
July $136,250 The company can start repaying the loan from the excess cash generated after July
August $58,750
September $5,000
October $0
November $0

A line of credit sets a maximum amount of funds available from the bank, to be used when needed
Minimum line of credit= $181,750

d. What do you think of CBM's cash position during the budget period? Do you see any concerns for the company in this regard?

The company would require outside financing to the tune of $181,750
From the excess cash position we see that this loan can be completely repaid by the month of October

Since the company is generating a cash surplus from July onwards and is in a position to repay the loan there are no reasons for concern