Excel

Hands-on Process Driven Individual contributor experienced in automating accounting, financial reporting processes by leveraging on Excel VBA, SQL, PowerQuery, Array Formulas etc

This section is on Excel Spreadsheet & its applications for the respective accounting tasks. Have learned and applied a lot of the techniques picked up from websites hosted by MVPs like:

Mike Alexander: One of the first few to start hosting free online excel video lessons. First mastered INDEX MATCH lookup from his DataPigTechnologies site

Ron de Bruin: One of the most comprehensive coverage on Linking Outlook & Excel. Is addicted to his RDBmerge Add-in which I have been using for years.

Debra Dalgleish: Contextures website has extremely wide coverage on many areas. Very well illustrated with lots of sample files.

WiseOwl Tutorials:  Solid & Structured Videos on VBA. The coverage is very extensive

Alex C YouTube Playlist:  Pretty neat coverage on web scraping & Controlling of Windows API through Excel VBA

The aim of automation is to have more time for exploring other aspects of life

Work to live , Not live to work

Money as a means to an end, Not an end in itself

Accounts Payable

• Expense Claim Template  X 

• TimeSheet Template  X 

• Mini Payroll: Sending Payslips (Password Zipped) through Excel  X   O   7z

• Mass email payment advises to respective payees  X   O 

• DIY Aging Report in Excel Spreadsheet template  X 

Accounts Receivable

• AR :Mass Email SOA through Excel  X   O 

• AR :Matching Unknown Payment to Invoices using excel solver  X 

  • AR :Mass Email Invoices (PDF) for Back-to-Back Drop Shipment Dual Billings (Retail, Healthcare)   X   O 

• AR :Mass Email Invoices (PDF) for monthly recharging of expenses (Engineering Co)   X   O 

• AR : Billing of Manpower / Labour ~ Day Rates   X 

  • FIFO Excel template: Automate Flow from inventory to Cost of Goods Sold based on First in First Out  X ~ updated 10/02/18, 13/04/19

Consolidation

• 3D referencing, Use SUMPRODUCT to "SUMIF" multiple columns  X 

  • Merge and Combine Worksheets from Multiple Workbooks  X 

  • Merge and Combine common fields selected from Multiple Workbooks  X 

• Merge and Group Data files into respective sheets based on filenames  X  

• Import all excel files in folder to table in Microsoft Access (FMCG , Retail)    X   A 

• Import files (csv, text and spreadsheet xls) into Microsoft Access (FMCG , Retail)   X   A 

• Batch Rename Files based on Specified Cell Value in respective spreadsheets  X 

• Retrieve Data from Multiple Closed Workbooks  X 

Reconciliation

• Reconciliation: FIND & MATCH function  X 

• Reconciliation by Pivot Template  X 

• Fixed Asset (FA) Template ~Straight Line   X 

  • Fixed Asset (KiOnce) Template to handle Straight Line, Double Declining & Sum of Year Digits Method  X ~ updated 08/02/18

  • Excel Custom Functions: DEPRE & ACCDEPRE for depreciation calculation  X 

Reporting

• Waterfall Charts for Variance Analysis  X  ~ updated 22/01/18

• Data Conditioning: Structuring Data for Pivot Table  X 

• Speedup Slow workbooks: PasteDown As Value  X 

• Using EVA for lookup on CPF rates  X 

• DIY Excel Template: Headcount Summary for Human Resource (HR)  X 

• Array Formula: Tabulating Current Balances for a list of Mortgage Loans (Real Estate)  X 

Miscellaneous

• Spelling Numbers-Convert figures to Text through Lookups  X 

• Mail2Who Excel Template: Include the correct point of contacts in Emails ~ by using lookups  X   O  ~ updated 26/01/18

• Accounting 404

• AutoSum TRick for filling up multiple Subtotals  X 

• Excel Template: Split data into multiple worksheets based on values in selected column  X  ~ updated 12/03/19

• Excel VBA: convert number format from European to US  X 

• Documentation ( PowerPoint 2016's Screen Recording Feature )  P 

System as a way of controlling population

Is the current system 's agenda more for the purpose of population control? 

For those who (the British Royalty etc) subscribe to Malthusian religion of over population, I believe Henry George 's Book has provided the necessary counter argument against this and I quote:

"Once the demand for quantity is satisfied, we seek quality. As human power to gratify our wants increases, our aspirations grow. At the lower levels of desire, we seek merely to satisfy our senses. Moving to higher forms of desire, humans awaken to other things. We brave the desert and the polar sea, but not for food; we want to know how the earth was formed and how life arose. We toil to satisfy a hunger no animal has felt, a thirst no beast can know.

Given more food and better conditions, animals and vegetables can only multiply — but humans will develop. In the one case, the expansive force can only extend in greater numbers. In the other, it will tend to extend existence into higher forms and wider powers.

None of this supports Malthus' theory. Facts do not uphold it, and analogy does not support it. It is a pure figment of the imagination, like the preconceptions that kept people from recognizing that the earth was round and moved around the sun.

This theory of population is as unfounded as if we made an assumption about the growth of a baby from the rate of its early months. Say it weighed ten pounds at birth and twenty pounds at eight months. From this, we might calculate a result quite as striking as that of Mr. Malthus. By this logic it would be the size of an elephant at twelve, and at thirty would weigh over a billion tons."

Quoted from Bertrand Russell's Proposed Roads to Freedom:

Malthus contended, in effect, that population always tends to increase up to the limit of subsistence, that the production of food becomes more expensive as its amount is increased, and that therefore, apart from short exceptional periods when new discoveries produce temporary alleviation, the bulk of mankind must always be at the lowest level consistent with survival and reproduction. As applied to the civilised races of the world, this doctrine is becoming untrue through the rapid decline in the birthrate; but, apart from this decline, there are many other reasons why the doctrine cannot be accepted, at any rate as regards the near future.

Quoted from Alan Watts:

The fear that adequate production and affluence will take away all restraint on the growth of population is simply against the facts, for overpopulation is a symptom of poverty, not wealth. Japan, thus far the one fully industrialized nation of Asia, is also the one Asian country with an effective program of population control. The birth rate is also falling in Sweden, West Germany, Switzerland, and the United States. On the other hand, the poorer nations of Asia and Africa resent and resist the advice that their populations be pruned, in the feeling that this is just another of the white man’s tricks for cutting down their political power. Thus, the one absolutely urgent and humane method of population control is to do everything possible to increase the world’s food supply, and to divert to this end the wealth and energy now being squandered on military technology.