Docstoc

Calculate Cash Flow off the Balance Sheet - Excel

Document Sample
Calculate Cash Flow off the Balance Sheet - Excel Powered By Docstoc
					                                                                                                                                                                                         9:33 PM 11/13/2010
                                                                                                                                                                   3f6f4ea9-9618-453e-9b31-a216103a90cc.xls


                                                                                                                                                                      Individual              Corporation
STATEMENT OF:                                                                                                                                                         Partnership             Trust
List all assets at fair market value. List all debts to who owed, interest rates, and payments.                                                                                            Month / Day / Year         Month / Day / Year

Attach additional sheets if more space is required.
                                                                   CURRENT                                                                                                 (Due in less      AMOUNT(S)
1. CURRENT ASSETS                                                                                              5. CURRENT LIABILITIES                                      than 1 year)
                                              Account No.           VALUE                                                                                                                         OWED
Checking                                                                                                       Accounts Payable
Savings
Bank Name                                                                                                      Accrued Int. (Current Liab.)
                                                                                                               Accrued Int. (I.T. Liab.)
Accounts Receivable SCH. 1                                                                                     Accrued Int. (L.T. Liab.)
Marketable Securities SCH. 2                                                                                   Rental(s)/Lease(s)
Livestock & Commodities SCH. 3A & B
Describe                  No. Units           Unit Value                                                       Income Tax & Social Security
                                                                                                               RE Taxes & Property Taxes
                                                                                                               CCC Loans/Accounts

                                                                                                               Operating Loans
                                                                                                                         Lender                           Due Date             Rate




Investments in Growing Crops SCH. 4                                                                            Other Current Liabilities
Supplies on Hand SCH. 5                                                                                        Credit Cards


                                                                                                               Prin. Portion IT Liabilities due in 12 months (from below)

Current Assets Total from SCH. 1-5                                                                             Prin. Portion LT Liabilities due in 12 months (from below)

                   1. TOTAL CURRENT ASSETS                                                                       5. Total Current Liabilities
2. Intermediate Assets(Not normally sold during yr.)                                                           6. Intermediate Liabilities(Due in 1-10 yrs.)
                                                                                                                                                        Due                  Principal
    Breeding Livestock           Number        Unit Value                                                            Lender            Collateral       Date      Rate     Due in 12 Mo




Personal Vehicles
Trucks
Machinery and Equip. SCH. 6




Cash Value of Life Insurance
Securities-Not readily marketable
Retirement accounts                                                                                            Intermediate Liabilities from SCH.8

                                                                                                               Subtract Prin. Due in 12 Months

            2. TOTAL INTERMEDIATE ASSETS                                                                         6. Total Intermediate Liabilities
3. Long Term Assets (Real Estate & Improvements)                                                               7. Long Term Liabilities (due in more than 10 yrs.)
             Original  Improve Cost     Year                                                                                                      Due              Principal
 # of Acres   Cost      Since Purch. Acquired                                                                  Lender          Collateral         Date    Rate Due in 12 Mo




Farm Credit Stock
Other Real Estate                                                                                              Other real estate debt
                                                                                                               Long Term Liabilities from SCH. 9

Long Term Assets from SCH. 7                                                                                   Subtract prin. Due in 12 months

                3. TOTAL LONG TERM ASSETS                                                                         7. Total Long Term Liabilities

     4. TOTAL ASSETS (1 + 2+ 3)                                                                                8. TOTAL LIABILITIES (5+6+7)
Lease Obligations:                                 Amt. Due         Total Unpaid
                                                                      Balance
                                                  in 12 Mos.
                                                                                                               9. Net Worth (4-8)
Lessors:                                      $                $

ADDITIONAL INFORMATION                                                                                                                                PLEASE EXPLAIN ANY "YES" ANSWERS
Are there any judgements of record against you?                                                                               Yes             No
Have you been a debtor in bankruptcy in the last 10 years?                                                                    Yes             No

Are you a party to a lawsuit?                                                                                                 Yes             No
Are any of your taxes delinquent or under dispute?                                                                            Yes             No
Are you obligated as a cosigner or guarantor on any other obligations?                                                        Yes             No
Does anyone else own an interest in the property listed on the balance sheet?                                                 Yes             No
SIGNATURES AND AUTHORIZATIONS
By signing below, we certify that this information together, with any accompanying schedule(s), is a true and correct and complete statement of our financial condition as of the date indicated and that our financial condition has not
materially changed. We understand that it is a federal crime punishable by fine or imprisonment, or both to knowingly make any false statements in this application as applicable under the provisions of Title 18, United State code,
Section 1014. We consent to any credit investigations necessary to act on or verify the supplied information and acknowledge that we may be asked to provide additional information. PHOTOCOPIES OF THIS AUTHORIZATION
MAY BE PRESENTED TO AND RELIED UPON AS OUR AUTHORIZATION TO RELEASE INFORMATION TO FARM CREDIT SERVICES (PCA, FLBA, OR ACA, as applicable). Lender, its agents, successors and assigns
may report our names and information regarding this loan and all our past and future loans to credit reporting agencies.




