Accounting for Database Developers The Franklin Savings Bank

Accounting for Database Developers

The Franklin Savings Bank, SE corner 42 nd St and 8 th Ave, circa 1901

Speaker Introduction

TALLAN

Project-Based Application Development Experience Founded in 1985 29+ years of building long-term relationships Delivered 2910 Projects to Over 589 Clients Consultants with a wide range of experience and expertise Strategy, Architecture, Integration, Design, User Experience, Project Management, Development Microsoft National Solution Provider, Gold Partner, MVP Authors, 2014 SQL/BI Partner of the Year (NYM) National Presence: Hartford • Boston • Santa Ana • Tampa • New York • Washington D. C.

29 Years of Long-Term Partnerships COMPANY DURATION Talbots 12 years ING United States Financial Services 8 years Disney 7 years Ingram Micro 7 years Columbia House 7 years Oakleaf / Waste Management 7 years Evolution 1 7 years Hudson Health Plan 5 years UNFI 4 years

Tallan Practices Understanding best practices & optimizing time to market: Application Integration - Biz. Talk Portals & Collaboration - Sharepoint Business Intelligence Mobile – i. OS, Android, Windows User Experience (UX) Cloud - Azure

Business Intelligence Tallan has been delivering Business Intelligence and data solutions for over a decade. Tallan BI Solutions can help you: Cleanse & Integrate Classify & Interpret …and ultimately reveal information and trends that your business can ACT ON.

Business Intelligence Capabilities Dimensional data modeling ETL development OLAP cube development Planning and forecasting analysis Report development using: Reporting Services Excel and Power Pivot Third-party tools such as Panorama and Strategy Companion Data warehouse architecture Metadata cataloging Data mining model development MDX and SQL queries Dashboard development using: Share. Point (including Power. XX) Custom-built WPF and Silverlight interfaces Third-party tools such as Dundas

Session Non-Goal – Tech-Heavy Demos

Session Goals Explain why this topic and why you should care. Explain the difference between Accounting and Bookkeeping Review terms and concepts developers are most likely to encounter Highlight typical implementation details Show Analysis Services directly supports some bookkeeping concepts

Why this topic - why should you care ?

Accounting, or accountancy, is the measurement, processing and communication of financial information about economic entities (Wikipedia) Economic Entity: any entity in society that can engage in financial activities. Individuals, businesses, government at all levels, non-profits. We’ll focus on businesses. Accounting is what decides how to characterize and record financial transactions – and indeed whether a transaction has financial impact. Accounting has standards (GAAP) but also much discretion and gray areas. Accountancy makes several assumptions about businesses, three of which are: A business and its owners (shareholders) are distinct economic entities The business is a “going concern” – i. e. is viable indefinitely. It only deals with activities and facts that can be measured in $ terms.

Why is Accounting done ? To provide 3 rd parties with information needed to make decisions with respect to the business. 3 rd parties may be internal or external. Internal 3 rd parties include Finance and HR. External 3 rd parties are actual and potential investors and creditors, auditors, regulators, the IRS and other tax authorities, customers… Accounting plus bookkeeping culminates with the periodic preparation of financial records: The Balance Sheet The Income Statement The Statement of Cash Flows Beware: Just because something is reduced to a number does not give it automatic credibility Even the best financial reporting about an entity does not reveal all there is of significance

Bookkeeping Accounting and bookkeeping are related but not the same. Accounting dictates and bookkeeping records. Bookkeeping is rote, mechanistic, relatively lacking in discretion. This is why it was one of the first business functions to be computerized. Most important for us, developers typically interface to bookkeeping systems, so the rest of the session will be about bookkeeping concepts and terminology.

Let’s Start at the Top and Work Down Key terms like debit, credit, account and ledger need a context to be meaningful There is no way to avoid a digression back to accounting The starting point is the properties of an economic entity that can be measured in $ terms: Ø Assets – things owned by or owed to the entity. Ex: Cash on hand, bank deposits, Land, Buildings, Vehicles, Equipment, Inventory, Accounts Receivable, Prepaid Expenses. NOTE: though perhaps its most valuable resource, employees are not Assets (though some managements consider them liabilities ) Ø Liabilities – things owed to another economic entity by the entity. Ex: Mortgage, Accounts Payable, Accrued Taxes, Salaries, Utilities Ø Equity – what belongs to the entity’s owner(s), i. e. Assets – Liabilities. Also known as Shareholders Equity, Owners Equity, Capital, Book Value or Liquidation Value – what would be left after all obligations are paid off. Ø If Equity is negative, the entity is or soon will be insolvent, aka bankrupt, if something doesn’t change.

