Docstoc

Cash Budget - ExcelModels

Document Sample
Cash Budget - ExcelModels Powered By Docstoc
					Chapter 3: The Cash Budget

Introduction
               CASH IS KING- which is why we do this
               Lists anticipated cash flows over a period of time
               Indicates when a company needs to borrow cash
               Uses the other financial statements to calculate

Composed of 3 parts
          Worksheet area
          List of cash inflows and outflows
          Calculation of ending balance and borrowing needs

From Inflows and Outflows
           Payments
                        Not all are in cash
                        Some lag into subsequent months
           Purchases
                        Not all are in cash
                        Sometimes depend on sales projections for future months

From Financial Planning
           All major costs (when summed, will tie to income statement)

From Balances and Borrowing
           "CFO stuff"
           Follows management's decision on required minimum cash
           Ending Cash Balance =
                       Beginning Cash Balance
                +      Total Collections
                -      Total Disbursements
                =      Unadjusted Cash Balance
                +      Current Borrowing
                -      Current Lending
                =      Ending Cash Balance

Current Borrowing
            May need to borrow to maintain minimum cash balance set by management
            "May need"… this question IS answered with the IF STATEMENT

IF
               One of the most important functions in Excel.
               A Logical Test is something with a TRUE or FALSE answer
               IF (Logical Test, Value if True, Value if False)
               IF (Ending Cash is not equal to target, Borrow to target, Lend to target)

Another use for the Cash Budget
            "What if?" analysis to try different assumptions for large expenditures
            Helps determine optimal timing based on answers

Scenario Manager
           Benefits
                         Don't have to keep changing values and copying new sheets ("by hand")
                         Can recalculate a worksheet quickly
                         Can summarize results
            Usage
                       Tools - Scenarios to get dialogue box
                       Name and change values for each scenario and click "Add"
                       Jump around by using "Show" to implement a scenario
            Summarizing
                       Define a name for your labels with Insert - Name - Define
                       Select Tools - Scenarios - Summary
                       Result Cell is the Total Borrowing formula

Cash Shortfall
            Need to borrow cash to reach minimum required

MAX and MIN functions
         Take the greater (with the MAX function) or lesser (with the MIN function) of the numbers separated by commas

Formula Auditing
           Tools - Formula Auditing - Trace Precedents lets you see which cells go into the formula in the selected cell
           Tools - Formula Auditing - Trace Dependents lets you see which cells the selected cell's value goes into
           Tools - Formula Auditing - Trace Error is like Trace Precedents for your cell if it contains an error value
           Tools- Formula Auditing - Remove All Arrows cleans up the auditing messiness
ed by commas
                                           Bithlo Barbeques
                                              Cash Budget
                                 For the Period June to September 2010
                                  April     May        June     July     August September
Sales                            291,000 365,000 387,000 329,000         238,000  145,000

Collections:
   Cash                  40%     116,400   146,000   154,800   131,600    95,200     58,000
   30 Day Terms          45%               130,950   164,250   174,150   148,050    107,100
   60 Day Terms          15%                          43,650    54,750    58,050     49,350
Total Collections                                    362,700   360,500   301,300    214,450

Purchases                50%     182,500   193,500   164,500   119,000    72,500     46,000

Payments:
   30 Day Terms          60%               109,500   116,100    98,700    71,400     43,500
   60 Day Terms          40%                          73,000    77,400    65,800     47,600
Total Payments                                       189,100   176,100   137,200     91,100

Collections                                          362,700   360,500   301,300    214,450
Less Disbursements:
   Inventory Payments                                189,100 176,100     137,200     91,100
   Wages                   20%                        77,400   65,800     47,600     29,000
   Lease Payment                                      10,000   10,000     10,000     10,000
   Interest on L/T Loans                              30,000        0          0     30,000
   Dividend (Common)                                  50,000        0          0          0
   Taxes                                              25,000        0          0     25,000
   Capital Outlays                                         0 200,000           0          0
   LOC Interest (Income)                                   0       92        702         (2)
Total Disbursements                                  381,500 451,992     195,502    185,098
Beginning Cash Balance                                20,000   15,000     15,000     15,000
Collections - Disbursement                           (18,800) (91,492)   105,798     29,352
Unadjusted Cash Balance                                1,200  (76,492)   120,798     44,352
   Current Borrowing                                  13,800   91,492          0          0
   Current Lending                                         0        0    105,798     29,352
Ending Cash Balance                         20,000    15,000   15,000     15,000     15,000
Cumulative Borrowing                             0    13,800 105,292        (506)   (29,858)

Notes:
Minimum Balance                   15,000
Line of Credit Rate                  8%      0.67%
Money Market Rate                     5%     0.42%
October   Operations Planning- "Inflows and Outflows"
 92,000   Forecasted by management



 36,800   Bithlo extends credit to customers
 65,250   Credit often extends to subsequent months (30-day terms)
 35,700   Credit sometimes extends to 60-day terms for good customers
137,750

          Purchases are made based on next month's forecasted sales



 27,600   Just like collections, Bithlo receives credit from suppliers
 29,000   Bithlo gets 60-day terms from its closest suppliers
 56,600
          Financial Planning
          Taken from above

          Taken from above
          Assumed to be percent of sales
          Fixed cost
          Predetermined by agreement with lender
          Predetermined dividend paid to shareholders
          Terms set by IRS and GAAP
          Predetermined "Investing Activities" by management
          Driven by Borrowing against the Line of Credit
          Balances and Borrowing
          Taken from end of last period
          Collections minus Disbursements
          Beginning + Collections (can be negative)
          Test to see whether minimum cash needs are met
          Test to see whether money is available to earn S/T interest
          "Cash is King"
          Borrowed money retained for future



          Set by management
          Required by the bank
          Offered by the bank
                                            Bithlo Barbeques
                                               Cash Budget
                                  For the Period June to September 2010
                                  April     May        June     July      August    September
Sales                            291,000 365,000 387,000 329,000          238,000      145,000
Collections:
   Cash                  40%     116,400   146,000   154,800   131,600     95,200      58,000
   30 Day Terms          45%               130,950   164,250   174,150    148,050     107,100
   60 Day Terms          15%                          43,650    54,750     58,050      49,350
Total Collections                                    362,700   360,500    301,300     214,450

Purchases                50%     182,500   193,500   164,500   119,000     72,500       46,000
Payments:
   30 Day Terms          60%               109,500   116,100    98,700     71,400       43,500
   60 Day Terms          40%                          73,000    77,400     65,800       47,600
Total Payments                                       189,100   176,100    137,200       91,100

Collections                                          362,700   360,500    301,300     214,450
Less Disbursements:
   Inventory Payments                               189,100    176,100    137,200      91,100
   Wages                   20%                       77,400     65,800     47,600      29,000
   Lease Payment                                     10,000     10,000     10,000      10,000
   Interest on L/T Loans                             30,000          0          0      30,000
   Dividend (Common)                                 50,000          0          0           0
   Taxes                                             25,000          0          0      25,000
   Capital Outlays                                  200,000          0          0           0
   LOC Interest (Income)                                  0      1,425        711           6
Total Disbursements                                 581,500    253,325    195,511     185,106
Beginning Cash Balance                               20,000     15,000     15,000      15,000
Collections - Disbursement                         (218,800)   107,175    105,789      29,344
Unadjusted Cash Balance                            (198,800)   122,175    120,789      44,344
   Current Borrowing                                213,800          0          0           0
   Current Lending                                        0    107,175    105,789      29,344
Ending Cash Balance                         20,000   15,000     15,000     15,000      15,000
Cumulative Borrowing                             0 213,800     106,625       836      (28,508)

Notes:
Target Cash Balance               15,000
Line of Credit Rate                  8%      0.67%
Money Market Rate                     5%     0.42%
October
 92,000

 36,800
 65,250
 35,700
137,750




  2,142 Total Interest Paid (Earned)
                                           Bithlo Barbeques
                                              Cash Budget
                                 For the Period June to September 2010
                                  April     May       June      July        August     September
Sales                            291,000 365,000 387,000 329,000            238,000       145,000
Collections:
   Cash                  40%     116,400   146,000   154,800    131,600      95,200       58,000
   30 Day Terms          45%               130,950   164,250    174,150     148,050      107,100
   60 Day Terms          15%                          43,650     54,750      58,050       49,350
Total Collections                                    362,700    360,500     301,300      214,450

Purchases                50%     182,500   193,500   164,500    119,000      72,500        46,000
Payments:
   30 Day Terms          60%               109,500   116,100     98,700      71,400        43,500
   60 Day Terms          40%                          73,000     77,400      65,800        47,600
Total Payments                                       189,100    176,100     137,200        91,100

Collections                                          362,700    360,500     301,300      214,450
Less Disbursements:
   Inventory Payments                                189,100    176,100     137,200       91,100
   Wages                   20%                        77,400      65,800     47,600       29,000
   Lease Payment                                      10,000      10,000     10,000       10,000
   Interest on L/T Loans                              30,000           0          0       30,000
   Dividend (Common)                                  50,000           0          0             0
   Taxes                                              25,000           0          0       25,000
   Capital Outlays                                         0    200,000           0             0
   LOC Interest (Income)                                   0          92        702            (2)
Total Disbursements                                  381,500    451,992     195,502      185,098
Beginning Cash Balance                                20,000      15,000     15,000       15,000
Collections - Disbursement                           (18,800)    (91,492)   105,798       29,352
Unadjusted Cash Balance                                1,200     (76,492)   120,798       44,352
   Current Borrowing                                  13,800      91,492          0             0
   Current Lending                                         0           0    105,798       29,352
Ending Cash Balance                         20,000    15,000      15,000     15,000       15,000
Cumulative Borrowing                             0    13,800    105,292        (506)     (29,858)

Notes:
Target Cash Balance               15,000
Line of Credit Rate                  8%      0.67%
Money Market Rate                     5%     0.42%
October
 92,000

 36,800
 65,250
 35,700
