Introduction to Spreadsheets 2003 by idy87864

VIEWS: 5 PAGES: 18

Introduction to Spreadsheets 2003 document sample

More Info
									Enter Numbers in Dark Blue on the following page, all others are calculated.

   Introduction: The primary purpose of this template is to demonstrate the interaction of financial statements and take
   look at the financial feasibility of an operation. The user must complete some very simple information about each en
   operation. Additional information about assests and liabilities are necessary to provide a complete financial picture.

   Use the Enterprise tab and Statements tab at the bottom of the screen to enter requested data. This template is not c
   financial analysis. Spreadsheets that are capable of detailed financial analysis are located at
   http://www.montana.edu/extensionecon/farmmgt/software.html

   Financial statements used are very condensed versions of the basic statements used for financial analysis (balance sh
   income statement, statement of owner equity). These statements are both simplfied and some data entry has been res
   information flows to the correct financial statement. While simplified statesments, the interaction of the statements a
   concepts remain the same. In fact, these statements produce financial analysis ratio values (see Ratio_Calc tab) and d
   calcualtions identical to detailed financial statements.

   The statements in this template are distributed "in balance" (fully reconciled). To help learn about the relationship am
   statements and how information flows from one to another, change a number (only those in blue text
   Enterprise tab. The Statement of Owner Equity indicates whether the statements are still reconciled. This provides a
   discuss how financial statements are linked together. Changes in one number must be "offset" with changes somewh
   system of financial statements. In many instances, in this spreadsheet, this offset in automatically computed and put
   statement. If the Statement of Owner Equity showns a Discrepancy figure that is not equal to zero the "set of stateme
   balance. The user must make one or more additional entries somewhere to make the statements reconcile.




This program uses only business assets and liabilities in the reconciliation process. Personal (Non-Business Assets and Liabilities) items are
However, non-business cash inflows and outflows are considered for correct Cash Flow calculations. (Off Farm wages, family living expen
                                                    Written by Duane Griffith                Last update
nancial statements and take a "quicka and dirty"    Montana State University                 Septemeber 11, 2003
 information about each enterprise within an        210 Linfield Hall
 omplete financial picture.                         Bozeman, MT 59717
                                                    griffith@montana.edu
     This template is not capable of detailed       406-994-2580

                                                    To check for updates of this software, click the link below.
                                                    http://www.montana.edu/extensionecon/farmmgt/software.html

ancial analysis (balance sheet, cash flow,          Loof for the RDFinancial file and check the
 me data entry has been restricted to assure        date this file was last updated.
eraction of the statements and financial analysis
 (see Ratio_Calc tab) and dollar amount


rn about the relationship among the financial
 n blue text) in one of the statements or on the
 econciled. This provides an opportunity to
  set" with changes somewhere else in the same
 atically computed and put on the right
 l to zero the "set of statements" are out of
ments reconcile.




Assets and Liabilities) items are ignored.
arm wages, family living expenditures, etc.)
       Only information in blue text can be changed/entered. All other information is calculated or fixed.

                                   Click button to print all input, statements, and ratios in this file>>
       Crop Production/Sales Estimates:
                                                                  Crop and Forage Enterprises                 (MUST use a yield of 1 for summer fallow, price = zero)
                                         Crop/Forage Name       corn       soybean
       Yield Per Unit                                          150.0         48.0
       Number of Acres Per Enterprise                           1500         1000                                                                                                                         2,500
       Sales Price Per Unit                                    $3.65        $9.75
       Percent Acres Leased                                    54.0%        54.0%
Help   Tenant Share Lease Percentage                           70.0%        70.0%                                                                                                                     Totals
       Total Enterprise Crop Revenue                          $821,250    $468,000         $0                    $0            $0             $0           $0             $0             $0         $1,289,250
       Lease Payments going To Others                         $133,043    $75,816          $0                    $0            $0             $0           $0             $0             $0          $208,859
       Units (Bu, tons, lbs) Produced                         225,000      48,000          0                     0              0              0            0              0              0
                                                                                                                                                                                                     $1,080,392
                 Total Owner/Tenant Revenue Received          $1,080,392

       Livestock Produciton/Sales Estimates:
                                                                Breeding Livestock Enterprises              Non-Breeding Livestock Ent.
                                                              Cow-Calf     Sheep    Other brdg lvstk          Feeders        Stockers                                         Cull Revenue Calculations
       Cull Rate for Enterprise                                 0%          0%            0%                                                                             Cash         Base Value       Capital
       Cull Female Animal Weight                                 0           0             0                                                                          Cull Income    Animals Sold    Gain/Loss
       Cull Female Price Per Unit                              $0.00       $0.00         $0.00                                                                Cows         $0             $0            $0
       Cull Male Animal Weight                                   0           0             0                                                             Bulls Only        $0            $0             $0
       Cull Male Price Per Unit                                $0.00         0             0                                                           Sheep - Ewes      $0.00            $0            $0
       Avg. Number Females/Male                                  0           0             0                                                             Rams Only      $0.00            $0             $0
       Bull/Ram/??? Purchase Cost                               $0           0             0                                                   Other Brding Females      $0.00            $0            $0
       Avg. Lbs Prod. Per Animal                                 0           0             0                     0              0               Other Brding Males      $0.00            $0             $0
       No. Animal Exposed/Fed/Finished                           0           0             0                     0              0                Total Cull Revenue        $0             $0            $0
       Price Per Unit                                          $0.00       $0.00         $0.00                 $0.00          $0.00
       Base Value/Brding Animal                                 $0          $0            $0
       Other Revenue/Unit                                      $0.00       $0.00         $0.00                 $0.00          $0.00
                                         Enterprise Revenue     $0          $0            $0                    $0             $0

       Total Livestock Revenue                                   $0




Help   Government Payments:
       Direct Payment Calculations:
     Name for each Enterprise                                    corn       soybean             0                0              0             0             0              0              0
     Base Acreage by Enterprise                                  1500          500              0
     Percent of Base Leased from Others                         20.0%        20.0%            0.0%
Help Direct Payment Rate by Commodity                           $0.28        $0.44            $0.52
     Direct Payment Yield by Commodity                            117          28              42
     Direct Payments Per Base Acre                              $27.85      $10.47           $18.56            $0.00          $0.00        $0.00       $0.00         $0.00            $0.00      Row Totals
     Total Enterprise Gov. Payments                            $41,769      $5,236             $0               $0             $0            $0         $0             $0               $0        $47,005
     Gov. Payments Going To Others                              $2,506        $314             $0               $0             $0            $0         $0             $0               $0         $2,820
                                                                                                                                          Government Payments, excluding share paid to landlords    $44,185


       Counter Cyclical Payments:

       Counter Cyclical Payment Target Price Schedule as of February 2005.

       Commodity                                Unit          2002--03 2004--07
       barley                                per bushel         $2.21    $2.24
       corn                                  per bushel         $2.60    $2.63
       grain sorghum                         per bushel         $2.54    $2.57
       oats                                  per bushel         $1.40    $1.44
       other oilseeds                         per Cwt.          $9.80   $10.10
       peanuts                                 per ton        $495.00 $495.00
       rice                                   per Cwt.         $10.50   $10.50
       soybeans                              per bushel         $5.80    $5.80
       upland cotton                         per pound          $0.72    $0.72
       wheat                                 per bushel         $3.86    $3.92


       Effective Price calculation                                                            corn            soybean         $0.00         $0.00         $0.00          $0.00          $0.00         $0.00       $0.00
       Direct Payment Rate for this Commodity (Enter from table above)                       $0.28             $0.54          $0.52
       National Average Market Price Received During the Marketing Year                      $2.90             $6.00          $4.00
       National Average Loan Price for This Commodity                                        $2.05             $5.40          $2.60
       Counter Cyclical Payment Yields




       LDP Payments:
       Expenses by Enterprise
          Warning: When entering the enterprise expenses below, do not include expenses for depreciation, interest (opportunity costs) or family
          living. Depreciation expenses are included at the whole farm/ranch level and are entered below on this tab. Interest is included on the
          whole farm/ranch level and is calculated below on this tab. Family living is entered on the Statements page. If included in the enterprise
          costs for crops or livestock, these expenses will be double counted.

       Crop and Forage Enterprises
                                                       corn            soybean             0               0              0          0                 0              0               0
Help   Avg. Cost Per Unit of Yield                     $2.13            $4.83            $1.66          $35.00         $1.90       $2.10             $2.10          $4.40           $0.06
Help   Cost/unit adjustment for leased acres           0.0%             0.0%             0.0%            0.0%           0.0%       0.0%              0.0%           0.0%            0.0%
       Cost by Enterprise                            $479,250         $231,840            $0              $0             $0         $0                $0             $0              $0
                       Total Crop Cost of Production $711,090
       Livestock Enterprise                                Breeding Livestock Enterprises            Non-Breeding Livestock Ent.           Do Not include interest or
                                                     Cow-Calf           Sheep       Other brdg lvstk    Feeders       Stockers             depreciation in the per unit (per bu.,
Help   Cost per Pound Produced                         $0.00            $0.00            $0.00           $0.00         $0.00               per pound, per ton, per cwt, etc.)
       Purchase Cost Mrkt Lvstk (Dollars per pound)                                                      $0.00         $0.00               costs of production.
       Purchase Weight Per Head (Enter in pounds)                                                          0              0
       Lvstk cost by enterprise                         $0                $0              $0              $0             $0
                     Cost Per Breeder/Feeder Animal     $0                $0              $0              $0             $0
                  Total Livestock Cost Of Production    $0
       Cost of Production (Crops & Lvstk)                                  $711,090
Help   Cost of production Overhead Percentage                                3.00% << Example, enter 2% as 2% not 102%.
                                                                            $21,333
       Total cost of production, Crops and Livestock                       $732,423



                                      << modified for Xcelsius
                                                   Asset and Liability Calculations for the Balance Sheet, Cash Flow and Income Statement
                                           Beginning                                                                                                                                                                                                                                                                                                                 Total
                                             Asset                                                                                                                                                           Real Estate         Principal    Mach & Equip.         Principal    Bldg & Improvements      Principal    Breeding Livestock       Principal        Sh o rt er Term         Principal
                                             Value                                                      Liabilities      Short Term Long Term                                                  Period   Principal    Interest   Rem aining   Principal   Interest   Rem aining    Principal    Interest   Rem aining   Principal     Interest   Rem aining   Principal       Interest   Rem aining
Crop Acres/Units Owned                        1150                                                         Interest Rate     7.0%          6.0%                                                   1      $22,110     $104,880   ########     $12,963     $9,310      $120,037      $2,193      $1,575       $20,307       $0           $0            $0      $15,156         $10,885    $140,344
Average Value Per Acre                       $3,800                                                           Payment #         4            15                                                   2      $23,437     $103,553   ########     $13,871     $8,403      $106,166      $2,347      $1,421       $17,960       $0           $0            $0      $16,217         $9,824     $124,127
                Total Cropland Value       $4,370,000                                                        # Payments         8            30                                                   3      $24,843     $102,147   ########     $14,842     $7,432       $91,325      $2,511      $1,257       $15,450       $0           $0            $0      $17,352         $8,689     $106,774
                                                           Depreciation                                                                                                                           4      $26,334     $100,657   ########     $15,880     $6,393       $75,444      $2,687      $1,081       $12,763       $0           $0            $0      $18,567         $7,474      $88,207