Signature                                                                              Signature
                                                                                                                                                                                           Date Signed
   LOAN
                 ASSN.#        B.O. NO.           CIF NO.                          LOAN NO.                      LN. OFF. NO.                  CUSTOMER SHORT NAME
  ENTITY
                                                                                                                                                                                                  BALANCE SHEET
CURRENT ASSETS                                                                                                   Value               Sch. 3a Livestock                                                                                     Value
 Sch. 1 Accounts Receivable - Owned By, Terms                                                                                    Description                                     Weight            No. of Units          Unit Value




   Sch. 2 Marketable Securities - Number, Description




   Sch. 4 Investment in Growing Crops
                                                                                                                                     Sch. 3b Crops and Feed
                                                                                                                                 Description                                                       No. of Units          Unit Value




   Sch. 5 Supplies on Hand - Description, Quantity




Total Value Sch. 1 - 5


ADDITIONAL SIGNATURES AND AUTHORIZATIONS (if more signatures needed)
By signing below, we certify this information, together with any accompanying schedule(s), is a true, correct and complete st atement of our financial condition as of the date indicated and our financial condition has not materially
changed. We understand it is a federal crime punishable by fine or imprisonment, or both to knowingly make any false stateme nts in this application as applicable under the provisions of Title 18, United State code, Section 1014. We
consent to any credit investigations necessary to act on or verify the supplied information and acknowledge that we may be as ked to provide additional information. PHOTOCOPIES OF THIS AUTHORIZATION MAY BE
PRESENTED & RELIED UPON AS OUR AUTHORIZATION TO RELEASE INFORMATION TO FARM CREDIT SERVICES (PCA, FLBA, OR ACA, as applicable ). Lender, its agents, successors and assigns may report our
names and information regarding this loan and all our past and future loans to credit reporting agencies.



                                                                                                                                                                                                Date Signed


Signature                                              (type name here)                                     Signature            (type name here)                                               Signature                     (type name here)


Signature                                              (type name here)                                     Signature            (type name here)                                               Signature                     (type name here)
                MACHINERY/EQUIPMENT SCHEDULE (SCH. 6)


      FOR:                                                                                        AS OF:     12/31/2008   12/31/2009
MACHINERY / EQUIPMENT (exclude leased equipment)
OWNERSHIP%       MODEL                     MAKE, MODEL #, SIZE, ETC.               HOURS         CONDITION   MARKET       Updated
(IF NOT 100%)     YEAR                                                         (IF APPLICABLE)    (E,G,F)*   VALUE         Value
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
       100%
TRUCKS/VEHICLES                                                                 MACH/EQUIP TOTAL -- :
OWNERSHIP%       MODEL                     MAKE, MODEL #, SIZE, ETC.              MILEAGE        CONDITION   MARKET
(IF NOT 100%)     YEAR                                                          (ESTIMATED)       (E,G,F)*   VALUE
       100%
       100%
       100%
       100%
       100%
 * CONDITION - E - EXCELLANT; G - GOOD; F - FAIR                                TRUCK/VEHICLE TOTAL:
LEASED ASSETS
OWNERSHIP%            LEASE COMPANY, ITEM YEAR, MAKE,MODEL #, SIZE, PAYMENT AMOUNT, FREQUENCY,               MARKET
(IF NOT 100%)            # REMAINING PAYMENTS (NOT INCLUDING BUYOUT), BUYOUT AMOUNT AND DATE DUE             VALUE
       100%
       100%
       100%
       100%
       100%
       100%

                                                   RETURN TO FARM CREDIT SERVICES
SCH. 7 Long Term Assets
   Description/Name                Section   Township    Acres    Year Aq.     Cost       Improvements     Current Value




TOTAL                                                       -                         -            -                   -

SCH. 8 Intermediate Liabilities
                                             Maturity   Payment   Payment    Interest      Principal   Current Principal
       Lender         Purpose/Collateral      Date       Dates     Freq.       Rate       Due < 12 Mo.     Balance




TOTAL                                                                                              -                   -

SCH. 9 Long Term Liabilities
                                             Maturity   Payment   Payment    Interest      Principal   Current Principal
       Lender         Purpose/Collateral      Date       Dates     Freq.       Rate       Due < 12 Mo.     Balance




TOTAL                                                                                              -                   -
                                                                       FARMS OPERATED SCHEDULE
                                                                       OPERATOR:
     FARMS OPERATED   SEC   TWP   N   RNG   E    COUNTY       % CROP      TOTAL      A. Corn            B. Soybeans        C.                D.                FULL SHARE   TOTAL CASH
                                                                                                EST.               EST.              EST.              EST.
     RECORD OWNER     NO.         S         W             INTEREST      CROP ACRES    ACRES    YIELDS    ACRES    YIELDS    ACRES   YIELDS    ACRES   YIELDS     ACRES       RENT DUE    MEMO

 1

 2

 3

 4

 5

 6

 7

 8

 9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24
