Various Projects
Worked very closely with Senior Manager and Bank in order to get all the financial information needed for a period of 5 years time tot simulate the bank covenant for the same period.
The refinancing was for the an international Group based in UK with subsidiaries in Latin America, Asia and Pacific.
The starting point of the financial model was the general ledger and then work with a 5 year strategic plan by division and country. The model included:
- balance sheet,
- profit and loss,
- cash flow,
- assumption page,
- bank covenants,
Bank covenants
Travel & Expense system
Designed and implemented a new travel and expense policies (see Fig 2a) together with a new excel template (see fig 2b) to use by each staff and a new consolidation expense document.
Journal process and upload
Set up process to ensure every journal entries were checked before they were uploaded in the general ledger. The advantage in doing so:
- all journal entries have appropriate back up,
- all journals are reviewed by at least one manager,
- risk of poor journal entries is greatly reduced,
- manager is in more control about jobs done and how,
- less corrections on the balance sheet and profit & loss accounts,
Fig 3a journal template below shows the fields to enter:
Automation format and distribution of reports
Finance department typically prepares a lot of reports and the preparation/ submission can quickly become a time consuming task and some of this task do not add any values.
Fig 3a Journal Template
After carefully looking at the fields and format for any journal entries to be uploaded in PeopleSoft, I designed and created the journal template (Fig 3a Journal Template) for the Company to use.
The Blue colors are here for information or to provide information. The Finance user has to enter the journal entry and for the account and chartfield2 chosen a description will appear in the blue column. This was created to facilitate the reviewer check.
Once the journal is prepared the user click on a button to get a journal reference. This reference will create a common key between this journal and peoplesoft and make any audit easy.
A macro run in the background, and do the followings:
- do basic control to make sure that the journal comply with company policy,
- get a reference in a file saved in the network and saved some information in this file,
- create and save a csv or xls file to be uploaded in PeopleSoft,
This template was added in each excel used by each Finance staff in order to make the journal preparation more efficient and to cut down on time used for recurring journal entries (e.g. fixed assets depreciation, prepayment journals, etc.).
Fig 2a Travel & Expense flow
Fig 2b Travel & Expense template
High level process:
Employee have to complete the travel & expense template and to attach all the relevant documents. Once completed the employee will then convert this template and backup as pdf and create a single pdf document. This pdf document will then have to be reviewed and approved by his manager. Once approved the hard and soft copy will be sent to Finance for control/check. In the event all is done as per company policy this was then sent to the GM for approval. All the process was done on pdf with no need to ever print any document to allow any manager to review on the go.
Once approved all the expense approved were saved on a specific folder and automatically consolidated in one document. This was used for control, trend, payment to staff and report.
This template was designed for a specific project and could be tailored to any requirement.
Results
-
Multi currency model
-
5 years refinancing plan
-
Multi company across the UK, Asia, Latin America
-
No previous model designed
-
Multiple scenario case
Situation
-
Forecast prepared by UK at division level
-
Forecast prepared for subs at subs level
-
No consistent format submitted
Findings
-
Designed monthly financial model for a period of 5 years
-
Gathered all revenue assumption from UK division and subs
-
Created high level assumption page (revenue, expense, etc)
-
Forecasted profit and loss, balance sheet and cashflow
-
Modelised assumption impact on bank covenants
Role
-
Financing was given to the corporation
-
Monitored bank covenants on quarterly basis
-
Provided variance gap analysis between forecast and actual
-
Quarterly bank covenants report automated
Results
-
Recurring monthly report
-
Preparation time and submission: 2-3 hours
Situation
-
Email format same as previous month
-
Report are saved in specific folder/converted in pdf
-
A few Finance staffs are involved in the preparation
Findings
-
Automated the preparation of the reports
-
Reports are automatically renamed in specific folder
-
Reports are converted to pdf
-
Designed table to allocate report with recipient
-
Created macro to automatically sent all reports
Role
-
Preparation time and submission: 10-20 mins
-
Process managed by only 1 Finance staff
-
Finance staff time allocated to more added value task