Livestock Acres Owned                          0             or Capital            Ending                                              Beginning and Ending Liability Estimates                   5      $27,914      $99,077   ########     $16,992     $5,281       $58,452      $2,875       $893         $9,888       $0           $0            $0      $19,867         $6,175      $68,340
Average Value Per Acre                         $0          Replacement           Asset Value                                             Beginning    Beginning       Ending      Ending          6      $29,589      $97,402   ########     $18,182     $4,092       $40,270      $3,076       $692         $6,813       $0           $0            $0      $21,257         $4,784      $47,083
               Total Rangeland Value           $0           Expense by           Adjustment           Percent Debt             %          Annual       Annual        Annual       Annual          7      $31,364      $95,626   ########     $19,454     $2,819       $20,816      $3,291       $477         $3,522       $0           $0            $0      $22,745         $3,296      $24,338
                                                            Asset Type          Total Dollars            By Asset Type       Debt        Principal     Interest      Principal    Interest        8      $33,246      $93,745   ########
Total Real Estate (Crop + Range)           $4,370,000            $0                  $0                    40%             $1,748,000     ($49,989)     ($77,001)     ($52,989)    ($74,002)      9      $35,240      $91,750   ########
Mach & Equip.                               $380,000          $38,000                $0                    35%               $133,000     ($15,880)      ($6,393)     ($16,992)     ($5,281)     10      $37,355      $89,635   ########
Bldg & Improvements                         $150,000          $15,000                $0                    15%                 $22,500      ($2,687)     ($1,081)      ($2,875)       ($893)     11      $39,596      $87,394   ########
Breeding Livestock                             $0                $0                  $0                     0%                      $0           $0           $0            $0           $0      12      $41,972      $85,018   ########
                                                  Totals     $53,000                 $0                Total Liabilities   $1,903,500      $68,556       $84,475       $72,855      $80,176      13      $44,490      $82,500   ########
                       Total Asset Value   $4,900,000                                                                                                                                            14      $47,160      $79,831   ########
                                                                                                      Debt/Asset Ratio         29.17%     Checksum     $153,032      Checksum     $153,032       15      $49,989      $77,001   ########
                                                                                                                                                                                                 16      $52,989      $74,002   ########
Purchase/Contribute/Distribute Section                                                                                                                                                           17      $56,168      $70,822   ########
                                            Purchase       Depreciation        Asset Value             Percent                                                                                   18      $59,538      $67,452   ########
         Capital Asset Purchase             Price or        on Assets           Plus for              Purchased           Loan         Additional    Additional     Added       Added            19      $63,110      $63,880   ########
             or Contribution                 Value          Purchased        + Contribution           Asset that is      Proceeds       Ending        Ending       Principal    Interest         20      $66,897      $60,093   $934,660
             or Distribution                of Asset        This Year        - for Distribution        Financed          Received      Principal      Interest    End Next Yr End Next Yr        21      $70,911      $56,080   $863,749
Machinery & Equipment                          $0              $0                   $0                    0%                $0            $0            $0            $0          $0             22      $75,165      $51,825   $788,584
Real Estate (Land & Buildings)                 $0              $0                   $0                    0%                $0            $0            $0            $0          $0             23      $79,675      $47,315   $708,908
Breeding Livestock                             $0              $0                   $0                    0%                $0            $0            $0            $0          $0             24      $84,456      $42,534   $624,452
                                               $0              $0                   $0                                      $0            $0            $0                                       25      $89,523      $37,467   $534,929
                                                                                                                                                                                                 26      $94,895      $32,096   $440,035
                                                                                                                                                                                                 27     $100,588      $26,402   $339,447
Asset Purchase Example                     $100,000          $10,000                $0                    90%                                                                                    28     $106,624      $20,367   $232,823
Step 1: Buy Equipment                      $100,000                                 $0                                                                                                           29     $113,021      $13,969   $119,802
Step 2: Include Financing                  $100,000                                 $0                    90%                                                                                    30     $119,802       $7,188         $0
Step 3: Include Financing & Depreciation   $100,000          $10,000                $0                    90%
Reset Asset Purchase to zeros                 $0               $0                   $0                     0%
Description
Turn Example Off



Minimize Tax Purchase Exmple ON/OFF
                                               MPCI Basic Unit Insurance Option: Example below allows only 3 Basic


Help Activate MPCI Basic Unit Insurance Option (Y or N) >>                                         n
                                                                                                                     Basi
                                                                                                 Enter a Y for each crop cov
              Crop Qualifites for MPCI Coverage (Y or N) >>         Y              Y               Y
                                                                   corn         soybean            0
      Total Number of Planted Acres in This Enterprise            1500            1000             0
      Acres of each crop in Basic Unit # 1                         800            500
Help APH Yield History                                             130             37
Help APH Yield Coverage Election-by Crop                           75%          75.00%
Help Ownership Level This Basic Unit                              100%          100.00%
Help FCIC Establish Market Price                                  $3.00          $6.50
Help Market Price Election                                        100%          100.00%


      Calculated Yield Coveage Level                               97.5          27.75             0
      Actual Harvested Yield                                       150             42             60
      Quantiity Indemnity Payment Level                             0               0              0
      Calculated Market Price Coverage Level                      $3.00          $6.50           $0.00
      Maximum Possible Indemnity Payment by Crop                $234,000        $90,188           $0
      Estimated Indemnity Based on Acutal Yields                    $0             $0             $0
      Total Potential Indemnity Payment This Basic Unit         $393,844
      Esttimated Total Indemnity This Basic Unit                    $0

                                                              Acres Remaining after allocation to Basic Unit # 1
                                                                   700            500              0
                                                                                                                     Basi
                                                                                                 Enter a Y for each crop cov
              Crop Qualifites for MPCI Coverage (Y or N) >>         Y              Y               N
                                                                   corn         soybean            0
      Acres of each crop in Basic Unit # 2                         200