The “Accounting Equation” ASSETS = LIABILITIES + EQUITY (Eq) As accounting/bookkeeping keeps track of financial activity, this equation must always balance Eq is an obvious “fudge factor” that can always be adjusted to maintain balance What counts as assets and liabilities, and how they are measured or valued, is the province of Accounting. These can be fudged too, e. g. to keep Eq from going negative. The Accounting Equation applies at a point-in-time. Typically Monthly, Quarterly and Annually. The same is true of the derivative financial statements. Assets are recorded at cost, not present market value (monetary assets are exceptions). This is both because determining market value of every asset every time financial statements are prepared would be difficult and subjective, and also because the entity does not need to know the market value of most assets as it is using them in its business rather than looking to sell them.

Accounts Track activity impacting a financial entity via entries Have names, and usually numbers Have types based on the Accounting Equation categories – Asset, Liability, Eq, and derived categories In everyday life checking, savings and retirement accounts are among your Asset accounts and are quite explicit. In everyday life liability and Eq tend not to be explicitly maintained as accounts. You tend to materialize them only when you need to calculate your Eq – aka your net worth.

Debits and Credits In everyday life accounts there is a single running total or balance – entries are added and subtracted We think of adding as a credit – a deposit; subtracting as a debit – a withdrawal. From this we tend to think of credits having positive sign and debits negative sign. This may be the source of the positive and negative connotations of these words we have. You have to give this up to understand bookkeeping, because debits and credits aren’t interpreted like this in bookkeeping. In bookkeeping all entries against an account are positive The notion of increase and decrease is instead expressed through debits (DR) and credits (CR) BUT, what constitutes an increase or decrease varies – depends on the type of account. And as we’ll see, this determines how debits and credits are used on a per account-type basis.

The T-account In bookkeeping, debit and credit refer to nothing more and nothing less than the left and right sides of a T-account. Regardless of account type, debit entries always go on the left and credit entries on the right. Account type determines which side gets hit to indicate an increase or a decrease. The sign of entries is always positive. Assets, Liabilities and Equity each break down into multiple T-accounts.

Accounting Equation as T-accounts The sum of debits and credits across all accounts must be equal. The amount of Net Income not paid out in dividends is called Retained Earnings. The above is a high-level view of “the chart of accounts”.

The General Ledger, aka the G/L, is the set of all Accounts set up to track entity financial activity. It is the physical realization of the Chart of Accounts (COA). Today the G/L is mostly virtualized, but originally was literally a paper ledger, or book. “Cooking the books” derives from this. In actual practice three elements of the Accounting Equation – Assets, Liabilities and Equity – are sub-divided into multiple detailed Accounts. This immediately suggests the idea of hierarchies and aggregation. This is what Analysis Services helps with. The next slide shows a representative Balance Sheet, which reflects the G/L and COA. It’s called a Balance sheet because it reflects the balance of the accounting equation.

A Balance Sheet http: //www. myaccountingcourse. com/financial-statements/balance-sheet

Double-entry Bookkeeping Each financial transaction is represented by a debit of at least one account and a credit of at least one other account More than one account on either side may be debited or credited. Quiz: does the above imply that one side always increases and the other decreases ? No. Increase/Decrease depends on account type as well as whether a debit or credit. Double-entry is a methodology for checking that the bookkeeping has been done correctly. Since debits and credits must equal in the aggregate (across the balance sheet) – if they do not, there is an error somewhere. Not all errors can be caught this way – e. g. if a transaction is not recorded at all, or twice.

Accounting Period and More on Accounts Accounting/bookkeeping is always with respect to time, as is the derived financial reporting. The BS is as-of a point in time – typically quarter-end and year-end. This is because the balance of many accounts will vary over time. The BS derives from BS accounts. The Income Statement is for a defined period – typically monthly, quarterly and annually. It derives from IE accounts. “Period” implies start/end dates and thus “opening” and “closing” balances. At the start of an accounting period, BS accounts have an opening balance equal to their prior period closing balance. At the end of an accounting period BS accounts have a closing balance. BS accounts are called “permanent” accounts. At the start of an accounting period, IE accounts have an opening balance of zero. At the end of an accounting period IE accounts have a closing balance. IE accounts are called “temporary” or “nominal” accounts. Nominal accounts have their balances transferred (debited or credited) to permanent accounts at the end of an accounting period, and are then reset to zero for the next accounting period.

The Accounting Cycle

Accounting Cycle Steps 1 -3 Steps condensed from http: //www. accountingverse. com/accounting-basics/accounting-cycle. html 1. Analysis of Business Transactions Identify and analyze business transactions and events. Determine accounts affected and amounts to be recorded. Not all transactions are entered into the accounting system, even if financial in nature – this is the principle of “materiality”. 2. Make Journal Entries For our purposes a “system of record” that feeds the G/L. Transactions are recorded using double-entry bookkeeping, in chronological order as they occur. Special journals are used for transactions that recur frequently such as sales, purchases, cash receipts, and cash disbursements. A general journal is used to record those that cannot be entered in the special books. 3. Posting to the General Ledger A collection of accounts that shows the changes made to each account as a result of past transactions, and their current balances. The detailed journals are aggregated (by debits and credits) to create the entries to be posted to the G/L.

Accounting Cycle Steps 4 -6 4. Prepare Trial Balance Intended to test the equality of the debits and credits. All debit balances and credit balances are (separately) added. Total debits should be equal to total credits. If they are not, correcting entries are made to rectify them or reverse their effect. The trial balance is only to test the equality of total debits and total credits and not to determine the correctness of accounting records. 5. Make Adjusting Entries (we will skip) 6. Adjusted Trial Balance An adjusted trial balance may be prepared after adjusting entries are made and before the financial statements are prepared. This is to test if the debits are equal to credits after adjusting entries are made.

Accounting Cycle Steps 7 -9 7. Prepare Financial Statements When debits equal credits in the G/L, the financial statements, the end-products of an accounting system, can be prepared. These include the Balance Sheet, Income Statement and Statement of Cash Flows. 8. Close Accounts Nominal accounts are closed. “Closed” means they are transferred to relevant “permanent” accounts, including Equity accounts, and then set to zero. Permanent accounts are never “closed”. 9. Post-Closing Trial Balance (we will skip)

IT-oriented Observations 1 • Account Numbers are usually “intelligent” keys which encode Account properties we’ve reviewed (and ones we haven’t). For example: 45021234, where: 45 = the subsidiary 02 = account type, e. g. Liability 1234 = specific account number of account – e. g. 0001 = Accts Payable, 0002 = Accrued Taxes, etc • These intelligent account numbers which all G/Ls use, can be a real problem in mergers/acquisitions. They’re a perfect example of the desirability of using surrogate rather than business keys in a data warehouse. • It is very common for account type to be encoded as 01 = Asset, 02 = Liability, 03 = Equity, 04 = Income and 05 = Expense. Adventure. Works. DW (kind of) does this.

IT-oriented Observations 2 • The typical DW/BI application is concerned with profitability. • In bookkeeping terms, Profit = Income – Expense. Aka Net Income. • Frequently expenses will either be stored X -1, or an expression in the consuming query will do this, so that income and expense can just be added. • Sometimes you will see logic that multiplies by -1 based on account type and whether a debit or credit balance. This is impossible to understand without understanding the basics we’ve reviewed.

IT-oriented Observations 3 • In the olden days, the G/L was the only point of integration among the operational applications of the business. The financial accounting was the only commonality. • This often led to opening accounts in the G/L whose sole purpose was specialized reporting. This made a mess of the chart of accounts. • The maturation of data warehousing has fortunately replaced this.

Accounting Perspective • The accounting depends on POV of the entity being accounted for. • From your perspective, your balance in a checking account is an asset. • From your bank’s perspective, your balance is a liability – that’s why it’s called a “demand deposit”… • You (and your accountant) consider your mortgage with your bank to be a liability. • But your bank (and its accountants) considers your mortgage an asset.

More About Equity • Equity is residual – what is left after assets satisfy liabilities. • Equity is capital acquired from non-liability sources. A loan is not equity, but proceeds of selling stock are. • Only the proceeds of the original sale of stock count as equity. Trading of those shares on the secondary market is irrelevant to equity (except when the entity buys back stock). The Retained Earnings component of Equity reflects operations since the entity began ! • • Retained Earnings consists only of capital contributed by the shareholders, and profit from profitable operations.

MS & Amazon Shareholder Equity Microsoft Amazon Microsoft

Google and Apple Shareholder Equity Google Apple

Wait ! There’s More… There are many more terms and concepts we don’t have time for. Just a few are: • Accruals and Depreciation • Cash vs Accrual accounting • The “Matching Principle” • Consolidation • Allocation • Contra Accounts • Intra-company Netting • Budgeting

Let’s take a look at Analysis Services

To go Further Search for any of the terms mentioned today to find a wealth of information Get a good book on bookkeeping (mine are so old they are out of print) Grab any opportunity to work on a project that involves interfacing to the accounting system-of-record or any of its feeding applications

QUESTIONS

Slide Show Tips To present in true widescreen, you’ll need a computer and, optionally, a projector or flat panel that can output widescreen resolutions. Common computer widescreen resolutions are 1280 x 800 and 1440 x 900. (These are 16: 10 aspect ratio, but will work well with 16: 9 projectors and screens. ) Standard high definition televisions resolutions are 1280 x 720 and 1920 x 1080.