In order to perform a high-level analytical review of your purchases you may want to compare purchases made to prior financial years. To compare purchases to historic data, you need a formula that will work out which financial year the asset was purchased. To calculate the financial year in which the purchase was made, we shall make use of the IF and the AND functions which will calculate whether or not the purchase date falls into a specific period, should that be the case, the financial year in which the purchase was made will be returned, otherwise zero will be returned. In this scenario, we will use the financial dates looked up from the lookup worksheet.
Note: Download the free asset register template Applies To: Microsoft® Excel® 2007, 2010 and 2013
1. Assets for 2011
- Select the West Coast asset register worksheet.
- Select cell N8
- Select the insert function button on the formula bar
- Locate the IF function
- Select Ok
- Nest the AND function into the IF by selecting the drop down arrow to the left of the formula bar
- Select more functions
- Locate the AND function
- Select OK
- Enter as per screen shot below
- The date in cell H8 must be between 11/07/2010 and 30/06/2011.
- To make the cell absolute ; so that that cell reference will not change:
o Place the cursor after A19 then press F4
o Place the cursor after A18 then press F4
- From the formula bar, select IF by placing your cursor after IF
- Enter as below
- If the date in H8 is between 01/07/2010 and 30/06/2011
- The value cell A17(2011) will be displayed
- Otherwise zero will be returned
- Select OK
- Place you mouse on the Autofill handle and double click to copy the formula down.
- The formula will find all assets purchased for the year 2011.
2. Assets for 2012
- Select N8
- Select the insert function button to edit the formula
- Place the cursor in the value_if_false option
- Delete the 0
- Select IF by selecting the drop down arrow to the left of the formula bar
- Select AND as per screen shot below
- Then enter as below
- The date in cell H8 must be between 01/07/2011 and 30/06/2012.
- To make the cell absolute ; so that that cell reference will not change:
o Place the cursor after A15 then press F4
o Place the cursor after A14 then press F4
- From the formula bar, select the second IF, by placing your cursor after IF
- Enter in the below
- Select OK
- Place your mouse on the autofill handle and double click to copy the formula down.
- The formula will find all assets purchased for the year 2012 including 2011.
3. Assets for 2013
- Select N8
- Select the insert function button to edit the formula
- From the formula bar, select the second IF
- From the value_if_false option ,delete the 0
- Select IF from the list by selecting the drop down arrow to the left of the formula bar
- Select AND as per screen shot below
- Enter as below
- The date in cell H8 must be between 11/07/2012 and 30/06/2013.
- To make the cell absolute ; so that that cell reference will not change:
o Place the cursor after A11 then press F4
o Place the cursor after A10 then press F4
- From the formula bar, select the third IF
- Enter in the below
- Select OK
- Place your mouse on the autofill handle and double click to copy the formula down
- The formula will find all assets purchased for the 2013 year ,including 2011 and 2012
By using the IF and the AND functions you are able to work out the financial year when the asset was purchased. Thus you can determine the number of assets purchased for each financial year. In the next tip we shall show you how to calculate depreciation using the straight line method.