Help APH Yield History                                             130
Help APH Yield Coverage Election-by Crop                           75%
Help Ownership Level This Basic Unit                               66%
Help FCIC Establish Market Price                                  $3.25
Help Market Price Election                                        100%


      Calculated Yield Coveage Level                               97.5             0              0
      Actual Harvested Yield                                       150             35              2
      Quantiity Indemnity Payment Level                             0               0              0
      Calculated Market Price Coverage Level                      $3.25          $0.00           $0.00
      Maximum Possible Indemnity Payment by Crop                 $63,375           $0             $0
      Estimated Indemnity Based on Acutal Yields                    $0             $0             $0
      Total Potential Indemnity Payment This Basic Unit          $63,375
      Esttimated Total Indemnity This Basic Unit                    $0

                                                              Acres Remaining after allocation to Basic Unit # 2
                                                                   500            500              0
                                                                                                                     Basi
                                                                                                 Enter a Y for each crop cov
              Crop Qualifites for MPCI Coverage (Y or N) >>         Y              Y               N
                                                                   corn         soybean            0
      Acres of each crop in Basic Unit # 3
Help APH Yield History
Help APH Yield Coverage Election-by Crop
Help Ownership Level This Basic Unit
Help FCIC Establish Market Price
Help Market Price Election


      Calculated Yield Coveage Level                                0               0              0
      Actual Harvested Yield                                       30.2            35              2
      Quantiity Indemnity Payment Level (bu/cwt)                    0               0              0
      Calculated Market Price Coverage Level                      $0.00          $0.00           $0.00
      Maximum Possible Indemnity Payment by Crop                    $0             $0             $0
      Estimated Indemnity Based on Acutal Yields                    $0             $0             $0
      Total Potential Indemnity Payment This Basic Unit             $0
      Esttimated Total Indemnity This Basic Unit                    $0

                                                              Acres Remaining after allocation to ALL Posible Basic Units In
                                                                   500            500              0
                                                                   corn         soybean            0
Help Total Potential Indemnity Payment                          $297,375        $90,188           $0
      Premium Rate Percenage                                       6%              6%             0%
      Premium Payment by Crop for All Basic Units                $17,843         $5,411           $0
      Percentage Premium Subsidy                                   55%            55%            55%
      Actual Premium Paid by Producer                            $9,813          $2,976           $0


      Total Premium Actually Paid                                $15,088
      Total Indemnity Paid Given Actual Yields                      $0
Help Net Benefit of Crop Insurance                                  $0
e below allows only 3 Basic Units for illustrative purposes.



                             Basic Unit      1 of 3
   Enter a Y for each crop covered crop that you would like to insure.
                      N              Y                 Y          N       N       N
                      0              0                 0           0      0       0
                      0              0                 0           0      0       0
                                    200               300        500
                                     40                25         30
                                   75.00%         75.00%       75.00%
                                  100.00%        100.00%       100.00%
                                    $2.00             $3.25     $3.50
                                  100.00%        100.00%       100.00%


                      0              30               18.75      22.5     0       0
                      1             31.5              27.2       24.5    14.8    1077
                      0              0                 0           0      0       0
                     $0.00          $2.00             $3.25     $3.50    $0.00   $0.00
                      $0           $12,000        $18,281      $39,375    $0      $0
                      $0             $0                $0         $0      $0      $0




 to Basic Unit # 1
                      0             -200              -300       -500     0       0
                           Basic Unit        2 of 3
   Enter a Y for each crop covered crop that you would like to insure.
                      N              Y                 Y          N       N       N
                      0              0                 0           0      0       0




                      0              0                 0           0      0       0
                     1           31.5              27.2         24.5           14.8    1077
                     0            0                 0            0              0       0
                    $0.00        $0.00          $0.00          $0.00           $0.00   $0.00
                     $0           $0               $0            $0             $0      $0
                     $0           $0               $0            $0             $0      $0




to Basic Unit # 2
                     0           -200              -300        -500             0       0
                          Basic Unit      3 of 3
 Enter a Y for each crop covered crop that you would like to insure.
                     N            Y                 Y            N              N       N
                     0            0                 0            0              0       0




                     0            0                 0            0              0       0
                     1           31.5              27.2         24.5           14.8    1077
                     0            0                 0            0              0       0
                    $0.00        $0.00          $0.00          $0.00           $0.00   $0.00
                     $0           $0               $0            $0             $0      $0
                     $0           $0               $0            $0             $0      $0




to ALL Posible Basic Units Insured (In This Example, only 3 units available)
                     0           -200              -300        -500             0       0
                     0            0                 0            0              0       0
                     $0         $12,000       $18,281         $39,375           $0      $0
                     5%           6%               6%           6%              0%      0%
                     $0          $720          $1,097         $2,363            $0      $0
                    55%          55%               55%         55%             55%     55%
                     $0          $396              $603       $1,299            $0      $0
