Budgetary tool

Interestingly enough, not all projects always have to be about developing app from scratch.

VBA

Here’s is an example of quotation utility that has been used in managing countless small to medium projects. We are talking cumulative sum of hundreds of thousand of USD in projects value. It’s nothing elaborate just plane old MS Excel with data queries and VBA scripts.

Project requirements

Original requirement

  • Use Microsoft Excel
  • In correlation of preexisting manually created excel projects
  • Automation within excel
  • Share database across team so everyone can access it via budgetary tool

Additional upgrades

  • Load data from inventory management software client uses.
  • Update to tool to use client’s mother company data source.

How it works

There are three main sheets within main Excel budgetary tool, items data, services data and budgetary sheet.

Power query

Items data – necessary data that client uses in their projects as items in their quotations and budgets. Queried from their inventory system. Query loads, filters and formats data.

Service data – list of services and types of works client offers. Can sync with client’s it system but on client’s request doesn’t sync.

Budgetary tool – represents both quotation and followup reporting project state for client’s investors and customers. This part allows inserting items data via macro buttons in similar fashion as most resource and economical management software do. Client’s budgeter inserts all the items and their quantity. Tool basically calculates quotation on its own. It is formatted so that quick print to *.pdf creates professional looking quotation. Tool also serves as project resource management. It basically allows evidence of the budgeted items that have been “delivered” and can thus be invoiced.

Logic used behind the Excel tool has been improving overtime in cooperation with budgeters and project managers.

Client’s budgeters can do their own small changes like formatting on the spot without requiring IT support.

,

2 komentáře: “Budgetary tool”

  1. Yeah, I’ve been discovering the Excel beyond the tables and formula myself. It’s good enough way to automate part of my work.

    • I agree. It was quite interesting to help someone with this kind project for a change.

cs_CZCzech
%d bloggers like this: