Requirement
The client, a large UK Insurance company needed a tool to create an accurate Capital Expenditure Budget for the ongoing replacement of its IT hardware assets. They also wanted detailed information to supplement the depreciation budget that was managed by the finance department.
Challenges
The IT department had to propose and justify its Capital Expenditure Budget but had insufficient financial information to build it. The technical asset inventory provided the base data but a model was required to generate financial information that could be justified.
Solution
The main asset listing with some of the calculated fields used to create the annual budgets
The solution was a custom built Excel spreadsheet. Each asset was listed along with purchase date and information about what it was used for. A model was built using 5 tables that were used to work out when and if the asset was to be replaced, and how much it would cost. This was then used to produce the annual CapEx and Depreciation Budgets. An override facility was also provided to handle exceptions to the modelling assumptions.
The spreadsheet made extensive use of vlookup and hlookup, named ranges and Pivot Tables for the budget and ageing summary reports.
Design Highlights
This spreadsheet was used by the IT department to produce a well articulated budget submission that could be defended to the finance department. It also allowed them to prioritise the replacement of ageing equipment and ensured that funds were used in the most cost effective way for the business.