Categories Pricing Corporate
Professional eBook

Excel Solutions for Accountants: Book 4

156
Language:  English
Four books with the title of Excel Solutions for Accountants: dozens and dozens of solutions. You get experience of the hundreds of cases that colleagues of yours have benefitted from over the years.
Professional Plus subscription free for the first 30 days, then $8.99/mo
Kirja ei sisällä mainoksia
Description
Content

This is the third in a series of four books with the title of Excel Solutions for Accountants: written after a career in the world of accounting, education and training: in which we have seen first hand what accountants know, what they think they know and what they don’t know.

All accountants have gaps in their knowledge and skills: this books fills them.

PPP: paper, pencil, plan. With complicated tasks, start with a pencil and paper: the more complicated the more you need PPP. You will see many examples throughout these pages in which accountants ignored PPP.

These books don’t contain the most glamorous or the most exotic of graphs, formulas and functions! What we have done is to take you step by step through hundreds of cases and problems that colleagues of yours have benefitted from over the years. What you are about to face come from our training and consultancy work: from one of the largest companies in the world to the individual cost accountant who was keen to learn more and more. Excel Files available here: https://drive.google.com/file/d/1VWq3tAjz2RKUspPND-hh-8GlUI383DH3/view

About the author

Duncan Williamson is addicted to lifelong learning and uses the opportunity of writing books for Bookboon as  an excuse to study new ideas and materials in his chosen areas of interest. Duncan holds an  MBA from the University of Newcastle Upon Tyne, a Certificate of Education from Sunderland Polytechnic and an HND in Business Studies from Teesside Polytechnic. In addition, Duncan has successfully completed the Diploma of the Chartered Institute of Marketing and recently received a Certificate in Data Analysis from the University of Texas at Austin.

Duncan has studied, worked with and taught many aspects of management accounting, financial accounting and bookkeeping, financial reporting under IFRS, corporate finance, financial analysis, financial and spreadsheet modelling. He has worked on the financial and economic appraisal of projects in several countries over many years. He has published books and articles, case studies and so on. Over the last four years or so, Duncan has written four books for Bookboon in his series, Excel Solutions for Accountants, as well as two books on Finance for the Non Financial Manager and a book on Report Writing for  Finance Professionals. He also wrote the book The Accounting and Bookkeeping Coach, published in April 2014 by Hodder and Stoughton, Prentice Hall published his Cost and Management Accounting in 1995 and he has self published a number of books including his 2012 major spreadsheet book The Excel Project … available on amazon.co.uk. Duncan has also written and published a wide number of books aimed at pre University and first year undergraduate level students.

Duncan has always loved travelling and over the last three decades or so he has lived and worked (from as little as a week to as long as five years) in such countries as Malawi, Bosnia and Herzegovina, Kyrgyzstan, Kazakhstan, Uzbekistan, Romania, United Kingdom, Denmark, Afghanistan, Dubai, Abu Dhabi, Kuwait, Oman, Saudi Arabia, Switzerland, Hungary, Thailand, Singapore, Vietnam, Malaysia, Bahrain, Pakistan, Holland, Sudan, Albania, Qatar, Mexico, Angola, Ghana, Tanzania, Angola and Mozambique.

In terms of hobbies and interests, Duncan clearly loves travelling: in addition to the countries he has worked in, he has visited another 35 – 40 countries. Cultural and ethnic differences and similarities are a fascination for him too. Duncan loves taking photographs and reckons that for every hundred of his serious attempts at photography, one might be good! Duncan loves writing and creating training and learning materials; both for his work and as part of his lifelong learning crusade: keeping his brain cells fully engaged. More than that, you will find Duncan on discussion lists, in various online forums in which he fully engages with others in putting the world to rights and to helping people solve their Excel, modelling and accounting problems.

Another travel connection comes from Duncan's modelling and spreadsheeting skills with which he has recently started to model data from commercial aircraft: altitude, speed, latitudes and longitudes; mapping the history of a particular aircraft over a one year period, that can involve as many as 1,000, 1,500 and even more flights. This aspect of his interests involves using Excel and Power BI.

Even though he lives in Thailand now, he still follows the English Premier League and his favourite club Burnley FC.

  • Introduction
  1. Accountant Specific 4
    1. TEXTJOIN
    2. Fill Justify
    3. Tax computation
    4. Complex Sorting
    5. Sorting from Left to Right
    6. An Accounting Example: sorting left to right
  2. Graphs 3
    1. Five Ways to Create a Histogram
    2. Icons in Sparklines
    3. Sunburst Graph
    4. SERIES() Function in Charts
  3. Compound Interest 2
    1. Introduction
    2. Loan Schedules and Calculations
    3. Amortisation of a debt
    4. Refinancing a debt
    5. Sinking funds
    6. Car Loan Example One: finding the rate of interest used
    7. Car Loan: Extensions … Data Tables
  4. Date & Time Functions
    1. Subtracting One Time from Another: negative time
    2. Leap year effect
    3. Variables/Range Names used
    4. NETWORKDAYS()
    5. Multiple Functions
  5. Forecast 2
    1. Introduction
    2. Forecast functions
    3. FORECAST Syntaxes
    4. Using the Forecast Sheet Utility
  6. Functions
    1. Multiple Scenario
    2. VLOOKUP() with array constants
    3. Calculating Miles Flown Using Latitude and Longitude
    4. The N Functions
    5. How to Create an Ordinary Comment in Excel: whoops, they changed!
    6. Finding Range Names
    7. ROW()/COLUMN() Functions
    8. The INDEX() Function
    9. Array form
  7. New Functions
    1. Data Types … Stocks and Geography
    2. Data Types Stocks
    3. Data Types Geography
    4. The SWITCH() Function
  8. Pivot Tables 4
    1. GETPIVOTDATA() Function
    2. Greater Complexity: multi level GETPIVOTDATA()
    3. Set up a Range to Help GETPIVOTDATA
    4. GETPIVOTDATA()+GETPIVOTDATA()
    5. 3D GETPIVOTDATA Models
    6. Conclusions GETPIVOTDATA
    7. PostScript: turn it off!
  9. Power Query and Power Pivot
    1. Flash Fill & Power Query
    2. Flash Fill
    3. Unpivot Data
    4. Power Query: Append
    5. Power Query: Merge
    6. Power Query: Export Connection File
    7. Power Pivot
  10. Statistics: explanatory models
    1. Second Hand Car Prices Bangkok
    2. Library Fine
    3. Linear and Non Linear Equations
    4. Benford’s Law Again: an example where we KNOW they were lying!
About the Author
Duncan

Duncan Williamson