Situation

A client in the real estate sector owns multiple properties across multiple business units. It outsources the management and operation of its properties to third-party management companies. The management companies co-ordinate the listing of accounts payables in spreadsheets, which is then sent to the real estate owner to approve cash payments in due process.

The client wanted to improve the internal controls or corporate governance surrounding the documentation of creditor invoices by various suppliers i.e. electricians, builders, the payment approval process, and the subsequent payment of each invoice.

Task

The real estate client approached Strategize to build a business model, which could

• attach the documented pdf invoice for each invoice line item;
• unhide/hide rows in the data entry range, based on whether they contain input data;
• flex between multiple languages given the client’s stakeholders were multi-lingual;
• restrict the use (editing or data input of cells) of columns for certain users of the spreadsheet, such as the management company, the client’s employees and the president;
• colour code specific columns that are the province of these various users;
• colour code each line item depending on whether the invoice has been paid or is outstanding;
• date “stamp” the areas of the spreadsheet, which require verification, authorisation and approval of payment for each invoice;
• dependant drop-down lists to enable a user to specify if an invoice is a capital, operating or construction expense;
• key-log, document and record the use of the spreadsheet by all users;
• auto shutdown feature if the business spreadsheet remains inactive after 15 minutes; and
• seamless use between DOS and Mac computers

Early on into the mandate, it became pertinently clear Strategize would need to undertake a lot of spreadsheet testing, in particular the need to re-engineer some features of Microsoft Excel that are currently not compatible in Excel for Mac. Hence, Strategize needed to compose various VBA macros to satisfy the needs of the real estate client, in order to deliver a robust, internal control tool for its payments of accounts payables.

Action

The primary focus with the successful execution of the mandate, was to build a business spreadsheet that could operate in both MS Excel and Excel for Mac; given Microsoft Windows and OS X Mac operating systems are different. ActiveX macros had to be changed to Form Controls given Mac does not currently support ActiveX; however it helped to materially reduce the file size of the spreadsheet.

Given the “Allow Users to Edit Ranges” function doesn’t exist in Excel for Mac, customised macros with passwords (to unlock the column) were written to essentially restrict or ring fence an authorised users’ ability to input or edit certain columns of data.

 

Strategize created a hyperlink (to an external folder) to the pdf invoice file for each line item, which met the client’s need for the spreadsheet to also act as a repository for its invoices.

The “radio button” dependent drop down lists safeguarded users from making the erroneous error of selecting, both the operating and capital expense columns for the allocation and payment authorisation of an invoice. A drop-down list permitted the seamless translation of the spreadsheet between German and English without the use of macros.

The detailed macros for key logging, auto shutdown, date stamping and unhiding/hiding of rows had to be composed in a way, which ensured a) the model remained operational and b) each macro properly functioned and didn’t crash the financial model.

 

Results

Realising the client’s need to access its pdf invoices delivered immense benefit to the client; whereby they could access the electronic invoice, verify the invoice detail and monetary amount, and approve/pay the invoice in a more effective and efficient way.

The macros to restrict users’ access to certain columns of the spreadsheet, provided the client with an internal control relating to its accounts payables, in terms of the governance surrounding the verification, approval and authorisation of invoice payments.

The hidden keylog worksheet and macro delivered an added level of documentation or oversight, surrounding the real estate client’s corporate governance of its cash outflows; by way of recording all keystrokes by users of the spreadsheet.

The clean and aesthetic appearance of the best practice Strategize model, by way of the conditional cell formatting to flag and highlight outstanding or near-due invoices; improved the client’s understanding of its list of accounts payable invoices.