Ag. Loan Analyzer Input Exercise

Exercise 1:How to Enter Dates in Ag. Loan Analyzer

There are two ways you can enter dates into the relevant cells in the tool. For example, to enter December 1, 2015 you can do the following:

=date(2015,12,1) or 12/1/2015

Instructions for Exercise 1:

  1. Use the Ag. Loan Analyzer version entitled “Ag. Loan Analyzer – AGLEND – Practice”
  2. Try entering the following dates into the (1) “Date of Loan Application” and (2) the “Preferred Initial Disbursement Date” cells:
  1. September 15, 2015
  2. January 1, 2016
  1. Once you have entered these dates, inspect the Outflows, Inflows, CashFlowstatements, Loan Calculator and Family Expenses tabs to understand how the model has automatically changed.

Exercise 2: How to fill out Summary of Plots to Be Farmed Schedule

The “Summary of Plots to be Farmed” schedule on the Client Info tab allows the loan officer to detail the following information for up to 10 crop and 10 livestock lines of production:

  1. Area of land used for cultivation/production
  2. Location of the land
  3. The anticipated agricultural activity taking place on the land
  4. Person responsible for the land and production

Once you have completed this schedule, it will pre-populate all of the relevant areas throughout the model, and form the basis for some of the automatic calculations in later tabs.

Please note: you mustpress the “Production Activity Complete?” button at the bottom left once you are down. Otherwise, the production activity will not be updated throughout the model and the automatic calculations will not function.

Instructions for Exercise 2:

  1. Set the date of the Loan Application to today, and the initial requested disbursement date for September 1, 2015.
  2. Go to “Client Info” tab and try inputting the following production information (you can make up the location); after you are done with each, make sure to press “Production Activity Complete”. Then, after each exercise below, go to the (1) Outflows, (2) Inflows and (3) CVP Analysis tabs to understand what has changed.
  3. A multi-crop farmer with 3 types of crops: wheat, corn and vegetables, all of which are 1 ha
  4. A mono-crop farmer that only produces Palm Oil on 100 ha.s of land
  5. A farmer who produces coffee, maize, wheat, garden vegetables, as well as catfish, hens and goats.

Exercise 3: How to Recognize Cash Inflows and Outflows

Step 1:input the first calendar month and year in which the CF is recognized

Step 2:input the last calendar month and year in which the CF is recognized

Example 1: one time, discrete CF

  • $1,000 cash outflow occurring one time, in Jan. 2016

Example 2: continuous, recurring CF

  • $1,000 cash outflow occurring every month from Jan. 2016 – May 2016

Example 3: non-continuous, recurring CF

  • $1000 cash outflow occurring every month from Jan. 2016 – May 2016, and then again from July 2016 to Dec. 2016

Please note: the Ag. Loan Analyzer is a very versatile tool and depending on the information available, the loan officer may have to make creative adjustments as necessary to recognize cash inflows and outflows. For example, if the user only knows the total amount of the overall cash flow, without knowing the constituent parts (i.e the yield rates, price per unit, etc), the user can always just enter the total amount in the Price column and enter “1s” in every other column to maintain the multiplicative identity of 1 * X = X.

Example 4: only know CF without other information

  • $1000 cash inflow occurring in Jan. 2016

Instructions for Exercise 3:

  1. Continue using the Ag. Loan Analyzer version entitled “Ag. Loan Analyzer – AGLEND – Practice”
  2. Set the hired labour expense to USD 10, and the opportunity cost of family labour to USD 5 on the Control Panel tab
  3. Assume a 24 month loan

Please input the following in the Outflows/Inflows section as appropriate:

Expenses

  1. The expense for purchasing 400 bags for packaging of garden vegetables. Each bag costs USD 0.05 and the expense occurs in December 2015. Every hectare of vegetables cultivated requires 100 bags, and there are a total of 4 hectares.
  2. A recurring expense of USD 100 for purchasing seeds for vegetable production, occurring in October, November and December of 2015.
  3. Expense related to the purchase of 50 kg bags of fertilizer for maize production. Every hectare of maize requires 25 bags, and each bag costs USD 6. There are a total of 2 hectares of maize being cultivated in two cropping seasons: one of these in September 2015 and the other in March 2016.
  4. Rent expense of USD 200 per month, starting at the time of initial disbursement
  5. An annually occurring expense of USD 100 related to school fees that occurs every April, prior to the beginning of classes.
  6. The labour expense related to hiring one person for one day to help with harvesting maize. Assume this occurs in October 2015.
  7. A USD 15 labour expense in October 2015 related to hiring one person for one day to help with harvesting maize
  8. A ploughing related hired-labour expense of USD 100, recognized over 2 months in December 2015 and January 2016.
  9. The expense related to buying 1 water pump for USD 120 in September 2015. Assume that the useful life of the pump is 5 years
  10. The expenses related to paying the USD 25 installment of an existing loan once every quarter (three months) starting in Jan 2016 and ending in Dec 2016.

Inflows

1. Income related to the sale of 2 hectares of tomatoes in January 2016. Assume a yield of 50 tons/hectare, a selling price of USD 85 per ton; and family consumption of 200 kg.

2. Income of USD 1,000 resulting from the sale of the wheat harvest, occurring in October 2015.

3. Non-farm income related to the receipt of remittances from family abroad of USD 100 every two months.