Follow

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
    mceclip0.png 

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. 

mceclip2.png

a. 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

b. Click the icon Inset Line mceclip3.png

c. Type ‘+’ to generate a sub total and a name to identify what the sub total means in the STOCK CLASS (e.g. + ewes)

d. Repeat the above to insert line totals for additional stock classes 

mceclip1.png

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

Calculate Natural Increase (Estimated Births) 

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 Natural Increase box 

2.    Press F5 or click the Link mceclip2.png icon to display the link dialog box

3.    At the Enter Link window, click on 

·         Select Source Worksheet – click Stock Reconciliation

·         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.    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 mceclip3.png icon to display the link dialog 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 Column - Open No

·         Select Source Row - Same ROW

·         Select Adjustment - Percentage

·         Adjust by - type 2 

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 to OK the selections 

5. To complete the SELLING NUMBERS mceclip4.png and BUYING mceclip5.png columns, enter your data as it stands. There are no formulae links for these two columns. These two columns are permanently linked to the Purchases 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.    Repeat the 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. Enter the Transfers Out mceclip6.png column by completing the following formula 

a. Click into the Transfers Out column (e.g. 3 YR EWES)

b. Press F5 or click the Link mceclip7.png icon to display the link dialog box

c. Select Stock Reconciliation, Before Transfers select where the sheep are coming from (e.g.3 YR EWES) then No Adjustment

d. Click mceclip8.png tick or press F10 to save data  

e. Now go to the Transfers In column of the 3 YR EWES. Repeat TRANSFER IN formula but change stock classes to suit. 

Note: If you are transferring data out of the Lambs, you can use the link Stock Reconciliation, Before Transfers, Lambs, No Adjustment. 

2. Enter the Transfers In mceclip9.pngcolumn by completing the following formula

 a. Click the Transfer In column (e.g. 4 YR EWES)

b. Press F5 or click the LINK mceclip10.png icon to display the link dialog box

c. Click Stock Reconciliation, Transfers Out select where the sheep are coming from (e.g. 3YR EWES), No Adjustment

d. Click mceclip11.png tick or press F10 to save data

e. Now go to the TRANSFERS OUT column of the next youngest stock class (e.g. 3 YR EWES)

Note: If you are transferring data into the Hoggets, you could use the link Stock Reconciliation, Transfers Out, Lambs, Divide By, 2. 

Before Transfers

 

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.

Trans. Out F5

 

The number of stock you want to have included in the transfer Recommended, press F5 and link to the Before Transfers. Alternatively, type a number

Trans In F5

 

The number of stock coming into this age group 

Recommended, press F5 and link to the Trans. Out for the younger year. This moves the stock up 1 year. Each box must be calculated using F5 and the right click Duplicate function does not work on this function. Note that the 2 Yr Ewes come from the Ewe Hoggets

Close No.

 

Agrimaster calculates the number of stock at the end of the season

Estimate Shearing Numbers 

Shearing Number F5

 

Recommended, press F5 and calculate the number of sheep 

Optional, type a number of sheep 

Used for planning the shearing costs and wool income in the Wool Sales and Crutching detail worksheets

There are multiple options for summarizing 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 mceclip13.png and Winter Grazed Ha mceclip14.png will calculate the stocking rate for your sheep enterprise. If stocking rate is not required, you may leave these columns blank

a. In the Stock Class column on the first available line type Stocked Area 

b. 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 

c. 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.

d. Click mceclip15.png 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. 


 

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments