New Partnership for Deltek Vision and Deltek Professional Services consulting

I am excited to announce the official launch of Version X Solutions!

Version X Solutions is a new partnership that brings together a combined 30 years of Deltek Vision knowledge and experience under one umbrella.

We offer a wide and practical range of products and services including custom modules, custom reports, and even a cloud hosting service.

We’ve put together the beginnings of an inventory, and will continue to add to it as we come up with new ideas and do more interesting work for clients. If you have an extension we need, we’d love to hear about it!

Of course we still offer hourly consulting for nebulous or “figure it out as we go” projects.

You can read more about us and our current offering here.

What does this mean for existing customers?

Our new partnership will not adversely affect the existing relationships I have built with you over the years.

You will continue to get the same great friendly, responsive and most importantly, knowledgeable consulting services you have come to rely on from me.

Thank you for your continued business and I look forward to working with you in the near future.

From now on, please refer to the Version X Solutions site for information about products and services offered.

 

 

 

Loren… what are you doing? Why haven’t you been writing more blog entries lately?

Happy Holidays!

I have been a bit remiss with this blog. I apologize. My excuse is being busy with the following projects:

CALIFORNIA:
1. Automating labor rates in Deltek Vision with an outside platform being the system of record. This includes automation of prevailing wage rate schedules, dealing with differentials and changing benefits elections. I will be using lots of workflows and webservices to pull in employee pay rates, benefits selections and related data to reside in Vision.  Then more workflows to automate maintenance of labor cost tables based upon employee selections of day/night shift, labor category (staff type) and reg, ovt, or double time.   I will be making extensive use of Vision’s new custom infocenter features.

This project should be completed some time in the spring.  If you’re suffering from prevailing wage rates and schedules maintenance in your own organization let me know and maybe I can help.
NEW YORK:
2. A client has been using Deltek Vision for a few years but they do not feel they are getting everything out of it that they can.  Thus, this is kind of a re-implementation project.  They previously converted from Advantage and need help making the conceptual and practical transition from a pure accounting system to a project accounting system.  They have yet to implement timesheets, expense reports and workable methods for project Work Breakdown Structure.

Both of these projects require a lot of work just scoping and defining what needs to be done. It’s very challenging!

It’s much more challenging than just building a custom report or a small workflow process with a relatively narrow set of requirements and small set of users. This is essentially taking a set of ad-hoc defined business process, and designing a proper automated solution to manage the operational data of that process (or those processes). This normally requires the integration of existing applications or solutions, importing or incorporating historical data, transforming historical data when data architecture corrections are made, and designing new processes, policies and procedures for using the new tools we put in place.

Lastly, training and documentation will be required to make sure that the processes and tools put in place actually get used!

Luckily most of the work can be done off site, thus keeping down travel costs and allowing me to work in relative isolation so I can concentrate!

If you’re ever in down town Austin, TX give me a call and I can show you my office and some cool stuff!

 

 

Credit card transaction import module finished

I have been remiss on posting to my site/blog. I have been swamped, buried in work.

The credit card import module has been completed and running now for 1 week in a production environment with no bugs yet.

Here are the basic functions of the module..

1. User downloads credit card transaction detail from Chase as a CSV file, then drops file into designated directory. User can name file whatever they want.

2. Module imports csv data, archives file and renames it. All imported data is linked back to that specific file so user can look up individual transactions with ease, all the way back to original csv data. If user imports the same file several times or if there are duplicate transactions the process cleans it up.

3. Module processes imported transactions into employee expense reports, adding transactions to appropriate open expense report or creating a new one if needed. Transactions will be added to either the appropriate report based on transaction date, or in current expense report if the appropriate one has been submitted.

4. Employees open their automagically created expense reports and add project number information as well as account for each detail, adding descriptions if needed.

5. User runs a report which shows any import errors, employee expense entry errors or missed imported transactions.

6. When expense reports have been completed, they are posted using normal Vision procedures.

7. When a payment is made to the credit card company, user locates payment serial number in imported data (all transactions are imported, even chargebacks and payments) and processes that payment using a workflow. The workflow creates journal entry files to allocate the cash effect to all projects based upon which details were paid by that payment, and where the employees assigned them. This shows cash flow on a per project basis when that credit card payment is made.

8. Journal entry file can then be posted through normal Vision procedures.

If you would like more information about this module please contact me.

The question you should ask of all your consultants…

I get this question every once in while… “do you write code?” or “do you know… abc technology”?