MPCI Coverage levels and
Insurance Premium Subsidy
               Premium
  Coverage      Subsidy
    Level        Factor
    50%         67.00%
    55%         64.00%
    60%         64.00%
    65%         59.00%
    70%         59.00%
    75%         55.00%
    80%         48.00%
    85%         38.00%
                       Percent Crop Revenue           100%                          Percent Livestock Revenue           100%          Percent Gov. Payments        100%
            Percent Cost of Production - Crops        100%                Percent cost of Production - Livestock        100%

                                             Assets                                                         L iab ilities
 Balance Sheet                      Beginning         Ending                                      Beginning            Ending       Income Statement - Accrual Adj.                       Income
 Cash on Hand                            2,500         156,114  Accounts Payable (Exp)                 1,000               1,000    Cash Income (adj. for cull lvstk sales)              $1,124,576
 Crops Held for Feed (Exp)                   0               0  Accrued Interest (Exp)                84,475              80,176    Non-Cash Income Adjustments                                    0                   Qu ick (an d Dirty) In co m e Tax Calcu latio n Fo r Cash B asis Taxp ayer
 Crops Held for Sale (Inc)             150,000         150,000  Current Principal                     68,556              72,855    Non-Cash Income (Raised Brdg Lvstk)                            0
 Market Livestock (Inc)                      0               0  Other Current Liability (Exp)         10,000              10,000    Capital Gain/Loss on Breeding Lvstk (Net)                      0                   Include Taxes In the Statements at Left     (Y = Yes, N= No)          n
 Other Current Assets (Inc)             15,000          15,000  Short Term Notes Payable (Exp)             0                   0                  Gross Revenue                          $1,124,576
 Cash Invt Growing Crops (Exp)          20,000          20,000  Other Current Liab. (Not Adj.)             0                   0                                                         Expense                       Cash Income                                                      ########
 Supplies&Prepaid Exp. (Exp)             5,800           5,800  Def. Tax on Current Assets                 0                   0    Cash Expense (Excluding Interest)                       732,423                    Cash Expense                                                     $732,423
 Total Current Assets                  193,300         346,914  Operating Loan Carryover                   0                   0    Non-Cash Feed Inventory Adjustment                             0                   Depreciation                                                      $53,000
 Non-Current Assets                                             Total Current Liab.                  164,032             164,032    Other Non-Cash Non-Interest Expense                            0                   Cash Interest                                                    $104,983
 Mach. & Equipment                     380,000          342,000 Non-Current Liabilities                                             Depreciation (Land, Bldgs, Equip.)                       53,000                    Taxable Income                                                   $234,170
 Breeding Livestock                          0                0 Prin. on T.D. & C.L.               1,321,569            1,248,713                  Total Operating Expense                  785,423                    Exemptions and Standard Deductions                                 $6,000
 Real Estate (Land, Bldgs, Impr)     4,520,000        4,505,000 Total Business Liab.               1,485,600            1,412,745   Cash Int. Exp. - T.D. & C.L.                             84,475                      Taxable Income After Exemptions and Standard Deductions        $228,170
 Total Business Assets               5,093,300        5,193,914 Business Net Worth                 3,607,700            3,781,169   Cash Int. Exp. - Operating                               20,508                    Combined State & Fed Tax Rate                                         32%
                                                               Change in Equity From Beginning to End of Year             173,469   Non-Cash Interest Expense                                 (4,299)                  Estimated Income Tax                                              $73,015
                                                                                                                                                   Total Expense                           $886,107
 Cash Flow Statement                                  Inflows                                                        OutFlows
 Crop Sales & Net Insurance Payments                          Operating Expenses
                                                      1,080,392                                  No Interest >          732,423     Net Business Income From Operations                     238,469
 Mrkt & Cull Livestock Sales                                  Other Cash Business Expense
                                                              0                                                               0     Net Business Income                                     238,469
 Government Payments                                     44,185
                                                              Cash Int. Exp. - T.D. & C.L.*                              84,475     Income & SS Taxes (Cash & Non-Cash)                           0                    To tal In terest Exp en se
 Other Cash business Income                                   0
                                                              Cash Int. Exp. - Operating             7.000%              20,508     Net Income                                             $238,469                    Cash interest TD & CL                       84,475
 Operating Loan Proceeds               80%              585,938
                                                              Loan Prin. Payments - T.D. & C.L.                          68,556                                                                                        Cash Operating interest                     20,508
 Loan Proceeds Capital Assets                                 Breeding Livestock Asset Purchases
                                                              0                                                               0     Statement of Owner Equity                                                          Non-cash Interest                           (4,299)
 Non-Business Inflows/Revenue                                 Mach & Equip & Real Estate Purchase                             0     Beginning Net Worth (Cost/Mrkt)                       3,607,700                                 Total Interest Expense        100,684
 Other Nonfarm Inflows                                      0 Owner withdrawals                                          65,000     Net Income                                  +           238,469
 Other Nonfarm Inflows                                      0 Cash Taxes Paid (Income & SS)                                   0     Non-Business Cash Inflows                   +                 0                    No n -Cash In co m e Ad ju stem en ts
 Total Cash Inflows                                $1,710,514 Other Cash Outflows (Not Expenses)                              0     Owner Withdrawals (Cash)                     -           65,000                    From Balance Sheet                               0
 * T.D. = Term Debt, C.L. = Capital Lease                                                              Subtotal        $970,962     Asset Valuation Change/Cont./Distrib.       +/-               $0                   Raised Breeding Livestock                        0
                                                              Operating Loan Prin. Payments                            $585,938     Calculated Ending Net Worth                 =         3,781,169                 Total Non-Cash Income Adjustments                   0
                                                              Total Cash Outflows                                    $1,556,900     Reported Ending Net Worth (Cost/Mrkt)                 3,781,169
                                                              Annual Net Cash Flow (never < zero)                       156,114                    Discrepancy                                    0                    Retain ed Earn in g s                     $173,469




