# Cash Budget - ExcelModels

Document Sample

```					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
+      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
How are you planning on using Docstoc?