Here is the short answer:

Yes. I’ve been writing code since I was 10, when my parents bought me my first computer and I stayed up late at night trying to write my own word based adventure games… often referred to as “zorg” games. You may or may not know about them. I wrote my first professional business application when I was 15, for a hospital in France. I was an exchange student at the time and earned an entire week’s worth of skiing in Switzerland for that app. It was a database that kept track of patients and the drugs they were trying for clinical trials.

I write code about every day… sometimes it’s in javascript, visual basic (excel, microsoft access etc), or html but most of the time it’s in T-sql or what’s officially called “transact SQL”. This particular language is a procedural language (hence the name “stored procedure”) that allows you to do lots of cool stuff at the database level. The advantage in this case, is that Deltek Vision has an open database so you can do almost anything to it using T-SQL.

However… the real question that people should ask of me and their consultants is:

“Can you determine what is the thing we need created to automate our business… and can you determine if we should build or buy it… and can you design it, then build it or implement it?”

and

“Do you understand our business and are you interested in providing service and making us buy technology to enhance our bottom line and make our business more efficient as opposed to a lot of other consultants who are only interested in making a quick buck and not really being thorough with what we need and not really developing and meeting a well thought out list of requirements?”

Now THAT is the question you should ask me.

Go ahead… I dare you.

CREDIT CARD TRANSACTION IMPORT AUTOMATION – PART 3

Ok I’ve made a lot of progress over the past two weeks. This is quite a complex project actually… but it’s about finished.

The final results look basically like this:

1. use SSIS (Sql Server Integration Services) to grab the csv files from designated directory and import them into a raw data table.

2. move any transactions from raw import data to the processing table… exclude any transactions that have already been imported.

3. using some data transformations in the form of user defined functions (UDF), make the data look like expense detail transactions

4. insert the expense detail transactions into the expense reports for the corresponding employees

5. allow employees to edit their expense transaction details in their pre-populated expense report

6. show the entirety of credit card expense transactions in a report… that shows the imported data, next to the matching expense detail records in Vision, allowing employees to accurately fill out details on their expenses, and make sure that everything has been accounted for.

7. for the payment to the credit card company, assign the cash disbursement to the different project, phase and tasks reflected in the expense detail transactions so that projects show the cash out for those expenses.

So I’ve completed everything except step 7, which I will complete over the next day or two.

Some really cool things this does:
– puts the CSV files in an archive folder and assigns a unique, logical name to them so you don’t have to worry about losing raw data history

– allows you to compare imported data from the CSV file to the actual data in expense reports by employee and expense report

– allows you to trace any imported transaction back to the actual CSV file it came from… it shows you the file name on the report

– reconciles the amounts imported vs. the amounts in the employee’s expense report so you can make sure you didn’t miss anything

– shows data that was imported into the processing section, but didn’t actually make it into an employee expense report

In a couple weeks I will provide more details on how it actually works with some screen shots of the results.

In the mean time, here is a snapshot of the entire process looked up wrapped up in an elegant diagram in SSIS. Of course, this doesn’t show any details or code… just the big picture logic of it. It’s interesting all the same.

Have an excellent day!

I’ll be at Deltek Insight in a couple weeks, and will be teaching a break out session:
PS-80 Putting Data in the Right Place

If you happen to be at Insight feel free to come say “hello” and give me some feedback on my blog!

Cheers!

Credit Card Transaction Import Automation – Part 1

I have this great client (Champion Technologies out of Baton Rouge, LA) giving me some very interesting projects lately. They are in the business of automation, and have recently asked me to automate their credit card transaction import process.

This will be a multi-part blog entry describing the process we go through in automation process.

PART 1

Initial conversation:
We had an extended gotomeeting (90 minutes) to talk about what needed to be done for this project. Since the client had already built a partially automated solution for this using a combination of excel spreadsheets (we love those don’t we?), macros, and some concatenated sql statements in text files… he had a pretty good idea of the requirements for the process. He walked me through the basics of what is already being done.

The first thing I do when attacking a project like this is go back through the recorded gotomeeting and take notes. Here are some of the notes I’ve taken:

Big Picture Requirement:
1. accounting user downloads a csv file from the credit card company and puts it in a designated folder somewhere on the internal network

2. accounting user clicks some kind of button (custom info centers will come in handy for this one!)

3. the process sucks up the file and imports it into Vision employee expense reports, exposing those reports to employees as “in progress” so they can go in and fill in the details on each transaction

