Excel Dashboard

One of my favorite Excel project is to create dashboards for my client.  A dashboard is a one page overview of various KPIs (Key Performance Indicator).  The goal of the dashboard is to have 1 minute overview of how the KPIs are doing.  It allows the audience to easily pin point the issues within an organization.  For that reason, a Dashboard have to be very visual.  Symbols are used in lieu of numbers, charts are used instead of tables, colors are used to indicate good or bad.  It is not easy to design and construct a good dashboard.  It requires somebody who is not only analytical, but also artistic.

joshmastaaa is excellent. Very professional, fast, great communicator and persistent in his effort to make sure things are right. Went above and beyond to make sure my mac excel version would perform without any problems. Without question we will be using his services again.

Some of the common use of dashboard are:

  • Internet marketing.  There are many indicators to keep track for Internet marketing, some of the KPIs are: Impressions, Clicks, Click Thru Rate, Submitted, Visits, New Visits, and many others.  Further, these KPIs are drilled down by region, by media type, and various other ways.  A good dashboard will show the trend, current month data, and how it compared to the prior month.
  • Sales data.  In this  type of dashboard, Sales by channel, Top 10 data, comps data, and some historical trend are usually very useful.  It will easily identify how the sales is doing, compared to plan, positive or negative trend, and ability to quickly identify which channel require some work.
  • Strategy.  This is the type of dashboard used in conjunction with CRM (Customer Relationship Management) system.  The dashboard will summarize the customers, the projects, the regions, and Customer Satisfactions.  A good dashboard will help management to see which business unit require attention, which demographic will generate most sales, and what type of products are most profitable.
  • Company Overall.  A dashboard for CEO or CFO, where it shows the level of Inventory, Accounts Receivables, Sales data as well as Accounts Payables information. This type dashboard will layout the vision to the company leader for the areas to focus on.

These are just some type of dashboard I’ve worked on in the past.  Please visit: https://www.fiverr.com/joshmastaaa to see some of the services I offer.

Unbelievable person to with!!! Josh OVER-delivered on my expectations. He had such a mess I brought him and what resulted were very detailed questions he asked (although I didn't have the greatest answers) and what he gave me was an elegant instrument for my work and company. He is an artist!!


Data Cleansing


There’s no job too small or too large.  If you have a data dump from a system, and need somebody to clean the data, please contact us for a quote.  We can clean and reformat the data for your specific need.  Recently we have a client who have over 10,000 line items on a CSV file.  The ultimate goal was to have an upload-able file with 3 columns of First Name, Last Name and Email address for Mass Email purposes.

We had to clean the names on the original spreadsheet, separate the First Name & Last name.  We had to delete any titles or any data shown after the names, remove any duplicates, and make sure all of the names and email are in the proper format.  Proper format meaning, they are Capitalize on the first letter, and lower caps on remaining of the name.   Also to make sure no extra spaces etc.

Although it seems like a daunting task, but we have a very strategic and systematic way of doing it.  We were able to clean, reformat, and remove duplicates in less than 24 hours.

This is the second time I have used Joshmastaaa and it was EXCELLENT . . . again.
This is the second time I have used Joshmastaaa and it was EXCELLENT . . . again.

Job well done!! Another happy customer.  Our goal 100% satisfaction.

You can visit us at www.fiverr.com/joshmastaaa

Solution to Multi National Cash Forecasting

In order to solve all of these issues out lined in Multi National Cash Forecasting System, I choose a very unique solution primarily by utilizing the Cloud, Microsoft Excel, and Microsoft Access.  Each of the subsidiary have access to their own Microsoft Excel spreadsheet, which everyone is familiar with.  The Excel files are hosted in the cloud, so it’s available for the subsidiary as well as the Treasury Manager all the time.  Further, I created a database using Microsoft Access to consolidate all of the Excel data.  And finally, the consolidated data is exported back to Excel for Management reports.

Cloud utilization
Cloud utilization is very important as the Excel files are not moving.  Each of the subsidiary as well as the Treasury Manager have access to the spreadsheet 24/7.  Email traffic and the confusion of which version is the latest version is eliminated.  1 Subsidiary = 1 Excel spreadsheet, each week / each day they work on the same spreadsheet and just adding data into it.
Implementation of deadline & Report card
Although historically they had deadline when to submit the reports, late submission was a norm.  We implemented a deadline that everyone need to submit their report before they leave for the weekend.  We define the week as Sunday to Saturday.  Although most of the countries work from Monday to Friday, there are few exception that work on Saturday and Sunday and banks are open on those days.  We facilitated the spreadsheets for those days and we told everyone that the prior week data need to be there by the beginning of the next week.  On Monday morning, the Treasury Manager run the consolidation report, and within few minutes the Report card of all of the banks and the subsidiary owners comes up.  Next to the bank accounts is the status between 1 to 5.  5 Being completed and reconciled and 1 being missing transactions and balances.  Then the Treasury Manager publishes the report to everyone highlighting the subsidiaries with incomplete status.  This weekly Report Card encourages everyone to submit and reconciled their reports on time, as they do not want to be the highlights of the mass email.
Decentralized spreadsheets
Although all of the spreadsheets are hosted centralized on the cloud, however, each of the subsidiary is assigned their own Excel spreadsheet.  The Excel spreadsheet is uniform and locked down in few places.  They are locked in the Data Input area as well as Report Area, any specific special requirement will have to be approved by the Treasury Manager and implemented across all of the spreadsheets.  Although they are locked in specific areas, but each of the subsidiary are welcome and can create additional tab and schedules to support their report.
The 40+ input spreadsheets owned by each subsidiary are designed so that whenever there’s newer version, the essential data can be moved easily.  Within seconds anyone can be up and running on the latest version of spreadsheet.  The spreadsheet have 4 primary tab:
  1. Transaction input tab
  2. Balance input tab
  3. Report
  4. Company setup

Input and output are segregated for easy extraction.

Consolidator only accessible by the Treasury Manager.  It consolidate all of the data from 40+ subsidiary spreadsheets.  With a click of a button, it consolidate and produce few reports.  Within seconds a Report Card is produced, data is consolidated and ultimately management report is ready to publish.  The Report Card gave the Treasury Manager a snapshot of the prior week data.  It immediately shows who did what and who did not do what.

Multi National Cash forecasting system

Cash Forecasting system for Multi National Company
Cash Forecasting system for Multi National Company

Recently I’ve been asked by a Multi National company to create a Cash Forecast consolidation system.  A system which will have input from 40+ subsidiaries in various countries, consolidate the data, and have the ability to produce a weekly Cash Position report in USD.  Each of the subsidiaries operate on their local currencies, they forecast their own Cash Flow, as well as manage their own Accounts Payable and Accounts Receivable.  As a Multi National corporation, they have access to some of the biggest software.  For the most part, they were not very impressed with their solution.  They needed something that is quick to implement, easy to use, cost efficient, flexible, and finally require minimal training.

Currently the Treasury Manager at the Corporate headquarter is receiving about 40+ Microsoft Excel reports by email from each of the subsidiaries on the weekly basis.   The total of email communication can easily double as the subsidiaries sent different version of the files for the same week.  The reports although they are similar, they were not standardized.  There are few different variations, and rather hard to keep them the same as each of the subsidiary may have slightly different requirement and wanted to have extra schedules for them to keep.  Further, Microsoft Excel Cash Forecast reports were poorly designed, so it took a lot of time to extract the data from each of the Cash Forecast reports.  As the reports are received every week, the Treasury Manager would then copy and paste the data from each of the spreadsheet to the master spreadsheet.  Due to the poor design, lack of uniformity and lack of checks and balance, it would take the Treasury Manager about 4 days to consolidate and produce the management report, in which by that time would be too late as the data is 4 days old.
In summary, these are the issues they are facing:
  • Too much email traffic
  • Manual consolidation process
  • Lack of uniformity
  • Lack of control
  • Lack of commitment from the subsidiary and reports were often submitted late
  • Unorganized data to create any meaningful snapshot
  • Erroneous data – Actual data were not reconciled to the bank statements
  • Stale report produced to management

In order to solve all of these issues, I choose a very unique solution primarily by utilizing the Cloud, Microsoft Excel, and Microsoft Access.  Each of the subsidiary have access to their own Microsoft Excel spreadsheet, which everyone is familiar with.  The Excel files are hosted in the cloud, so it’s available for the subsidiary as well as the Treasury Manager all the time.  Further, I created a database using Microsoft Access to consolidate all of the Excel files.  And finally, the consolidated data is exported back to Excel for Management reports.

Please read the next article on How to Solve Multi National company with their Cash forecasting issue.

Love Fullerton

Participated on the second Love Fullerton annual serve day.  It is a city wide serve day involving 3000 volunteers, 8500 volunteer work hours, over 65 projects around the city.  It is a partnership of City Government, Local Businesses, Local Residents, Public and Private education, Chief of Police, The Mayor and City Council as well as 20+ churches in the neighborhood.  Love Fullerton motto is simply Demonstrating kindness, meeting needs and Impacting Lives.  It is one of the many Love your cities movements.  This year we are participating in Fullerton PD car wash & detail project.  A family project involving kids and adults working together to appreciate the Police Department.

Below are some of the pictures from that day, for more pictures and event info, visit their website www.lovefullerton.org.

2015-05-09 14.15.52 2015-05-09 15.19.25 2015-05-09 14.50.31 2015-05-09 14.38.54 2015-05-09 14.26.10


2015-05-09 14.21.24