Explore BrainMass
Share

Explore BrainMass

    To solve this problem, one must plug Data into MS Excel Spread Sheet from lower Spread Sheet for Answers

    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

    © BrainMass Inc. brainmass.com October 9, 2019, 4:07 pm ad1c9bdddf
    https://brainmass.com/business/financial-statements/23044

    Attachments

    Solution Preview

    I have converted the word document to Excel and have put in the formulas in the table at the top. You can click on any cell and you will be able to see the relationship witht the rest of ...

    $2.19