Adamson & Venerable Chapter 9 Financial Reporting Spring 2012
Star Schemas in Financial Reporting • Managerial accounting vs. financial accounting • Balance sheets • Income statements • Cash Flow statements
Managerial vs. Financial Accounting • Managerial Accounting: It is accounting intended for internal consumption, used in planning and operating the organization by managers and employees. • Data may come from many sources, not just the approved, audited financial system. For example, sales force automation software may be used to forecast sales. These will not be the official expectations for external consumption, but only used internally to manage the sales force.
Managerial vs. Financial Accounting • Financial Accounting: It is accounting for external consumption, used in reporting financial results on a periodic basis to shareholders, creditors, and the government. • The source of data is a company’s approved, audited accounting system. Well-defined procedures are used to ensure data accuracy, prevent irregularities and thwart fraud. • Shareholders use information from the financial accounting system to monitor the performance of their investment. • Creditors use it to monitor the security of their debt.
Guidelines for a Financial Reporting DW To ensure the financial reporting DW is reliable and meets the high standards demanded of a financial system, we follow two strict guidelines: • The financial reports produced from a DW must be as reliable as reports from the existing financial reporting system. All changes and reversals in the financial system must be reflected in the financial DW. • The sources for the financial DW must be the approved, auditedsystems recognized as the official source for that data in the corporation. If not, the DW can still be a valuable managerial accounting asset, but should not be used for financial accounting purposes
Accounts • An Account is a defined entity of financial importance to the business. Transactions are recorded against accounts. Examples include: • A corporate checking account • A line of credit • Salaries • Vacation • Taxes • The Chart of Accounts is the current set of valid accounts in the business, against which transactions may be recorded. • Accounts can be balance sheet accounts or income statement accounts.
The Balance Sheet It is also called the statement of financial position. It is less accessible to most people than the income statement. It includes 3 categories of accounts: • Assets. Items that have inherent value. Short term (convertible securities) or long term (real estate). • Liabilities. Items for which the company must eventually use cash to reconcile. Unpaid bills for operations, current salaries, lines of credit, loans. • Owner’s equity. The capital invested in the company and retained earnings.
Balance Sheet Granularity • The general ledger of the accounting system records all transactions against an account • We can take each general ledger transaction for balance sheet accounts, apply reversals, and create our transaction fact tables • The general ledger transaction will be the grain of our fact tables
Balance Sheet Granularity The basic dimensions related to a General Ledger transaction are: • Time. Recording individual transactions requires a grain of day. • Category – credit or debit.Debit an asset to increase its value. Credit an asset to decrease its value. Debit a liability to decrease its value. Credit a liability to increase its value. • Account. Contains the type (asset, liability, equity) and other interesting characteristics.
Benefit of Star Schemas in Financial Reporting • These detailed facts are usually not presented in the official summary balance sheet, even though they exist in the financial source system • Detail reporting will allow users to drill into the usually bland balance sheet line items: • Accounts Receivable could be classified by customer, age, quality, etc. • Long-term liabilities can be presented by creditor and interest rates • Lines of Credit can be assigned to different operating divisions (which are not directly related to GL transactions) • These details are invaluable for internal analysis of financial results!
ASSET_FACTS LIABILITY_FACTS ASSET_PARTY LIABILITY_PARTY ACCOUNT_OWNER Acct_key Date_key Type_key Asset_party_key Account_owner_key Transaction_amount Date_key Type_key Acct_key Account_owner_key Liability_party_key Transaction_amount Account_owner_key Department Division Region Activity Manager Responsible_executive Asset_party_key Name Type (creditor/customer/steward) City State Liability_party_key Name Type (creditor/internal/govt.) State_province Country ASSET_ACCOUNT TRANSACTION_TYPE LIABILITY_ACCOUNT Acct_key Financial_system_id Name Liability_type Term (debt only) Interest_rate (debt only) Type_key Category (debit, credit) Type (depreciation charge, receivables payment, etc.) Acct_key Financial_system_id Name Asset_type Term (short/long) Fixed_flag (yes if fixed asset) Fixed_asset_category Balance sheet star with transaction facts EFFECTIVE_DATE
Analyzing Accounts Receivable (AR) over time • A Data Mart built only from AR records would provide details of the timeliness of payments • BUT, it would be useful to compare AR activity with asset value cycles. • Example: • If there is an increase in slow payments from customers, the value of AR will increase • As operating expenses continue to be paid, thebalance of cash accounts decreases • Eventually, a company would be forced to draw on credit sources to replenish cash supplies!
Increased Value of the AR Asset due to Slow Payments Decreased Value of Cash Assets to Pay Short-Term Expenses Increase in lines of Credit (Long Term Liabilities) to Supply Cash Relationship among accounts receivable, cash, and credit
Producing a Balance Sheet • With a trio of transaction fact tables, we can create a complementary set of summary tables that store the balance of all accounts associated with the balance sheet.
LIABILITY_FACTS EQUITY_FACTS ASSET_FACTS LIABILITY_PARTY ACCOUNT_OWNER ASSET_PARTY Period_key Type_key Liab_acct_key Account_owner_key Liability_party_key End_of_period_balance Asset_acct_key Period_key Type_key Asset_party_key Account_owner_key End_of_period_balance Equity_acct_key Period_key Type_key Account_owner_key End_of_period_balance Asset_party_key Name Type (creditor/customer/steward) City State Liability_party_key Name Type (creditor/internal/govt.) State_province Country Account_owner_key Department Division Region Activity Manager Responsible_executive EQUITY_ACCOUNT ASSET_ACCOUNT LIABILITY_ACCOUNT PERIOD Equity_account_key Financial_system_id Name Type Period_key Period_type Period Period_year Liab_acct_key Financial_system_id Name Liability_type Term (debt only) Interest_rate (debt only) Asset_acct_key Financial_system_id Name Asset_type Term (short/long) Fixed_flag (yes if fixed asset) Fixed_asset_category Balance sheet star with period summaries
The Income Statement • The Income Statement is a summary of revenue, expenses, and income for a given period of time. • The income statement is a summary of activity, not a snapshot, therefore measures that make up the line items of an IS are additive • Revenue comes from operations or from non-operational investment activity • In manufacturing, the cost of materials, labor, plant depreciation, etc. is called Cost of Goods Sold • Gross Income = Revenue – Cost • Earnings Before Interest and Taxes (EBIT) = Gross Income – Expenses • Net Income = EBIT – (Taxes + Interest)
EXPENSE_FACT REVENUE_FACT REVENUE_SOURCE ACCOUNT_OWNER PAYEE_OR_PARTY Rev_acct_key Rev_type_key Date_key Rev_srce_key Account_owner_key Transaction_amount Date_key Exp_acct_key Exp_type_key Account_owner_key Payee_or_party_key Transaction_amount Account_owner_key Department Division Region Activity Manager Responsible_executive Payee_or_party_key Name Type (vendor/asset/govt.) Parent_org State_province Country Rev_srce_key Name Parent_organization Type (customer/investment) Industry State_province Country REVENUE_ACCOUNT EXPENSE_ACCOUNT REVENUE_TYPE EXPENSE_TYPE TIME Exp_type_key Subtype Type Category (Cost of goods sold, store overhead, SAG) Rev_acct_key Financial_system_id Name Rev_type_key Subtype Type Category Date_key Month Year Exp_acct_key Financial_system_id Name Cash_flag Income Statement Star schema
Cash Flow Statement • Cash flow from operations • Cash flow from investments • Cash flow from financing