137,750




  $792 Total Interest Paid (Earned)
                                           Bithlo Barbeques
                                              Cash Budget
                                 For the Period June to September 2010
                                  April     May       June      July       August     September
Sales                            291,000 365,000 387,000 329,000           238,000       145,000
Collections:
   Cash                  40%     116,400   146,000   154,800    131,600     95,200       58,000
   30 Day Terms          45%               130,950   164,250    174,150    148,050      107,100
   60 Day Terms          15%                          43,650     54,750     58,050       49,350
Total Collections                                    362,700    360,500    301,300      214,450

Purchases                50%     182,500   193,500   164,500    119,000     72,500        46,000
Payments:
   30 Day Terms          60%               109,500   116,100     98,700     71,400        43,500
   60 Day Terms          40%                          73,000     77,400     65,800        47,600
Total Payments                                       189,100    176,100    137,200        91,100

Collections                                          362,700    360,500    301,300      214,450
Less Disbursements:
   Inventory Payments                                189,100    176,100    137,200       91,100
   Wages                   20%                        77,400     65,800     47,600       29,000
   Lease Payment                                      10,000     10,000     10,000       10,000
   Interest on L/T Loans                              30,000          0          0       30,000
   Dividend (Common)                                  50,000          0          0             0
   Taxes                                              25,000          0          0       25,000
   Capital Outlays                                         0          0    200,000             0
   LOC Interest (Income)                                   0         92       (395)           (7)
Total Disbursements                                  381,500    251,992    394,405      185,093
Beginning Cash Balance                                20,000     15,000     15,000       15,000
Collections - Disbursement                           (18,800)   108,508    (93,105)      29,357
Unadjusted Cash Balance                                1,200    123,508    (78,105)      44,357
   Current Borrowing                                  13,800          0     93,105             0
   Current Lending                                         0    108,508          0       29,357
Ending Cash Balance                         20,000    15,000     15,000     15,000       15,000
Cumulative Borrowing                             0    13,800    (94,708)    (1,603)     (30,959)

Notes:
Target Cash Balance               15,000
Line of Credit Rate                  8%      0.67%
Money Market Rate                     5%     0.42%
October
 92,000

 36,800
 65,250
 35,700
137,750




   (309) Total Interest Paid (Earned)
                                            Bithlo Barbeques
                                               Cash Budget
                                  For the Period June to September 2010
                                  April     May        June     July       August      September
Sales                            291,000 365,000 387,000 329,000           238,000        145,000
Collections:
   Cash                  40%     116,400   146,000   154,800    131,600      95,200       58,000
   30 Day Terms          45%               130,950   164,250    174,150     148,050      107,100
   60 Day Terms          15%                          43,650     54,750      58,050       49,350
Total Collections                                    362,700    360,500     301,300      214,450

Purchases                50%     182,500   193,500   164,500    119,000      72,500        46,000
Payments:
   30 Day Terms          60%               109,500   116,100     98,700      71,400        43,500
   60 Day Terms          40%                          73,000     77,400      65,800        47,600
Total Payments                                       189,100    176,100     137,200        91,100

Collections                                          362,700    360,500     301,300      214,450
Less Disbursements:
   Inventory Payments                                189,100    176,100     137,200        91,100
   Wages                   20%                        77,400     65,800      47,600        29,000
   Lease Payment                                      10,000     10,000      10,000        10,000
   Interest on L/T Loans                              30,000          0           0        30,000
   Dividend (Common)                                  50,000          0           0             0
   Taxes                                              25,000          0           0        25,000
   Capital Outlays                                         0          0           0       200,000
   LOC Interest (Income)                                   0         92        (395)         (840)
Total Disbursements                                  381,500    251,992     194,405       384,260
Beginning Cash Balance                                20,000     15,000      15,000        15,000
Collections - Disbursement                           (18,800)   108,508     106,895      (169,810)
Unadjusted Cash Balance                                1,200    123,508     121,895      (154,810)
   Current Borrowing                                  13,800          0           0       169,810
   Current Lending                                         0    108,508     106,895             0
Ending Cash Balance                         20,000    15,000     15,000      15,000        15,000
Cumulative Borrowing                             0    13,800    (94,708)   (201,603)      (31,793)

Notes:
Target Cash Balance               15,000
Line of Credit Rate                  8%      0.67%
Money Market Rate                     5%     0.42%
October
 92,000

 36,800
 65,250
 35,700
137,750




           Right-click and select "Name a Range" to give cell I36 a label




 (1,143) Total Interest Paid (Earned)
Scenario Summary
                                         Current Values:       June Outlay   July Outlay
Changing Cells:
            $E$26                                    0            200,000           0
            $F$26                                    0                  0     200,000
            $G$26                                    0                  0           0
            $H$26                             200,000                   0           0
Result Cells:
            Interest_Paid_or_Earned             (1,143)             2,142         792
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
August Outlay   September Outlay

         0                   0
         0                   0
   200,000                   0
         0             200,000

       (309)             (1,143)

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:8
posted:8/9/2012
language:English
pages:16