4. once completed (the details are filled in) a report will be run to make sure that all transactions in the csv file(s) have been imported and have details added to them. Once all transactions are ready, the expense reports can be submitted and posted.

5. when the credit card bill is paid by the company, the automated entries by Vision for the payment are removed, and replaced by a detailed set of transactions that go down to the individual project level, possibly transaction level. This will show cash outflows by project for those expenses.

Some of the implied and explicit “gotcha” requirements:
– if a user imports a csv file twice, make sure that those transactions are not duplicated
– if a user imports once single transaction twice, make sure it’s not duplicated
– if a user splits an expense entry into two so they can share it between two or more projects… make sure the reconcile still works
– what happens if the accounting department un-posts the individual expense transactions? do they unpost the payments as well?

— end —

Cheers!

Case Study – Vision Implementation Restructure

Ronald Lu & Partners, HK had implemented Deltek Vision 5.1 in 2008 and had been live for 2 years.

I received an email from the founder’s son, Bryant Lu saying that although they had been live for 2 years, their accounting department was still doing a lot of accounting and reporting on spread sheets. After I spent a week in their offices I found the following major issues that needed correcting:

0. They needed to convert from Vision 5 to 6, which we accomplished in 2 weeks.

1. Their work break down structure for every project was the same, displaying the 8 standardized phases of a project that they took each project through, but not reflecting the actual milestones and deliverables of the project.  Thus, they were unable to produce meaningful reports from Vision that could show budget vs. actuals, expected revenues or EAC reports tied to project milestones.  Instead, they were maintaining a completely separate set of financials outside of Vision on an organized but extensive set of Excel spreadsheets so they could track consultant expenses, milestones, invoices, and receivables.

2. Their entire set of P+L statements and Project Earnings reports were being produced from spreadsheets in order to show P+L across companies or divisions. The reason for this was because after going live they had adopted a new organizational structure which consisted of studios (which is common in architectural firms).  Because the studios were not represented in the data in Vision, it was not possible to produce any reports based upon studio from the application.  This affected all of their performance reporting and financial reporting.

Just tackling these two issues implied that I would have to do the following:

1. Restructure the work break down structure (WBS) for every project in the database to reflect true milestones and deliverables (or start over from scratch with a new database and a new go live date, which would mean re-importing historical data… essentially doing their implementation completely over).

2. Either convert their current organization scheme to one that considered the studios, or find some way to generate their entire financial report set using a custom field in the project infocenter which would reflect the studio a project was assigned to.
While the problems and solutions described above are only numbered 1 and 2, there are many implied requirements that would come into play as I executed this project over the next 4 months.  Some of the issues I encountered and resolved were:

– In Deltek Vision, when you key convert a WBS item like a phase or task to move it to another project, phase or task (not merely changing the number), it completely ignores the invoice history data during the key conversion process.  Since we decided we would key convert every project into a new, meaningful work breakdown structure, this meant that I had to write sql scripts to convert the invoice history as well.

– The projects that RLP undertakes can last many years, and often have over 100 phases and tasks. The interface for Deltek Vision does not yet allow a user to update fee or budget amounts in a grid like format that shows the entire project structure.  During the execution of this project I built a Microsoft Access based application they could use for both data entry for the conversion (to enter the WBS for projects into Vision) and to use after the conversion for adjusting fees and budgets for projects, as well as enter fees and budgets for new projects.

– Since it was decided not to restructure their organizational scheme in Vision (this was a management decision) it was necessary to develop custom reports to satisfy their entire financial reporting set.  This included P+L reports, balance sheet reports and forecasting reports.

– Because of the language barriers (Vision is not yet localized in Cantonese, the Hong Kong version of Chinese), it was decided we would not implement Resource Planning.  Also, some projects could actually commence and continue in execution for months before actually becoming a signed project.  Thus, it was necessary to in a sense “bill time to opportunities”.  So instead we came up with a solution for revenue and opportunity forecasting using the project info center only.

There were of course many more issues that we worked through and solved throughout the course of the project.  Bottom line is that we completed and went live with the new project structure and report set by December of that year.  Working on site, together with their executive and accounting departments we were able to complete 6 to 8 months of work in 5 months.  

Here are some example images and reports from after their implementation:

Click for full size.

This shows the mission critical business path, order of priority for phased restructure, area of effort and timeline for the phased restructuring of their Vision implementation.
(I will be adding to this case study with more images and samples… later this week)