25


      CROP YEAR                       TOTAL ACRES
         2010                         FULL SHARE ACRES



                                      TOTAL PRODUCTION

                                      FULL SHARE PRODUCTION
                                      TOTAL CASH RENT
                                                                                                                                                                                11/13/2010
                                                                                                                                                                                   9:33 PM
                                                                                                                                                  3f6f4ea9-9618-453e-9b31-a216103a90cc.xls
                                                                                                                                                                           Mthly Cash Flow


          Input                    MONTHLY CASH FLOW DETAILS SHEET          2010
                            Operator:
          Calculations

                                      Jan.      Feb      Mar       Apr      May         June      July   Aug   Sept   Oct   Nov   Dec   Total
                                                   MARKET HOG SALES
# Sold
Weight                                                                                                                                  #DIV/0!
CWT                                                                                                                                     #DIV/0!
Resulting Income

                                                  CULL BR. HOG SALES
# Sold
Weight                                                                                                                                  #DIV/0!
CWT                                                                                                                                     #DIV/0!
Resulting Income

                                                  MARKET CATTLE SALES
# Sold
Weight                                                                                                                                  #DIV/0!
CWT                                                                                                                                     #DIV/0!
Resulting Income

                                                 CULL BR. CATTLE SALES
# Sold
Weight                                                                                                                                  #DIV/0!
CWT                                                                                                                                     #DIV/0!
Resulting Income

                                             OTHER MARKET LIVESTOCK SALES
# Sold
Weight                                                                                                                                  #DIV/0!
CWT                                                                                                                                     #DIV/0!
Resulting Income

                                              OTHER CULL BR. ANIMAL SALES
# Sold
Weight                                                                                                                                  #DIV/0!
CWT                                                                                                                                     #DIV/0!
Resulting Income

                                              LIVESTOCK PRODUCT SALES #1
# Units Sold               MILK
Price Per Unit                                                                                                                          #DIV/0!
Resulting Income

                                              LIVESTOCK PRODUCT SALES #2
# Units Sold              OTHER
Price Per Unit                                                                                                                          #DIV/0!
Resulting Income

                                                      CROP SALES
Bushels sold -Grain #1    CORN
Price/Bu.                                                                                                                               #DIV/0!
Resulting Income

Bushels sold -Grain #2   SOYBEANS
Price/Bu.                                                                                                                               #DIV/0!
Resulting Income

Bushels sold -Grain #3    WHEAT
Price/Bu.                                                                                                                               #DIV/0!
Resulting Income

Total Crop Sales

                                                   NON-FARM INCOME
Salary
Dividends
Interest
Other Business
Rents/Royalties
Non-Farm Income




                                                                                   Version 1.12
                                                                                    08/31/05
                                                                                        6
                                                                                                                                                                             11/13/2010
                                                                                                                                                                                9:33 PM
                                                                                                                                               3f6f4ea9-9618-453e-9b31-a216103a90cc.xls
                                                                                                                                                                        Mthly Cash Flow




                                                                    MONTHLY CASH FLOW -          2010

Beg.Op Loan Balance                       -      -      -      -      -           -          -      -      -      -      -      -         -
                                       Jan.   Feb    Mar    Apr    May         June      July    Aug    Sept   Oct    Nov    Dec    Total CF
Cash Income:
Farm Rental Income                                                                                                                       -
Crop Insurance                                                                                                                           -
Sales/Grain (see sch above)               -      -      -      -      -             -        -      -      -      -      -      -        -
Resale Products                                                                                                                          -
Market Livestock (see sch above)          -      -      -      -      -             -        -      -      -      -      -      -        -
Breeding Livestock (see sch above)        -      -      -      -      -             -        -      -      -      -      -      -        -
Livestock Products (see sch above)        -      -      -      -      -             -        -      -      -      -      -      -        -
Gov't. Payments                                                                                                                          -
Other Farm Income                                                                                                                        -
Total Cash Farm Inc.                      -      -      -      -      -             -        -      -      -      -      -      -        -
Non-Farm Income (see sch above)           -      -      -      -      -             -        -      -      -      -      -      -        -
Capital Asset Sales                                                                                                                      -
Total Cash Receipts                       -      -      -      -      -             -        -      -      -      -      -      -        -

Cash Expenses:
Feed                                                                                                                                     -
Breeding Fees                                                                                                                            -
Spray/Chemicals                                                                                                                          -
Conservation Exp                                                                                                                         -
Custom Hire                                                                                                                              -
Officer Compensation                                                                                                                     -
Fertilizer and Lime                                                                                                                      -
Advertising                                                                                                                              -
Gas, Fuel, Oil                                                                                                                           -
Insurance                                                                                                                                -
Oper Interest                                                                                                                            -
Labor Hired                                                                                                                              -
Pension/Profit Sharing                                                                                                                   -
Rent - Machines                                                                                                                          -
Rent - Land                                                                                                                              -
Repairs / Maintenance                                                                                                                    -
Seed, Plants                                                                                                                             -
Storage, Warehouse                                                                                                                       -
Supplies                                                                                                                                 -
Taxes RE/Business                                                                                                                        -
Utilities                                                                                                                                -
Vet Fees, Medicine                                                                                                                       -
Other Farm Exp                                                                                                                           -
Replacement/Breeding                                                                                                                     -
Marketing                                                                                                                                -
Other or Summation                                                                                                                       -
Total Cash Farm Op.                       -      -      -      -      -             -        -      -      -      -      -      -        -

Resale Items                                                                                                                             -
Breeding Livestock                                                                                                                       -
Non-Farm Expense                                                                                                                         -
Capital Purchases                                                                                                                        -
Living                                                                                                                                   -
Income Taxes                                                                                                                             -
Total Cash Expenses                       -      -      -      -      -             -        -      -      -      -      -      -        -
Cash Residual (18 - 56)                   -      -      -      -      -             -        -      -      -      -      -      -        -
Loan #1     (Lender/descr)   Payment                                                                                                     -
Loan #2     (Lender/descr)   Payment                                                                                                     -
Loan #3     (Lender/descr)   Payment                                                                                                     -
Loan #5     (Lender/descr)   Payment                                                                                                     -
Loan #6     (Lender/descr)   Payment                                                                                                     -
Loan #7     (Lender/descr)   Payment                                                                                                     -
Loan #8     (Lender/descr)   Payment                                                                                                     -
Loan #9     (Lender/descr)   Payment                                                                                                     -
Loan #10 (Lender/descr)      Payment                                                                                                     -
CF Change                                 -      -      -      -      -             -        -      -      -      -      -      -        -
End. Op Loan Balance                      -      -      -      -      -             -        -      -      -      -      -      -        -




                                                                          Version 1.12
                                                                           08/31/05
                                                                               7
                                                        MARKETING POSITION REPORT
                                                        CROP YEAR:       DATE PREPARED/UPDATED:                                       Input Cells
                                                                                                                                      Calculated Cells

Commodity 1:

Total Crop Acres:                             COMMODITY PROJECTIONS          Total          Per Acre     Per Bushel
Projected Crop Yield (per acre) :               Projected Production:                   0            0        --
Current Cash Price (per bushel) :                Projected Revenue:                   $0        $0.00          $0.00
Projected Total Cost (per acre) :               Projected Crop Costs:                 $0        $0.00          $0.00
                                                  Projected Margin:                   $0        $0.00          $0.00

                                            Delivery   Bushels Forward      Bushels          Price                     Put Options                   % of Projected   Estimated   Net Prices
                 Delivery Location
                                             Date        Contracted         Hedged           ($/bu)       Bushels      Strike Price     Premium       Production        Basis       ($/bu)
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00

                                            Bushels     Bushels/Acre     % of Production                 Price per bushel needed on
                                                                                                                                                         $0.00
                   Total Unsold/Unpriced:      0            0.0                     0.0%                 unsold bushels to break-even:


Commodity 2:

Total Crop Acres:                             COMMODITY PROJECTIONS          Total          Per Acre     Per Bushel
Projected Crop Yield (per acre) :               Projected Production:                   0            0        --
Current Cash Price (per bushel) :                Projected Revenue:                   $0        $0.00          $0.00
Projected Total Cost (per acre) :               Projected Crop Costs:                 $0        $0.00          $0.00
                                                  Projected Margin:                   $0        $0.00          $0.00

                                            Delivery   Bushels Forward      Bushels          Price                     Put Options                   % of Projected   Estimated   Net Prices
                 Delivery Location
                                             Date        Contracted         Hedged           ($/bu)       Bushels      Strike Price     Premium       Production        Basis       ($/bu)
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00
                                                                                                                                                               0.0%                    $0.00

                                            Bushels     Bushels/Acre     % of Production                 Price per bushel needed on
                                                                                                                                                         $0.00
                   Total Unsold/Unpriced:      0            0.0                     0.0%                 unsold bushels to break-even:
                                     AMORTIZED PAYMENT SCHEDULE

               Loan amount                                        # of years
               Interest rate                                   Payment(s)/yr.
                Collateral value

    Pymt.                          Principal                      Total           Interest
      #            Balance         Payment        Interest       Payment          per diem   L:AV




                                          Total Annual Debt Service


                                   Principal      Interest            Total
                                    $0.00          $0.00              $0.00


Comments:




       Name:                                                           Loan # :
                      Instructions on filling out the Balance Sheet
1- Enter the current value of assets (columns "F") and liabilities (columns "M") the
following ways:
    A-Current Assets:
         ►For the following categories simply enter the value:
                ● Checking, Savings, Accounts Receivable, Marketable Securities, Investment in Growing
                Crops, Supplies on Hand
         ►For the Livestock & Commodities category type the description, number of units, and current cash
         price. The current value will be figured based on that data.
                ● Example-Enter: Corn; 10,000 bushels; $3.50/bu. The current value will calculate $35,000.
                ● Example-Enter: Calves; 20 head; $500/head. The current value will calculate at $10,000.
         ►If additional space is needed for Accounts Receivable, Marketable Securities, Livestock &
         Commodities, Investment in Growing Crops or Supplies on Hand, please utilize the worksheet tab
         titled "Sch. 1-5". The totals entered into that spreadsheet will carry to the balance sheet current assets
         category named "Current Assets Total from SCH. 1-5".
    B-Intermediate Assets:
         ►For the following categories simply enter the value:
                ● Personal Vehicles, Trucks, Machinery and Equipment, Cash Value of Life Insurance,
                Securities-Not Readily Marketable, Retirement Accounts
         ►For the Breeding Livestock category type the livestock, number of units, and value. The current
         value will be figured based on that data.
                ● Example-Enter: Cows; 25 head; $500/head. The current value will calculate at $12,500.
    C-Long-Term Assets
         ►Enter the # of Acres, Original Cost, Improvement Costs, Year Acquired and Current Value for each
         individual tract of land.
         ►Enter the Current Value of Farm Credit Stock and Other Real Estate.
         ►If additional space is needed for Long Term Assets please utilize the worksheet tab titled "Sch. 7-9".
         Enter the additional Long-Term Assets into "SCH 7 Long Term Assets. The totals entered into that
         spreadsheet will carry to the balance sheet long term assets category named "Long Term Assets from
         SCH. 7".
    D-Current Liabilities:
         ►Enter the Amount Owed for all Current Liability categories, except Prin. Portion IT Liabilities due in
         12 months and Prin. Portion LT Liabilities due in 12 months (those values will carry from amounts
         entered in below liability categories) .
    E-Intermediate Liabilities (Due in 1-10 years):
         ►For each loan enter the Lender, Collateral, Due Date, Interest Rate, Principal Due in 12 Months, and
         Current Amount Outstanding.
                ● The "Principal Due in 12 Months" for all the loans entered onto the balance sheet will be added
                to calculate the TOTAL IT PRIN DUE IN 12 MONTHS. That value will then automatically be
                subtracted from the total portion of intermediate term debt outstanding, as well as moved and
                added to the current liabilities outstanding on the balance sheet.
         ►If additional space is needed for Intermediate Liabilities please utilize the worksheet tab titled "Sch.
         7-9". Enter the additional Intermediate Liabilities into "SCH 8 Intermediate Liabilities". The totals
         entered into that spreadsheet will carry to the balance sheet Intermediate Liabilities category named
         "Intermediate Liabilities from SCH. 8".
    F-Long Term Liabilities (Due in more than 10 years):
         ►For each loan enter the Lender, Collateral, Due Date, Interest Rate, Principal Due in 12 Months, and
         Current Amount Outstanding.
                ● The "Principal Due in 12 Months" for all the loans entered onto the balance sheet will be added
                to calculate the TOTAL LT PRIN DUE IN 12 MONTHS. That value will then automatically be
                subtracted from the total portion of long term debt outstanding, as well as moved and added to
                the current liabilities outstanding on the balance sheet.
      ● The "Principal Due in 12 Months" for all the loans entered onto the balance sheet will be added
      to calculate the TOTAL LT PRIN DUE IN 12 MONTHS. That value will then automatically be
      subtracted from the total portion of long term debt outstanding, as well as moved and added to
      the current liabilities outstanding on the balance sheet.
►If additional space is needed for Long Term Liabilities please utilize the worksheet tab titled "Sch. 7-
9". Enter the additional Long Term Liabilities into "SCH 9 Long Term Liabilities". The totals entered
into that spreadsheet will carry to the balance sheet Long Term Liabilities category named "Long Term
Liabilities from SCH. 9".
                        Instructions on filling out Schedules 1-9
Schedules 1- 2- 4- and 5-:
    Each individual schedule allows the opportunity to have multiple entries for Accounts Receivable,
    Marketable Securities, Investment in Growing Crops and Supplies on Hand.
        ►Enter each individual entry by typing a description and the current value of the entry.
        ►Each individual schedule will automatically calculate a total for that specific schedule.
        ►The total of Schedules 1-5 will automatically be added together.
        ►The total value of Schedules 1-5 will then automatically be carried forward to the balance sheet to
        the Current Assets line titled "Current Assets Total from SCH. 1-5".

Schedules 3a- & 3b-:
    A-Schedule 3a: Livestock
          ►For the livestock inventory schedule type the description, number of units and unit value for each
          individual entry. The current value will be figured based on that data.
                ● Example: Calves; 20 head; $500/head; The current value will calculate at $10,000.
          ►The total of schedule 3a will be automatically calculated from all individual livestock entries.
    B-Schedule 3b: Crops and Feed
          ►For the commodity inventory schedule type the description, number of units and unit value for each
          individual entry. The current value will be figured based on that data.
                ● Example: Corn; 10,000 bushels; $3.50/bu. The current value will calculate at $35,000.
          ►The total of schedule 3b will be automatically calculated from all individual crop/feed entries.
    NOTE: The totals from Schedules 3a & 3b will automatically be included in the calculation of the
    total value of schedules 1-5. As mentioned above, the total value of schedule 1-5 will automatically
    be carried forward to the balance sheet to the Current Assets line titled "Current Assets Total from
    SCH. 1-5".

