Sheep Reconciliation Worksheet

The Stock Reconciliation Sheet holds the summary data that will be copied over to the detail sheets. The steps below are explained using a completed Stock Reconciliation. 

Seeing the finished sheet helps you know what to expect from the function. The steps following each section will take you back to how to activate the calculation or connect to data in another location on the worksheet.

1. Start in the Stock Reconciliation worksheet.
2. Click the cursor on line one of the Stock Class. Use your enter key to progress to the next line. You can also use your arrow keys on your keyboard to move around.
    

Subtotal Opening Numbers

Agrimaster calculates subtotals for consecutive rows of numbers (no blanks rows). Insert a blank row before the next group of numbers to create another set of subtotals. Agrimaster displays subtotals as bold, red text. Subtotals have to be started from the Stock Class column. 

1. Enter your Stock Classes and Opening Numbers in the first two columns. It is important that the first five characters of each stock class are unique if the links that you will be entering are to calculate correctly. 
   
2. To insert a line total in between your different stock types, click on the line number to highlight the line, below where you wish to insert a line total.
3. Click the icon Insert Line.
    
4. Type ‘+’ to generate a subtotal and a name to identify what the subtotal means in the Stock Class (e.g. + ewes).
5. Repeat the above to insert line totals for additional Stock Classes.

Agrimaster will calculate subtotals for any consecutive numbers above and subtotals are displayed bold and red. 

Calculate Natural Increase (Estimated Births) 

Natural Increase (Nat. Inc, F5) - Press F5 to start a calculation based on the number of ewes and estimated birth rate. 

1. Go to the lambs row and click into the blank Nat. Inc, F5 box. 
   
   
2. Press F5 or click the Link icon to display the link dialogue box. 
   
3. At the Enter Link window, click on:
   
   • Select Source Worksheet - click stock Reconcilition. 
   • Select Source Column - click Open No.
   • Select Source Row - click +Ewe.
   • Select Adjustment - click Percentage of.
   • Adjust By - type the number that approximates your flock's birth rate (85% in this example).
     
4. Please Note: Agrimaster changes the box colour to yellow and calculates a number. 
   
5. Click on the green tick to ok the selection. 

Worksheet Colour Coding 

Agrimaster colour codes the boxes and text in your worksheet as you enter your data: 

• White boxes – type text or numbers into these boxes.
• Light grey boxes – type data or convert to data calculated by Agrimaster.
• Yellow boxes – data calculated by Agrimaster, such as a percentage of a number.
• Bold, red text – Agrimaster calculates subtotals of the rows above with no blanks.

Buy, Sell and Loss

• Buy No - Type the number to be purchased for this class of stock.
• Sell No - Type the number to be sold for this class of stock,
• Losses F5 - Press F5 to calculate the estimated number lost by stock class. 
  

Calculate Losses 

Estimate the number of stock you lose to natural causes using the Losses column.

1. Click into a blank box in the Losses F5 column.
2. Press F5 or click the Link icon to display the link dialogue box.
   • In this example, 2% of the Wether Hoggets are lost each season.
     
3. At the Enter Link window, click on:
   • Select Source Worksheet - Stock Reconciliation.
   • Select Source Comumn - Open No.
   • Select Source Row - Same Row.
   • Select Adjustment - Percentage. 
   • Adjust By- Type 2.
   • Please Note: When calculating losses for lambs, you would choose Nat. Inc in the Select Source Column as opposed to Open No. 
4. Click on the green tick. 
   
   
5. To complete the Sell No and Buying columns, enter your data as it stands. There are no formulae links for these two columns. These two columns are permanently linked to the Purchase and Sales Worksheets.

Copy Calculation 

Copy the same calculation to the next box down, as long as there are no blank boxes or different calculations. As an example, the Stock Class losses are estimated 2% for all rows. 

1.  Click into Losses for 4 Yr Ewes and press F5.
2. Complete the losses calculation using the steps in Calculate Losses, above.
3. Right click into the Losses box for 3 Yr Ewes.
   • Agrimaster will duplicate the calculation from the box immediately above.
4. Right click into the Losses box for the 2 Yr Ewes.
   • Agrimaster will duplicate the calculation from the box immediately above.
     

Track Ageing Sheep

Each season the stock becomes a year older. Transfer your sheep from one age group to the next age group at the end of the season. 

1. Trans. Out F5 is the number of stock you want to have included in the transfer recommended.
   Enter the Transfers Out column by completing the following formula:
   • Click into the Transfers Out column (e.g. 3 Yr Ewes).
   • Press F5 or click the Link icon to display the link dialogue box.
   • Select Stock Reconciliation, Before Transfers and select where the sheep are coming from (e.g. 3 Yr Ewes) then click No Adjustment.
   • Click green tick or press F10 to save data. 
   • Click Transfers In column of the 3Yr Ewes. Repeat Transfer In Forumula but change stock classes to suit.
     • Please Note: If you are transferring data out of the Lambs, you can use the link Stock Reconciliation, Before Transfers, Lambs, No Adjustment. 
2. Trans. In F5 is the number of stock coming into this age group recommended.
   Enter the Transfers In column by completing the following formula: 
   • Click the Transfer In column (e.g. 4 Yr Ewes).
   • Press F5 or click the Link icon to display the link dialogue box.
   • Click Stock Reconciliation, Transfers Out select where the sheep are coming from (e.g. 3 Yr Ewes), and click No Adjustments. 
   • Click green tick or press F10 to save data. 
   • Now go to the Transfers Out column of the next youngest stock class (e.g. 3 Yr Ewe).
     • Please Note: If you re transferring data into Hoggets, you can use the link Stock Reconciliation, Transfers Out, Lambs, Divide By, 2. 
3. Before Transfers is the number of stock before the stock is transferred to the next age group. Agrimaster calculates the number of stock left after sales and losses. The total is Open No. + Buy No. – Sell No. – Losses.
4. Close No. is that Agrimaster calculates the number of stock at the end of the season.

Estimate Shearing Numbers

Shearing Number F5 is used for planning the shearing costs and wool income in the Wool Sales and Crutching Detail Worksheets. 

There are multiple options for summarising the shearing numbers depending on your business. 

1. Press F5 and choose the number of sheep from Open No. (beginning of season) or Close No. (end of season). 
2. Press F5 and calculate a number as a percentage to estimate numbers less losses.
3. Type a number.
4. Press F3 to use the calculator to input a static number. 
   • In the example, the sheep will be sheared before being sold. The numbers from Sell No, Before Transfers, are added and typed into Shearing Number F5. 

Estimate Winter Sheep
The Winter Numbers and Winter Grazed Ha will calculate the stocking rate for your sheep enterprise. If stocking rate is not required, you may leave these columns blank.

1. In the Stock Class column on the first available line type Stocked Area.
2. On this same line, arrow across to the Winter Grazed Ha column, type in the grazed Ha (i.e. 600). This is the total area of Hectares to be grazed.
3. In the Winter Number column type the number of each stock class that you expect to carry during winter (July). These two columns are permanently linked to the Stocking Rate Worksheet.
4. Click the green tick to save the data in the Stock Reconciliation Worksheet and now progress to the Purchase Worksheet. 

Save Stock Worksheet Group 

Click on the green tick to save the Worksheets after data entry is complete.