C1: Excel for Acct : Chapter 3

While large firms often use purpose built tools like ___. ___ can be a substitute.

1. Oracle

2. Excel

A good Gl should

Integrate to other aspects of the fin. stmts for the company

GL links to other ___ and updates them when changed

Sheets

GL categories/sections

GL Code, invoice date, invoice #, requested by, check amt, payee, check use, method of distribution, and file date

Itemized expenses

All the expenses in a particular category

From a controllership POV, we make sure

People don’t add invalid codes

To validate and add an invalid code

Highlight empty cells in a desired column → Data tab → Data validation → add invalid code to “sources” to make it accepted → OK

T/F: you cant highlight cells and copy and paste formulas

false

Journal vs Ledger

A journal contains accounting information at the transactional level, while a ledger is more focused on account-level balances.

Chat of Accounts

List of all the accounts in a general ledger each identified by a reference number

Chart of Accts

List of all the accts in a general ledger- each identified by a reference number

Purpose of charts of accounts

to organize the finances of that entity by segregating assets vs. liabilities, revenue, and expenses

To give a better understanding of the financial health

A strong chart of accounts lets us go through and analyze

Information systematically to see where organization is efficient or not efficient

We categorize individual transaction w/

A separate code or string to let us identify what’s going on in the business

GL strings

Detailed codes used to categorize each transaction

GL strings contain_ parts totaling _ digits, covering which areas

1. 6

2. 24

3. Covering funds, general ledger acct, cost cover, project, function, object code

GL strings has _ separate digits

24

First part of the GL string refers to

The fund in questions (what internal accounts fund are we using?)

Second portion of the GL string is for

The general ledger acct( is it coming from the cash accts, AR accts, or the transfer acct?)

the third portion of the GL string is for

The cost center( which area of the business, the facility, the office, etc, is responsible for those costs?)

Fourth portion of the string is for

The project in question( if we have special program/project )

Fifth portion of the GL string is for

The function that the string is being used for ( is this a revenue or expense )

What is the sixth portion of the GL string is for

The object code/type of expenditure

T/F: we’d have a separate GL string for each/every transaction as part of our overall record keeping process

True

With Excel, we can use functions like SUMIF to analyze and aggregate

Costs, based on GL string categories, helping identify efficiencies and inefficiencies of the business

Excel is a powerful tool for preparing fin. Stmts because

It allows embedding formulas that dynamically update results when inputs change

Accountants often use massive raw data and put it into an

Intelligible and digestible format

Income stmt/profit and loss stmt outline

Sales

-Sales discounts

-Sales Returns and allowances

---

Net Sales

- COGS

---

Gross Profit

(Operating Expenses)

-Selling Exp.

-General & Admin Exp

- Dep./Amort. if significant

- Total Operating Expenses

---

Operating Income

+ Other income

+/- Gain/Losses

- Other expenses

(Interest revenue, gain on sale of building, interest expenses)

---

Income b/f taxes

- Income tax

---

Net profit/net losses

Main aspects of a Balance Sheet

Assets, liabilities, equity

Total assets =?

Short term assets + long term assets

Long term assets =

Property, plant, and equipment

Total liabilities =

Short term liabilities + long term liabilities

Capital lease obligations are a type of _

Long term liabilities

Retained earnings formula

Beg. RE

± net income/loss

- dividends

---

Ending RE

Retained earnings are calculated by

Linking prior retained earnings, net income, and cash dividends

Equity is based on:

Common stock holdings, paid in capital, retained earnings, unrealized gain/loss

Ratios used vary depending on

Industry

Debt to assets or the “debt ratio” is used to

Understand whether or not the firm has too much debt

Current assets/current liabilities is used to

Whether the firm is in a strong liquidity position

Debt/EBIDTA is used to understand

The level of debt the firm has compared to its profitability

ROA is used to undertstand whether

The firm is doing a good job achieving profitability

Debt to assets or “debt ratio” formula

Liabilities/assets

Current ratio formula

Current assets/current liabilities

debt to EBITDA ratio

Debt/EBITDA

ROA formula

Net income/total assets

ROA is typically reported to management in _

A Percentage

GAAP

• GAAP US accting system

• Rules based

• Less use of fair value accting

• More presciptive: specific industries and types of transactions have to be reported in particular ways

IFRS

• international accting system

• Principles based

• Greater use of fair value accting

• More flexible

XBRL

(Extensible Bus. Reporting Language)

T/F: XBRL is a new accounting standard

False

XBRL enhances the

Distribution and usability of existing fin. stmt information

XBRL is the enabler and extension for

Relational database functionality for all relational database functionality for all fin. stmt info.

XBRL is required for

Large firms that are publicly traded

Large firms have to file w/

The SEC through their EDGAR database

XBRL benefits

• Common interchange format and storage is good information management practice

• Reduces redundancies and discrepancies

• Platform is used for continuous reporting

• Standardizes fin. Info., making it easier to compare fin. stmts. across dif. firms and over time

XBRL is a _ between what investors see and what companies have

Intermediary

MD&A

Management discussion and analysis

T/F: we can use SEC EDGAR to find a company’s filings

True

Publicly traded companies are required to submit information to the _ and comply with _Additionally, they are required to submit it into_ format.

SEC and comply w/ EDGAR And submit it into XBRL format

How to calculate operating expenses?

Revenue - Expenses