Schedule 6: Machinery/Equipment
    This schedule will be an itemized list of all Machinery/Equipment Owned, Trucks/Vehicles Owned
    and Leased Assets.
         ►For each category indicate the appropriate portion of Ownership, Model Year, Model Specifications,
         Hours, Condition and Estimated Market Value in the "Market Value" column.
         ►The "Updated Value" column is used to update the value of the
         machinery/equipment/trucks/vehicles in subsequent years.
         ►MACH/EQUIP TOTAL and TRUCK/VEHICLE TOTAL will automatically be calculated given the
         values entered for each individual entry.
         ►The totals for MACH/EQUIP and TRUCK/VEHICLE will need to be MANUALLY entered into the
         appropriate spot on the balance sheet.

Schedule 7: Long Term Assets
    This schedule will allow for ADDITIONAL long term assets to be itemized and added to the balance
    sheet, in addition to what has already been entered specifically on the balance sheet.
         ►For each individual asset enter a Description, Section, Township, Acres, Year Acquired, Cost,
         Improvements and Current Value.
         ►Total Acres, Total Costs, Total Improvement and Total Value will automatically be calculated based
         on the individual long term assets entered on Schedule 7.
         ►The total acres and total value of Schedule 7 will then automatically be carried forward to the
         balance sheet to the Long Term Assets line titled "Long Term Assets from SCH. 7".

Schedule 8- and 9-: Intermediate Term and Long Term Liabilities
These schedules will allow for ADDITIONAL intermediate and long term liabilities to be itemized
and added to the balance sheet, in addition to what has already been entered specifically on the
balance sheet.
     ►On either the intermediate and long term liabilities schedule, for each loan enter the Lender,
     Purpose/Collateral, Maturity Dates, Payment Dates, Payment Frequency, Interest Rate, Principal Due
     in <12 Months and Current Principal Balance.
          ● Each individual column labeled "Principal Due in <12 Months" and "Current Principal Balance
          Due" for all the loans entered into each specific schedule will each be added for a total. There
          will be a total for "IT Principal Due in <12 Months", "IT Current Principal Balance Due", "LT
          principal due in <12 Months" and "LT Current Principal Balance Due".
          ● The "IT Principal Due in <12 Months" and "IT Current Principal Balance Due" will automatically
          carry forward to the balance sheet to the Intermediate Term Liabilities line titled "Intermediate
          Liabilities from Sch. 8". The total IT principal due <12 months will be automatically included in
          the calculation for the overall IT principal due <12 months. That total will be automatically
          subtracted from the total portion of intermediate term debt outstanding, as well as moved and
          added to the current liabilities outstanding on the balance sheet (as described in 1-E of the
          Balance Sheet instructions).
          ● The "LT Principal Due in <12 Months" and "LT Current Principal Balance Due" will
          automatically carry forward to the balance sheet to the Long Term Liabilities line titled "Long
          Liabilities from Sch. 9". The total LT principal due <12 months will be automatically included in
          the calculation for the overall LT principal due <12 months. That total will be automatically
          subtracted from the total portion of long term debt outstanding, as well as moved and added to
          the current liabilities outstanding on the balance sheet (as described in 1-F of the Balance Sheet
          instructions).
             Instructions on filling out the Farms Operated Schedule
1- For each farm operated, put each individual farm on the schedule in lines 1-25:
    A-General Farm Information:
        ►For Record Owner, Section Number, Township, N/S, Range, E/W and County, simply type the
        description needed to identify the farm.
    B-Crop Interest and Total Crop Acres:
        ►For Crop Interest, simply type your personal interest in that particular farms production (for 100%
        interest, type 100; for 50% interest, type 50). For Total Crop Acres, simply type the number of crop
        acres used in production. NOTE: This is production acres, NOT total tract acres.
    C-Specific Commodity Acreage Production and Estimated Yields:

        ►For each individual farm indicate what commodity or commodities are planned to be grown. Enter
        the acres of production and estimated yield for each specific commodity. If a commodity other than
        corn or soybeans is being produced, use commodity space C and D (columns "P/Q" and "R/S") for
        those commodities. Make sure to type the specific commodity grown into either commodity C or D.
              ● This commodity specific data will be used to calculate total acres and full shares acres, as well
              as estimate total production and full share production for each specific commodity. Each of the
              follow calculations will be automatically figured based on data entered into the complete
              schedule.
    D-Total Cash Rent Due:
        ►Enter the Total Cash Rent Due (if applicable) for each farm operated. NOTE: This is TOTAL cash
        rent due for that specific farm. It is NOT cash rent per acre.
              ● This information will automatically calculate total cash rent due for all farms operated in that
              particular growing season.
    E-Crop Year:
        ►To complete the crop year, type the year (example: 2009) into the yellow colored box (in the bottom
        left-hand corner).
                 Instructions on filling out the Monthly Cash Flow
Please Note:
   ►There is a "Monthly Revenue/Income Detail Sheet". This will take all of the detailed revenue/income from
   sales and non-farm income, and automatically put it below into the actual cash flow.
   ►This spreadsheet, if filled out correctly, will help to determine the peak balance of an outstanding
   operating account balance. This can be effective in determining the appropriate amount for an operating
   loan request.
   ►Yellow cells are the only cells that should be filled out on this spreadsheet.
   ►White cells (when applicable) will be automatic calculations, given the information entered into yellow
   cells.

