C1: Excel for Acct. Chapter 1

Accting

measuring or analyzing what’s going on

Traditional accounting can be divided into two categories

1. Audit

2. Tax

Small firms focus on

payroll and business taxes

Small firms use what type of acctants

outside accountants and bookkeepers

Small firms traditionally use

Quickbooks (also may use Excel as an alternative)

Larger firms focus on what type of acctants

in house bookkeepers and acctants

larger firms track transactions using

the general ledger

Larger firms may use __ to help with track transactions. But a versatile, generic alternative can be _.

1. Purpose - built software packages ( ex: Oracle )

2. Excel

Bus. Valuation relies on

fin. stmts

acctants come up with bus. valuation based on

accting stmts that they have prepared/evaluated (this can be done in Excel)

Two major formulas

=IF

=VLOOKUP

= IF

Tests whether a condition is true or not and returns different results accordingly

= IF on its own only gives _ possible answers

two possible answers

Three major parts of = IF

1. condition to test

2. what to do if it’s true

3. what to do if it’s false

= IF formula

=IF(logical_test, [value_if_true],[value_if_false]

logical test = what we’re testing

value if true = yes value

value if false = no value

Nested IF

-when you put one nested if inside another

-uses more than two possible answers\

-used to test for multiple conditions

ex) =IF(H2=O, “Not nominated”, IF(12=0, “Loser, “Winner”))

ex2)=IF(A6=2017,IF(C6=”B”, B6, 0),0

VLOOKUP

looks up a value in the first column of a range and returns a value from another column in the same row

VLOOKUP is good for

complicated issues (ex: tax rates)

Three parts to VLOOKUP

1. the value you’re looking up

2. the table you’re looking it up from

3. the column number to return

VLOOKUP formula

(lookup value, table range, column index #, range_lookup)

Summarize/Explain: VLOOKUP($B$7, $E$5:$H$32, 2,FALSE)

The formula looks for the value in B7 in the first column of the range E5:H32. If it finds a match, it will return the value from the 2nd column of the range (column F). If no exact match is found, the formula returns an error

Tasks in Excel include

-Budgeting & fin. stmts

-Spreadsheets

-Using External data

-Integrating data across multiple different areas of the company

With cash flow budgeting you need to be

generating positive cash flow over time

What is found in a cash flow projection?

• cash receipts (inflow)

• cash paid out (outflow)

• cash on hand (end of month)

T/F: you can find the SUM among values by highlighting them in Excel

T

T/F: You can use the black plus sign and highlight empty cells to create further projections

T

Profit & Loss Stmt aka

• income stmt

• P&L stmt

P&L 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

Break down this Net Sales If function by identifying the condition, yes value, and no value: =IF(SUM(C4:C9)=0, “ “, SUM, (C4:C9))

If yes → “ “

If no → SUM(C4:C9)

How to format cells

CTRL + 1

→ can change to “Currency”, “General”, “Number”, “Percentage”, “Text”, etc

What is NOT a common task for acctants?

a. Fin. models

b. doing payroll tasks

c. performing valuation analysis

d. making general ledger entries

Creating financial models: more likely to be a task for corporate finance people