The Ewe Planner is a spreadsheet-based tool used to make five-year annual projections for a new or existing ewe-lamb operation.
Operating instructions
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 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 of the formulas in the spreadsheet to function properly.
It is recommended that the first time you open the EwePLAN program, you save the original file with its original name. Once you start working with the program and input your data, save the program with a new name and include the date to reference your most recent version.
When the EwePLAN spreadsheet is first opened, it should show the "Disclosure" sheet. Once you read the "Disclosure" sheet and agree with the statement, 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 year's 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 names at the bottom of the program.
COP Calculation
The cost of production (COP) calculation sheet provides an area where you can 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 is the total cost of that specific input. It is important to at least complete the first (number of head/unit) and third (total cost) columns. 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.
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 of 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 years two-to-five, the carry-forward formula will be broken and numbers in year one will 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 inputs 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.
In year one, in of most of the 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.
Monthly Sheet
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 the different expenses occur and the 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.
Summary
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 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.