Note: This program is designed to teach concepts of financial analysis and get a rough initial look at a farm/ranch operation. It is not intended and you can not use this to make detailed                            Alet L evels fo r selected item s
analysis of an individual operation. It does not have the capability to analyze detailed information.                                                                                                                  Net Farm Income From Operations                          30000      100000
                                                                                                                                                                                                                       Net Income After Taxes                                   40000       75000
 Additional Information Needed for Ratios                                                                                                                                                                              Retained Earnings                                        40000      100000
 1) Value of Operator and Unpaid Family Labor & Management                                                    0                                                                                                        Owner W ithdrawal                                        40000       75000
 2) Payment on Operating Debt Converted to Term from Previous Years                                           0                                                                                                        Total Operating Expenses                                325000      375000
 3) Total Annual Payments on Personal Liabilities                                                             0                                                                                                        Interest Expense                                         20000       40000
                                                                                                                                                                                                                       Depreciation Expense                                     35000       50000




                                                                                                                                                                Taxes ON
                                                                  Label Menu                                       Display Status                               Taxes OFF
                                                                  Graphic Display                                                                                     Y               << No tax calculations
                                                                  Financial Statements                                                                                N               << Turn tax calculation on.
                                                                  Ratios Table                                     Display Values
                                                                  Ratio Graphic                                          0       = Display Off
                                                                  Description                                            1       = Display On

                                                                                                                   Xcelsius Display Control
                                                                                                                   Asset Purchase Description OFF
                                                                                                                   Asset Purchase Description ON
                                                                                                                   Display Status for Asset Purchase Example

                                                                                                                   Display Status for Graph/Table/Description
          Financial Statement Ratios                                                                                                    The ranges of values suggested for financial ratio benchmarks are guidelines and will vary by the type of operation and the way they were calcualted.


Liquidity                                                                                                                                                                                                                                          Green = Sound Financially      Yellow = Caution           Red = Take Immediate Action                                            University
  Current Ratio                                          Beginning                        Ending                              Generally Financial Statement Ratios and Measures                               Case Farm          University of Nebraska1,2                   University of Vermont Extension 3            Purdue University4               Purdue University5      of Minnesota
           Current Assets                            193,300                      346,914                                     Better If Liquidity                                                       Beginning Ending             Green      Yellow        Red               Green       Yellow     Red                  Green     Yellow       Red     Average   High Profit     FinBin6
           divided by Current Liabilities            164,032         1.18         164,032              2.11                    Larger     Current Ratio                                                   1.92     2.20              >1.50    1.0 to 1.50     <1.0                >2        1 to 2      <1                  >1.50 1.00 to 1.50 <1.00          3.1       3.3           1.48
  Working Capital                                                                                                                                 Value Range                            1
          Current Assets                             193,300                      346,914                                                                                              1.5
          - Current Liabilities                      164,032     $29,268          164,032          $182,883
Solvency
  Debt/Asset Ratio                                      Beginning                       Ending                                 Larger        Working Capital                                             $40,889      $53,373               Positive Number                                                               Compare to Business Expense;                               60,304
          Total Business Liabilities              1,485,600                      1,412,745
          divided by Total Business Assets        5,093,300      29.17%          5,193,914            0.272                           Solvency
  Equity/Asset Ratio                                                                                                          Smaller   Debt/Asset Ratio                                                  23.43%      22.75%         <.30      .30 to .60     >.70              <30% 30% to 60% >60%                        <20% 20% to 60% >60%             32%        26%           51%
          Total Business Equity                   3,607,700                      3,781,169                                                 Debt to Asset (mostly rented/leased land)                                                                                                                                        <30% 30% to 70% >70%
          divided by Total Business Assets        5,093,300       70.83%         5,193,914            0.728                                    Value Range                  0.3
  Debt/Equity Ratio                                                                                                                                                         0.7
          Total Business Liabilities              1,485,600                      1,412,745
          divided by Total Business Equity        3,607,700          0.41        3,781,169            0.374                    Larger        Equity/Asset Ratio                                           76.57%      77.25%         >.70      .40 to .70     <.40              >70% 40% to 70% <40%                        >80% 40% to 80% <40%                                      49%
Profitability                                                                                                                                  Equity to Asset (mostly rented/leased land)                                                                                                                                  >70% 30% to 70% <30%
  Rate of Return on B usiness Assets                                                                                                                 Value Range                 0.4
          Net Business Income From Operations                                      238,469                                                                                       0.7
          + Business Interest Expense                                              100,684
          - Value of Operator and Unpaid Family Labor & Management                       0
            Divided by Average Total Business Assets                             5,143,607            0.066                   Smaller        Debt/Equity Ratio                                             0.31         0.29         <.50     .50 to 1.50    >1.50              <43% 43% to 150% >150%                      <25% 25% to 150% >150%                                    103%
                                                                                                                                               Debt to Equity (mostly rented/leased land)                                                                                                                                   <42% 42% to 230% >230%
  Rate of Return on B usiness Equity                                                                                                                 Value Range                 0.5
          Net Business Income From Operations                                      238,469                                                                                       1.5
          - Value of Operator and Unpaid Family Labor & Management                       0
            Divided by Average Total Business Equity                             3,694,435            0.065


  Operating Profit Margin Ratio                                                                                                         Profitability
          Net Business Income From Operations                                      238,469                                     Larger     Rate of Return on Business Assets                                            2.94%                                                    >5%       1% to 5%     <1%                  >5%   1% to 5%        <1%         7%        14%          7.00%
          + Business Interest Expense                                              100,684                                                   Rate of Return on Business Assets (mostly leased land)                                                                                                                         >11% 3% to 11%        <3%
          - Value of Operator and Unpaid Family Labor & Management                       0                                                         Value Range               0.01
            Divided by Gross Revenue                                             1,124,576            0.302                                                                  0.05

  Net B usiness Incom e
          Calculated by Matching Revenues and Expense (Accrual) Plus                                                           Larger        Rate of Return on Business Equity                                         1.88%                                                    >10% 5% to 10%         <5%                  Compare to Othe Non-farm          6%        18%          7.30%
          Gain or Loss on the Sale of Capital Assets                                          $238,469.48                                            Value Range               0.05
                                                                                                                                                                                0.1
