Docstoc

8861228799593Excel_Assignment_Question-_4.3

Document Sample
8861228799593Excel_Assignment_Question-_4.3 Powered By Docstoc
					                                                         Huggins and Griffin Financial Planners
                                                                       Cash Budget
                                                           For the Period January to June 2008
                                         November December      January       February
Sales                                       48,000     45,000        25,000         27,000
Collections:
   Cash                           60%                     27,000       15,000       16,200
   First Month                    39%                     17,550        9,750       10,530
   Second Month                   -1%                       (450)        (250)        (270)
Total Collections                                         44,100       24,500       26,460
Less Dispersements:
   Wages:Clerical                                                       7,000        7,000
   Commisions                     25%                                  11,025        6,125
   Huggins Salary                                                       9,000        5,000
          Minimum Salary         5000                                   5,000        5,000
          Sales Percentage        20%                                   9,000        5,000
   Griffin Salary                                                       9,000        5,000
          Minimum Salary         5000                                   5,000        5,000
          Sales Percentage        20%                                   9,000        5,000
   Rent Expense                                                         2,500        2,500
   Lease for Office Equip.                                                800          800
   Utilities                                                              175          175
Total Dispersements                                                    39,500       26,600
Beginning Cash Balance                                                 12,000        8,000
Collections - Dispersments                                            (15,000)        (140)
Unadjusted Cash Balance                                   12,000       (3,000)       7,860
Current Borrowing                                                      11,000          140
Ending Cash Balance                                       12,000        8,000        8,000
Notes:
Minimum Acceptable Cash          8,000



  Huggins and Griffin Financial Planners were able to recoupe funds being
borrowed and did not have to borrow funds following February of this year.
Because of this, they do not need to open a line of credit with their bank as
                sufficent cash flow is entering the business.
ancial Planners
get
y to June 2008
            March       April        May        June
                 30,000       38,000     40,000      45,000

               18,000       22,800      24,000       27,000
               11,700       14,820      15,600       17,550
                 (300)        (380)       (400)        (450)
               29,400       37,240      39,200       44,100

                7,000        7,000       7,000        7,000
                6,615        7,350       9,310        9,800
                5,400        6,000       7,600        8,000
                5,000        5,000       5,000        5,000
                5,400        6,000       7,600        8,000
                5,400        6,000       7,600        8,000
                5,000        5,000       5,000        5,000
                5,400        6,000       7,600        8,000
                2,500        2,500       2,500        2,500
                  800          800         800          800
                  175          175         100          100
               27,890       29,825      34,910       36,200
                8,000        9,510      16,925       21,215
                1,510        7,415       4,290        7,900
                9,510       16,925      21,215       29,115
                    -            -           -            -
                9,510       16,925      21,215       29,115
Scenario Summary
                                       Best Case Scenario   Base Case Scenario   Worst Case Scenario

Changing Cells:
                        November                52,800                48,000                43,200
                        December                49,500                45,000                40,500
                        January                 27,500                25,000                22,500
                        February                29,700                27,000                24,300
                        March                   33,000                30,000                27,000
                        April                   41,800                38,000                34,200
                        May                     44,000                40,000                36,000
                        June                    49,500                45,000                40,500
Result Cells:
                         December              12,000                 12,000                12,000
                         January               (3,453)                (3,000)               (2,548)
                         February               8,894                  7,860                 5,827
    Unadjusted Cash
                         March                 11,602                  9,510                 8,032
        Balance
                         April                 20,806                 16,925                13,658
                         May                   26,565                 21,215                16,479
                         June                  36,295                 29,115                22,549
                         December
                         January               11,453                 11,000                10,548
                         February                    -                   140                 2,174
   Current Borrowing March                           -                     -                     -
                         April                       -                     -                     -
                         May                         -                     -                     -
                         June                        -                     -                     -
                         December              12,000                 12,000                12,000
                         January                8,000                  8,000                 8,000
                         February               8,894                  8,000                 8,000
  Ending Cash Balance March                    11,602                  9,510                 8,032
                         April                 20,806                 16,925                13,658
                         May                   26,565                 21,215                16,479
                         June                  36,295                 29,115                22,549
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.
    Based on the three case scenarios, the lease that the organization
  would have to borrow is $11,140, which is in the base scenario. The
 most that the organization will have to borrow is $12,722, which is in
 the worst case scenario. This would not change the answer to part B,
in that they still recover from the losses in the early months of the time
             period and produce profit in the later months.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:2
posted:2/19/2012
language:
pages:5