1-Complete the monthly detail revenue projections using the following methods for
each category:
   A-Livestock Sales (including Market Hogs, Cull Breeding Hogs, Market Cattle, Cull Breeding Cattle,
   Other Market Livestock and Other Cull Breeding Livestock):
        ►In the appropriate livestock category enter the projected number of head sold, projected average
        weight of each animal and the projected $/cwt sold, in the month of the projected sale/revenue.
              ● Projected revenue for each monthly livestock sales projection will be automatically calculated
              by the following equation: Resulting Income = # Sold x Weight x $/CWT
   B-Livestock Products (including Livestock Product Sales #1 and Livestock Product Sales #2):
        ►In the appropriate livestock product sales category, type the product name, enter the projected
        number of units sold and the price per unit, in the month of the projected sale/revenue.
              ● Projected revenue for each monthly livestock product sales projection will be automatically
              calculated by the following equation: Resulting Income = # Units Sold x Price per Unit
   C-Crop Sales (Corn, Soybeans, Wheat and other):
        ►In the appropriate crop category, type the product name, enter the bushels sold and the
        price/bushel, in the month of the projected sale/revenue.
              ● Projected revenue for each monthly crop sales projection will be automatically calculated by
              the following equation: Resulting Income = Bushels sold x Price/Bushel
   D-Non-Farm Income:
        ►Enter any projected non-farm income (salary, dividends, interest, other business and
        rents/royalties), in the month of the projected income.

2-Complete the monthly cash flow projections using the following methods:
   A-Enter the beginning balance of your operating loan (as of January 1) in the appropriate yellow
   cell.
   B-Enter the projected cash income of each category with yellow cells, in the month of the projected
   income.
         ►Categories include: Farm Rental Income; Crop Insurance; Resale Products; Government
         Payments; Other Farm Income and Capital Asset Sales
   C-Enter the projected cash expenses of each category with yellow cells, in the month of the
   projected expense is incurred.
         ►Categories include: Feed; Breeding Fees; Spray/Chemical; Conservation Expense; Custom Hire;
         Officer Compensation; Fertilizer and Lime; Advertising; Gas, Fuel, Oil; Insurance; Operating Interest;
         Labor Hired; Pension/Profit Sharing; Rent-Machines; Rent-Land; Repairs/Maintenance; Seed, Plants;
         Storage, Warehouse; Supplies; Taxes RE/Business; Utilities; Vet Fees, Medicine; Other Farm
         Expenses; Replacement/Breeding; Marketing; Resale Items; Breeding Livestock; Non-Farm Expense;
         Capital Purchases; Living Expenses; and Income Taxes
   D-Enter the name of the lender or description for each loan, as well as the month(s) a loan payment
   (Principal and Interest) will be made, in the lines labeled "Loans 1-10".
Helpful Hints (these are all automatically calculated) :
    ►The Ending Operating Loan Balance is determined by subtracting (a positive monthly cash flow) or
    adding (a negative monthly cash flow) to the Beginning Operating Loan Balance for that particular month.
    The Ending Operating Loan Balance will be the new Beginning Operating Loan Balance for the next month.
    The highest number of the line labeled "Ending Operating Loan Balance" would be an effective starting
    point to determine the operating line needs for the coming year.
           Instructions on filling out the Marketing Position Report
Please Note:
   ►The marketing position detail should be considered by each crop year and separated into individual
   commodities.
   ►Yellow cells are the only cells that should be filled out on this spreadsheet.
   ►White cells (when applicable) will be automatic calculations, given the information entered into yellow
   cells.

1-Complete the "Crop Year" and "Date Prepared/Updated" for the Marketing Position
Report.


2-For each commodity marketing plan complete the following general information:
   A-The specific commodity name:
       ►Example: Corn, Wheat, Soybeans, etc.
   B-For each specific commodity indicate the Total Crop Acres, Projected Crop Yield (per acre),
   Current Harvest Cash Price (per bushel) and Projected Total Cost (per acre).
       ►This data will help determine the projected production (in total acres and per acre), projected
       revenue (in total acres, per acre and per bushel), projected crop costs (in total acres, per acre and
       per bushel) and projected margin (in total acres, per acre and per bushel). All of the mentioned
       calculations will be important in calculating the total number of unsold/unpriced bushels,
       bushels/acre and % of production, as well as the price per bushel needed on unsold bushels to
       break-even.
            ● Example- Total crop acres for corn is 100. Enter "100" in the "Total Crop Acres" cell. NOTE:
            This is only the acreage in corn production.
            ● Example- Projected crop yield for corn is 175. Enter "175" in the "Projected Crop Yield" cell.
            NOTE: This is projected yield per acre.
            ● Example- Current harvest cash price for corn is $3.50. Enter "3.5" in the "Current Cash
            Price" cell. NOTE: This is the current harvest cash price per bushel.
            ● Example- Currently the University of Illinois is projecting corn production at $500/acre (non-
            land costs). Enter input expenses per acre (non-land) plus land costs per acres in the
            "Projected Total Cost" cell. NOTE: This is projected total costs per acre.

2-Enter all of the specific commodity market positions by completing the delivery
location and delivery data, and the remaining position information in one of the
following ways:
   A-Forward Contracts
       ►Enter the number of bushels forward contracted "Column G" and the price per bushel "Column I"
       for each specific forward cash contract.
             ● Example-1,000 bushels of corn contracted at $4.05/bushel: Enter "1000" into "Bushels
             Forward Contracted" (Column G) and enter "4.05" in "Price ($/bu)" (Column I). This will
             automatically show $4.05/bushel as the net price on the right-side of the report.
   B-Hedging
       ►Enter the number of bushels hedged "Column H", price per bushel "Column I" and estimated
       basis "Column N" for each specific hedge. When the basis is estimated to be "under" a certain
       contract, type the basis as a positive number.
             ● Example-1,000 bushels of corn hedged at $6.01/bushel, with an estimated basis of $0.35
             under. Enter "1000" into "Bushels Hedged" (Column H), enter "6.01" in "Price ($/bu)" (Column
             I) and enter "0.35" into "Estimated Basis" (Column N). This will automatically calculate a net
             price of $5.66/bu on the right-side of the report.
    C-Put Options
        ►Enter the number of bushels the put option covers "Column J", strike price per bushel "Column
        K", premium per bushel "Column L" and estimated basis "Column N" for each specific put option
        purchased.
              ● Example-1,000 bushels of corn have a put option, with a strike price of $5.60, premium of
              $0.54 and an estimated basis of $0.58 under. Enter "1000" into "Bushels" (Column J), enter
              "5.60" in "Strike Price" (Column K), enter "0.54" in "Premium" (Column L) and enter "0.58"
              into "Estimated Basis" (Column N). Given a harvest cash price of $3.50/bushel, this will
              automatically calculate a net price of $4.48/bu on the right-side of the report. This option will
              be "in-the-money" until the cash price less the estimated basis and option premium is greater
              than $4.48.
              ● Example-1,000 bushels of corn have a put option, with a strike price of $3.00, premium of
              $0.30 and an estimated basis of $0.12 under. Enter "1000" into "Bushels" (Column J), enter
              "3.00" in "Strike Price" (Column K), enter "0.30" in "Premium" (Column L) and enter "0.12"
              into "Estimated Basis" (Column N). Given a harvest cash price of $3.50/bushel, this will
              automatically calculate a net price of $3.08/bu on the right-side of the report. This is because
              the cash price less the premium and estimated basis is a net price of $3.08/bu, which is
              greater than the put option price, which would be the strike price less the premium and
              estimated basis, which would be a net price of $2.58/bu.
    D-Hedge-to-Arrive Contracts

         ►For each specific hedge-to-arrive contract, enter the number of bushels hedged "Column H",
         price per bushel "Column I" and estimated basis "Column N" on the day the contract will establish
         the basis portion of price. When the basis is estimated to be "under" a certain contract, type the
         basis as a positive number. It is a best practice to note next to either the delivery location or the
         delivery date, the day the contract establishes the basis. After the basis is locked in at the specified
         date, change the basis in the marketing report to the appropriate value, as indicated above.
               ● Example-1,000 bushels of corn hedged at $6.01/bushel, with an estimated basis of $0.35
               under on December 10th. Enter "Dec. 10" next to the specific \delivery location. Enter "1000"
               into "Bushels Hedged" (Column H), enter "6.01" in "Price ($/bu)" (Column I) and enter "0.35"
               into "Estimated Basis" (Column N). This will automatically calculate a net price of $5.66/bu on
               the right-side of the report. On December 10th, the basis actually strengthened from your
               estimate and the basis, on the date the contact established the basis, was $0.31 under.
               Change "0.35" in the "Estimated Basis" (Column N) to "0.31". This will automatically change
               the net price to $5.70/bu on the right-side of the report.

Helpful Hints:
    ►The harvest current cash price is very important, because it helps to determine if the purchased put
    option received a higher net price than the harvest cash price minus the option premium minus the
    estimated basis. The highest net price ($/bu) will automatically be the price used in the spreadsheet.
    ►All of the marketed bushels impacts the overall projected revenue and projected margin in the
    "Commodity Projections" table.
    ►Given projected production and marketed bushels, the spreadsheet automatically calculates the
    number of bushels unsold/unpriced.
    ►Given the number of bushels unsold/unpriced (that is automatically figured) the price per bushel
    needed on unsold bushels to break-even will be automatically figured by taking the difference of the
    revenue from the marketed grain minus total projected crop costs and then dividing by the number of
    bushels unsold.
         ● Example: Revenue from marketed grain is $24,000, total projected crop costs is $50,000 and the
         number of unsold/unpriced bushels is 12,500. The spreadsheet will automatically calculate a break-
         even per bushel price of $2.08/bushel. Calculation is $50,000 - $24,000 = $26,000 / 12,500 =
         $2.08/bushel

				
DOCUMENT INFO
Description: Calculate Cash Flow off the Balance Sheet document sample