Review for Midterm Flashcards
What does sumproduct use?
Arrays, choose a single column at a time
How to do questions that are like Add if >4
SUMIF Function
Two ways to do roots
^(1/2)
sqrt
What do countif and sumif add to regular functions?
Criteria
If you want to fill a series
Drag outside the selection!
Formula vs Function
Formula is +-/*
Function SUM PRODUCT
Cash Helper Formula
(total liabilities & Equities)-(total non current assets+ sum(current assets-cash))
Credit Card Formula WHAT TO REMEMBER!!!
Credit Card charges Revenuecustomers using credit card
Gst Paid
Sum(GST Eligible expenses)*GST
Gst payable
Collected GST-Paid GST
GST collected
Revenue*GST
REF
reference is invalid
NULL
WEIRD SPACE
N/A
No reference available
VALUE
wrong argument
####
Width is too small/wrong date or time
DIV/0
Division by zero or blank cell
NAME?
Formula not recognized
NUM
Non numeric data
If given data where a % is used for business questions!! BE CAREFUL BETCH!!!!!!!!!! MULTIPLICATION!!!!!!!!!!!!
PLEASE BE CAREFUL!!!!!!!!!!1 YOU MUST MULTIPLY IT EVERYWHERE!!!!!!!!!!!111
Prod cost formula
Fixed Cost formula
cost* quantity
cost*occurences (monthly or annual)
Accumulated CCA
How to get the actual non current assets value?
Both the CCA from Year One and Year two
subtract accumulated cca from non current assets!!!!!
Is retained earnings accumulated??
Yes! Add net income previous year+this year
For interest questions:
IF NEGATIVE?
Where do you get this value?
Put a MINUS sign to make negative positive
LOAN WORKSHEET USUALLY look at THE INTEREST PAYMENT COLUMN
RCGONeTN
Revenue-Cogs= Gross profit
Gross profit- operating expenses (fixed costs)= Net income before taxes [EBITDA]
EBITDA- sum(interest depreciation ammortization)= ebt
Net income before taxes-Tax= Net Income
EBITDA formula
Gross Profit-Operating Expenses
EBT Formula
EBITDA-(intersest+depreciaiton+ammortization)
Percent of Revenue
cell w value your determining/Revenue
how to use percentage of revenue
Example:
COGS Y1Percent of revenue
CREDIT CARD CHARGESPercent of revenue
Year to Year growth
Current/Old-1
NO BRACKETS
What is Net Sales?
Revenue
Accounting Formula
Assets=Liabilities+Equities
what is Implied volume
-calculated sales based on this?
-Actual Sales?
-is there rounding?
This is pro-forma like inferencing the future volume so its like a guess (previous years volume * expected growth rate!!!)
-IMPLIED VOLUME*PRICE
-Must use an if statement bc youre guessing and dont wanna be inaccurate SOOOO
=IF(implied volume<Max Volume, implied volume *Price, Max Volume *Price)
-Yes roundodwn! because if youre doing customers you want whole customers and be conservative
Retained earnings?
Net income (Profit) of current year + Retained earnings from previous year
Shipping formula
SHIPPING*VOLUME
5 RATIOS5
Profit Margin
Current Ratio
Debt Ratio
Debt to Equity Ratio
Asset Turnover
profit
revenue-costs
how to calculate NON CURRRENT ASSETS WHEN GIVEN ACCUUALTED CCA!!!!!
EQUIPMENT- ACCUMULATED CCA!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!111
“payable”
owing
current assets
assets that will be liquid in one year
non-current assets
things that will be not liquid for a while EQUIPMENT!
VLOOKUP FORMULA
=(cell w info, chart ABS REF, column in range you wana get)
HLOOKUP
=(cell w info, chart ABS REF, row in range you wana get)
HLOOKUP
=(cell w info, chart ABS REF, row in range you wana get)
What is Goalseek
Trial and error speedrun
Goalseek sections
set cel: which cell has the val you wanna change!!!
to value: integer that you want set cell to be
by changing cell: which cell is gonna change for this to happen
Profit Margin Ratio
Net Income/Sales
PROFITABILITY
Current Ratio
Current Assets/Current Liabilities
LIQUIDITY
Quick Ratio
Current Assets-Inventory/Current Liabilities
LIQUIDITY
Debt Ratio
Total DEBT/Total Assets
LEVERAGE OR DEBT RATIOS
Debt to Equity Ratio
Total Debts/Equity
Leerage or Debt Ratios
Asset TURNOVER
Net Sales/Total Assets
REVENUE/TOTAL ASSETS
Activity/Efficiency Ratio
4 types of ratios
profitability- profit margin
Liquidity- current ratio/ quick ratio
leverage or debt ratios- debt/debt to equity
Activity/Efficieny- Asset Turnover
do you roundup or down in implied volume
DOWN bc you want full customers
Breakeven sales?
Price*Breakeven points volume
What formula always used in breakeven
Rounddown
Year to Year growth rate?
(Current Year/Previous Year)-1
gst relation?
PCO
WHY?
GST paid-GST Collected=GST owing
-Paid is first because Revenue GST is gonna be so HIGH! Because you earned a lot of $$$
-Collected will be lesser bceause not all costs are GST eligible
Balance Sheet; CCA section with Non-Current Assets
3
- Be careful with the acquisitions of different classes! USE ROWS 3&4 for their data!!! (the untouched cells)
Formula: (Last years acquistion+this years acquisition-this years disposition)
2.Accumulated CCA: LESS!! and also ACCUMULATED (add up every year as you go along) USE ROW 8 FOR ITS DATA
Formula: (Last years CCA + this years capital cost appreciation)
- Subtract accumulated CCA from the value you got in the first row of acquisiton/disposition cells
what are TWO SECTIONS YOU HAVE TO BE CAREFUL OFF?????
BALANCE SHEET CCA SECTION (3 POINTS)
INTEREST!!! (-)
INTEREST HOW DO YOU DO
ITS always positive; so in Income statement put
=-Interest
to make positive!!!
How to do Break evens?
why volume?
- Make a new sheet for break even (Copy income statement)
- Erase ONLY the volume cells for Breakeven Year!!!!
- use goal seek
Set cell=net income
to= 0
by chanigng cell= volume - whatever you get copy into breakeven volume section! USE FORMULA ROUNDDOWN
=ROUNDDOWN(volume,0) - Get Breakeven Revenue by multiplying Breakeven volume by price
bc this is the variable
