The Ewe Planner is a spreadsheet-based tool used to make five-year annual projections for a new or existing ewe-lamb operation.
The EwePLAN is an Excel-based program, and different versions of Excel may affect how well the program works on your computer. Different versions of Excel may have compatibility issues with the EwePLAN program due to the different changes developed by Excel over time. When you open the spreadsheet, you may see a box that says “Macros have been disabled.” Click on the “Enable Content” button to allow all the formulas in the spreadsheet to function properly.
It is recommended that the first time you open the EwePLAN program that you save the original file with its original name in a specific place. Once you start working with the program and input your data, save the program with a new name and include the date to help you know your most recent version. When you make changes and save with the same file name, the new data will be saved over the previous saved data not leaving you a copy of the prior information.
When the EwePLAN spreadsheet is first opened, it should show the “Disclosure” sheet. Once you read the “Disclosure” sheet and agree with the statement, you will then click on the “Agree” button to proceed. You will then be taken to the “Intro” sheet and provided a brief explanation of the different worksheets that are located in the program.
Once you scroll down the “Intro” sheet there will be a choice of two buttons to get you started. If you want to start by doing a previous years cost of production, you click on the “Go To COP Calculation Sheet” button. If you want to go directly to the input sheet to get started with inputting numbers, you click on the “Go To Input Sheet” button. Once you are past this stage you can move freely through the program by clicking on the worksheet name at the bottom of the program that you wish to work in or review.
The cost of production calculation sheet will provide you an area to describe your operation and input your past production and financial numbers. The first column is number of head or units; the second is price per head or unit; and the third column is the total cost of that specific input. It is important to at least complete the first (number of head/unit) and third columns (total cost). Once the information is inputted the second part of the sheet will provide a summary of the total costs and break down the costs per ewe and per lamb which can be used in the “Input” sheet.
The numbers provided on the “Input” sheet in the base model are some general long-term average numbers or prices to provide users with a starting point. Each operation may differ significantly with some numbers depending on the type and structure of their operation, management, location of the operation, and size of the operation.
The “Input” sheet is the main worksheet of the program where all the data is inputted to help calculate the net income projections in the program. There are 237 inputs, which are coloured in grey boxes. Most numbers can be changed from year one to year five. A number inputted into year one will typically be carried through to year five. The input can be changed for any year, but once changed in year two-to-five, the carry forward formula will be broken and numbers in year one would not be carried forward. Numbers will then need to be inputted manually as required in that input row. As an example: If you change the number of ewes in year two from year one (400 to 500) then the numbers inputted in year two will be carried forward to year five. If you decide to go back to 400 for year two, then you will need to manually type 400 back in year two.
Most of the numbers in red are calculated with formulas and locked to preserve the formula. There are a few inputs such as input 6, 7, and 11 that are in red and can be changed. Once changed the formula will be lost and needed to be manually calculated. The areas of the spreadsheet that are not meant to be changed are locked to preserve the headings and formulas. If you type in a cell you will know quickly if the cell can be changed or not.
In year one of most inputs there is a small red triangle at the top right of the cell. If you move your cursor on that cell, it will give you a brief explanation of the input and what it is asking for.
The “Monthly” sheet provides monthly cash flow estimates for the first 12 months of year one of an operation. The sheet is mostly open and cells can be altered in the monthly sheet to better reflect the month different expenses occur and revenues received. On the third page (below), a producer can alter the number of feeding days (grey boxes) by animal type in each feed ration which will change the monthly feeding costs. Feed costs are a very large part of the production costs and can change the monthly cash flows significantly.
Once the data is inputted into the “Input” sheet the focus will shift to total revenues and total expenses. The “Income and Expense” sheet provides a summary of total projected revenues and expenses for years one-to-five. Expenses are divided into variable and fixed. Net Income is projected for each year along with Net Cash Flow, which adds back depreciation and capital injections; and subtracts capital payments. The projections are also broken down per ewe and per lamb for the first three years. Changes to the projections can be made by going back into the “Input” sheet and altering the input numbers.
There are several other areas in the program that provide valuable analysis and information for the producer in regards to the projections. Based on the year one numbers, the “Sensitivity Analysis” sheet will provide the net income/loss sensitivity to market prices, lambing productivity, and changes in feed and pasture costs. The sensitivity will provide an estimate of net income/loss in year one with respect to changes in these main inputs and provide an idea of break-even points in the price and productivity changes.
The “Feed” sheet will provide a summary of the main feed requirements of the operation broken down by animal type based on the number of animals and rations outlined on the “Input” sheet. A summary of the total flock requirements is provided at the top of the sheet. The “Balance” sheet will provide a projected statement of assets and liabilities over the five years with a detail statement of changes in assets and liabilities. The “Investment” sheet will look at the nominal rate of return of the operation by estimating the annual net income over the net annual equity at the start of each year. A value or opportunity cost of labour needs to be included in the input sheet to get a proper return on investment. The “Financing” sheet provides a summary of the breakout of the individual loans and annual payment of interest and principal. The “Depreciation” sheet summarizes the assets by type with different depreciation rates for the different asset categories. The depreciation rates can be manually changed in the different assets categories with the changes in depreciation reflected on in the “Income and Expense” sheet along with the “Balance” sheet.
Download the Ewe Planner