Instructions for Part A and Part B:
Company XYZ started the business on the 1 July 2012. Financial year ends on the 30 June. You need to set up a fixed assets register using the Excel template provided. Asset ID, asset item, cost price, purchase date, asset category code, location code, depreciation method, depreciation rate, useful life, disposal date and disposal values are given in the template. You are not allowed to change these values in the template.
Part A:
Using appropriate formulae, you need to specify the values for the following items using vlookups; if statements or formulae as specified below :
i. Asset Category descriptions (Column D) and branch names (Column F) (you must use vlookups)
ii. Depreciation expense, accumulated depreciations and carrying amount for each asset item for the following financial years: 2013, 2014 and 2015 (Column L to Column T) (you can use if statements and/or formulae).
Please note: if the asset is disposed within the financial year, the carrying amount should be zero. The accumulated depreciation and
depreciation expense are calculated up to the date of disposal.
Part B:
Using appropriate pivot tables and filtering functions, perform the following:
i. Show the total purchase price and total carrying amount (as at 30/6/2015) for each asset category (computer, equipment/machine, furniture and motor vehicle) in Adelaide, Melbourne and Sydney.
ii. Show the total carrying amount (as at 30/06/2013, 30/06/2014 and 30/06/2015) by asset category, with filter on to enable the selection of location.
iii. Show each asset category with purchases made after the 1 January 2013 in the Melbourne branch with their respective cumulative carrying amount.
iv. Show all the assets disposed from the Adelaide branch between 01/07/2014 and 30/06/2015 and display the maximum disposal item in each category.
v. Show all asset items purchased after 30/06/2013 in the Melbourne branch with their respective carrying amount (as at 30/6/2014) and purchased date.
Attachment:- Excel_Template.xlsx