Repayment Capacity
  Term Debt and Capital Lease Coverage Ratio                                                                                   Larger        Operating Profit Margin Ratio                                            11.22%                                                    >35% 20% to 35% <20%                        >25% 10% to 25% <10%             16%        32%          16.20%
          Net Business Income From Operations                                     238,469                                                            Value Range                      0.2
          + Total Non-Business Income                                                   0                                                                                            0.35
          + Depreciation/Amortization Expense                                      53,000
          + Interest on Term Debt                                                  84,475
          + Interest on Capital Leases                                                                                         Larger        Net Business Income                                                      $47,409                                                                                                                                                        47,559
          - Total Income Tax Expense                                                     0                                                           Value Range                   30000
          - Owner Withdrawals                                                       65,000    $310,944.71                                                                         100000
          Divided by
           Scheduled Prin. & Int. on Term Debt & Cap. Lease                       153,032
                                              Ratio Value                                              2.03
  Capital Replacem ent and Term Debt Repaym ent Margin                                                                                  Repayment Capacity
          Net Business Income From Operations                                      238,469                                     Larger     Term Debt and Capital Lease Coverage Ratio                                    1.41                                                   >135%110% to 135%<110%                      >150%110% to 150%<110%                                     140%
          + Total Non-Business Income*                                                   0                                                       Value Range                1.1
          + Depreciation/Amortization Expense                                       53,000                                                                                 1.35
          - Total Income Tax Expense                                                     0
          - Owner Withdrawals                                                       65,000
          = Capital Replacement and Term Debt Repayment Capacity                  226,469                                      Larger        Capital Replacement and Term Debt Repayment Margin                       $13,296                                                                                                                                                        16,684
          - Payment on Unpaid Operating Debt From Prior Year                             0                                                           Value Range             5000
          - Principal Pymnts-Current Portions of Term Debt & Cap. Leae              68,556
          - Total Annual Payments on Personal Liabilities (if Not in Withdrawals)*       0
          = Capital Replacement and Term Debt Repayment Margin                                $157,913.18


          * To evaluate for the business only, do not include items marked with an *                                                    Financial Efficiency
                                                                                                                               Larger     Asset Turnover Ratio                                                        0.2623                                                    >40% 20% to 40% <20%                       Depends on type of operation      35%        43%          43.40%
Financial Efficiency                                                                                                                              Value Range                          0.2
  Asset Turnover Ratio                                                                                                                                                                 0.4
          Gross Revenues                                                         1,124,576
          divided by Average Total Business Assets                               5,143,607           0.2186                   Smaller        Operating Expense Ratio                                                  76.96%                                                    <60% 60% to 80% >80%                        <55% 55% to 65% >65%             63%        52%          72.80%
  Operating Expense Ratio                                                                                                                      Operating Expense Ratio (mostly leased land)                                                                                                                                 <65% 65 to 75% >75%
          Total Operating Expense                                                  785,423                                                           Value Range                0.6
          - Depreciation & Amortization Expense                                     53,000                                                                                      0.8
            Divided by Gross Revenues                                            1,124,576           0.6513


  Depreciation Expense Ratio                                                                                                  Smaller        Depreciation Expense Ratio                                                5.66%                                                    <10% 10% to 20% >20%                      Compare to capital replacement      8%        7%           6.20%
          Depreciation & Amortization Expense                                       53,000                                                           Value Range                       0.1
          divided by Gross Revenues                                              1,124,576           0.0471                                                                            0.2

  Interest Expense Ratio
          Total Business Interest Expense                                          100,684                                    Smaller        Interest Expense Ratio                                                    5.70%                                                    <10% 10% to 20% >20%                        <10% 10% to 20% >20%              8%        6%           6.90%
          divided by Gross Revenues                                              1,124,576           0.0895                                           Value Range                      0.1
                                                                                                                                                                                       0.2
  Net Farm Incom e From Operations Ratio
          Net Business Income From Operations                                      238,469
          divided by Gross Revenues                                              1,124,576           0.2121                    Larger        Net Farm Income From Operations Ratio                                    11.67%                                                    >20% 10% to 20% <10%                        Look at trends; varies with      20%        37%          14.10%
                                                                             Check Sum               1.0000                                          Value Range              0.1
                                                                                                                                                                              0.2


Liquidity                                                       Beginning        Ending            Alert Level Values
  Current Ratio                                                      1.18            2.11                  1            1.5
  Working Capital                                                 $29,268        $182,883            40,000      75,000
                                                                                                                                        1
Solvency                                                                                                                                Source: Nebraska Farm Business Association
                                                                                                                                        2
  Debt/Asset Ratio                                                   0.292           0.272               0.3            0.7                 Source: Achieving Success With a Business Plan, University of Nebraska, John Wichmann, John Hanson and Thomas Dorn
                                                                                                                                        3
  Equity/Asset Ratio                                                 0.708           0.728               0.4            0.7             Developed by: Rick Wackernagel, Dennis Kauppila, and Glenn Rogers, University of Vermont Extension, 1998
                                                                                                                                        4
  Debt/Equity Ratio                                                  0.41            0.374               0.5            1.5                 Purdue University; http://fbfm.ace.uiuc.edu/cooperators/PDF/fin%20char/financialbenchmarks.pdf; Modified from Daivd Kohl
                                                                                                                                        5
Profitability                                                                      Ending                                                   Purdue University, Interpreting Financial Performance Measures , William Edwards, 1998
                                                                                                                                        6
  Rate of Return on Business Assets                                                 6.59%               0.01      0.05                  University of Minnesota, Summary FinBin report, Average of ALL farms in Database from 1993 to 2004.
  Rate of Return on Business Equity                                                 6.45%               0.05       0.1
  Operating Profit Margin Ratio                                                      0.30                0.2      0.35
  Net Business Income                                                            $238,469             30000     100000
Repayment Capacity
  Term Debt and Capital Lease Coverage Ratio                                         2.03               1.1       1.35
  Capital Replacement and Term Debt Repayment Margin                             $157,913              5000     150000
Financial Efficiency
  Asset Turnover Ratio                                                               0.22                0.2            0.4
  Operating Expense Ratio                                                            0.65                0.6            0.8
  Depreciation Expense Ratio                                                         0.05                0.1            0.2
  Interest Expense Ratio                                                             0.09                0.1            0.2
  Net Farm Income From Operations Ratio                                              0.21                0.1            0.2
                                             Check Sum                            100.00%
Labels starting in column B on this tab are from the Accrual Adjusted Income Statement
    on the Statements tab and are shown in this color font.
Labels starting in column C on this tab are from the Balance Sheet
   on the Statements tab and are shown in this color font.

             Income Accrual Adjustments from Current Assets on the Balance Sheet
                                                          Ending       Beginning                  Accrual
                                                         Inventory      Inventory                Income
                                                        Value From     Value From               (+/-) to the
                                                          Current        Current                  Income
Non-Cash Income Adjustments                               Assets          Assets                Statement
    Crops Held for Sale (Inc)                            $150,000       $150,000                    $0
    Market Livestock (Inc)                                  $0             $0                       $0
    Other Current Assets (Inc)                           $15,000         $15,000                    $0
                      Total Non Cash Income Adjustment made to the Income Statement                 $0

Non-Cash Income (Raised Brdg Lvstk)                                                                 $0
    See the Enterprise tab, cells I21 throught J31 for the non-cash value of raised breeding livestock.

              Expense Accrual Adjustments made on the Accrual Income Statement
                                                                 Ending        Beginning          Accrual
                                                                Inventory       Inventory        Expense
                                                               Value From      Value From       (+/-) to the
                                                                Current         Current           Income
Non-Cash Feed Inventory Adjustment                               Assets          Assets         Statement
    Crops Held for Feed (Exp)                                      $0              $0               $0


                                                                 Ending        Beginning          Accrual
                                                                Inventory       Inventory        Expense
                                                               Value From      Value From       (+/-) to the
                                                                Current         Current           Income
Other Non-Cash Non-Interest Expense                              Assets          Assets         Statement
    Cash Invt Growing Crops (Exp)                               $20,000         $20,000             $0
    Supplies&Prepaid Exp. (Exp)                                  $5,800          $5,800             $0
                                                               Liabilities     Liabilities
    Accounts Payable (Exp)                                       $1,000          $1,000             $0
    Other Current Liability (Exp)                               $10,000         $10,000             $0
    Short Term Notes Payable (Exp)                                 $0              $0               $0

                                                                 Ending        Beginning          Accrual
                                                                Inventory       Inventory        Expense
                                                               Value From      Value From       (+/-) to the
                                                                Current         Current           Income
Non-Cash Interest Expense                                      Liabilities     Liabilities      Statement
    Accrued Interest (Exp)                                      $80,176         $84,475          ($4,299)


Income & SS Taxes (Cash & Non-Cash)                             Only Accrual Adjustment Portion of Taxes
    Def. Tax on Current Assets                                     $0             $0             $0




    Other Current Liab. (Not Adj.)                  << This line is not adjusted on the Income Statement.
                                                   Please read the comment on the Statements page of this spreadsheet.
                                                   This line is provided to help illustrate the Statement of Owner Equity's
                                                   function and reconciliation of the financial statements.
    List of defaults and/or assumptions used in this spreadsheet template.
1       Bulls are assumed to have a three year life and are always fully depreciated when they are sold.
              All bull revenue is treated just like "ordinary income," even though it is listed under Capital Gains/Loss on Breeding Livestock.
              See the Enterprise tab of this template in cell block I21 throught L31.
2       Bulls (or other male breeding livestock, Rams for example) are the only type of purchased breeding livestock included in the initial setup.
              Female breeding livestock are always raised and assigned a base value by the user. However, the user can enter any type
              of purchased breeding livestock on the Enterprise tab under the Purchase/Contribute/Distribute Capital Asset section starting
              in cell A81
3       No asset sales, or death loss of breeding livestock, etc. is allowed in this spreadsheet.
4       The beginning and ending values on the balance sheet for current assets and liabilities are (should) always be the same.
              This eliminates accrual adjustments on the income statement prior to the user entering or changing a specific number to
              show an accrual income or expense. It simplifies teaching the concepts.
5       Due to the design of this spreadsheet, interest and depreciation are handled separately from the basic cost of production figures entered
        for each enterprise. This is necessary to calculate and display principle and interest for all loans on the various financial statements.
        It is important that these numbers are separated out of the enterprise cost of production figures, to the best of your ability, remembering
        that this template is basically designed to teach interaction of and how to interrupt financial statements.
              Separate out:
                    Interest or opportunity costs charged on operating costs
                    Interest or opportunity costs charged on capital assets
                    Depreciation on capital assets included in the enterprise costs
6       Some basic assumptions about timing are included when making entries to illustrate assest purchases, receiving captial contributions,
        or making capital distributions.
              It is always assumed, in this system of financial statements, that these activities happen at the end of the
              year when there is no opportunity for a purchased or contributed asset to generate any additional
              revenue to be included in this analysis year.
              The assumption with distributed assests is the revenue from that particular asset has already been received during the
              year and no reduction in revenue occurs because of the asset's disappearance from the balance sheet during the year.
              This assumptions helps to illustrate the fact that the mear purchase of an asset will not increase equity, I.e. you can
         not buy equity. You must employ the asset in a manner that earns equity through increasing the revenue stream or
         reducing the expense stream, hence increasing net income.
7   If using the purchase capital asset section on the Enterprise tab, the assumption is that, if financied, no P & I payment is made on the asset
    during the year of purchase. Other expenses like taxes, and insurance are also not made during the year of purchase.
ng Livestock.

 in the initial setup.

ection starting




n figures entered
al statements.
ity, remembering




l contributions,
s made on the asset

								
To top