Section 6-P&L from Scratch Flashcards
What is P&L
Every company has set of financial activities such as revenue, expense or cost within one fiscal year. P&L is to summarize whole details to one summary of revenues, costs and expenses.
What elements are inside Data Source and what is Data Source
It has 5 main columns: Account name, Partner company(or number), Partner name, Amount, Account number. Data Source is the first step towards Profit and Loss statement. Inside this all details and repeating items are available.
What is the process of Ordering Source Worksheets(years)
First, we put correct headers(P&L Account, Partner Company, Code, Amount and Account Number), Second, align them all to left and adjust the width of columns. Third, Filter only totals. Four, Delete the total cells. Five, we remove the filter and get back to new table. Six, We repeat to all years.
Whatassociated information is in Name of Partner company segment
External companies, Related companies and total of related.
What is the specification of Amount
+ is for Expenses and (-) for Revenues.
What is code and what is it’s creation purpose
Code is a unique id for each transaction.
What is the process of creating codes and hints
+(Account Number)&(Partner Number) —then—> Paste special for only value and remove the formula.
The code contains two parts which one is first and which one is the second
First Account number and second Partner number and should not be replaced.
Creating database with V-Lookup has 8 main steps, what are they
First, move the Database column inside each sheet to first column.AND DON’T FORGET TO REMOVE THE DUPLICATES 2-Add header rows till Partner name(before amount) and add column for each sheet year. 3-Insert V-Lookup formula into first cell next to Code.4-Fix only the lookup value from inside of Database (e.g. $B4). 5- Fix the reference area both row and column.DON’T FORGET THAT THE AREA IS BEFORE AMOUNT COLUMN. 6- Change the column number of other cells till finish first sheet. 7- Now we have some cells with N.A which belong to other sheets go on those cells. 8- Do the same but only change the reference area until there be no N.A cell remained.
Which elements and whyshould be fixed inside V-Lookup function
Only column for Lookup value, Both column and row for Reference areas(DON’T FORGET THAT THE AREA IS BEFORE AMOUNT COLUMN). Because when we move downward we want the lookup value gets down inside on column but we range only a limited area.
Which header columns are not pasted into Database
Amount and Account number. But we should separate columns for each year for the Amount.
What is the process for building up years amounts
We should use Sum-If function. First select the range(Select whole column) from the year sheet and fix column, Second back to database select the first cell, Third back to year sheet and select the amount column and fix the column. Four, add (-)before formula to make revenue positive and cost negative.
Which function should we use for completing database amounts
Sum-If
Which elements should be fixed inside Sum-Iffunction
Column of reference and column of sum reference.
How to make revenues positive in database
Add (-)before sum if.
How to test the Database
First, we should filter and find Net Income and reverse the sign because the sum of all other accounts should be equal to Net Income and by this we can have a sum for Zero—>Second,Add sum at the end of each year column inside Database and it should be Zero(0).
What is Mapping
There is no ruling for mapping but the whole idea is grouping accounts into one related group.
How to build-up the structure of P&L
1-We should copy new created Mapping column from Database and then paste in a new final sheet(P&L). 2-Remove duplicates 3- Sorting items based on below….
Now we have mapping in our Data base what’s next
We have to copy and paste to a new sheet which is our final place: P&L. Paste and remove duplicates. We should add additional rows such as Total revenue… and etc… to complete.
What is total revenues
Has three main segments: Net Sales, Other Revenues, Recharges
What is below the total revenues
Direct costs
What is below the direct costs
Gross Margin
What is Gross Margin
Difference between Total revenues and Direct costs(Total revenues-Direct costs)
What is below the Gross Margin
Operating costs
What is the nature of Operating costs
Are not variable in nature.
Give some examples from Operating costs
Leasing, Other operating costs, other incomes, capitalized costs…
What is below the Operating costs
EBIDTA
What is EBITDA
EBIDTA stands for Earnings before interest, tax, depreciation and amortization and contains: Gross Margin(Total revenue -Direct costs)-Operating cost or on the other hand is revenue minus costs of making products or services.
What does EBITDA show
It evaluates company’s performance without(or before) considering it’s financial decisions, tax environment and other accounting decisions.
What is below the EBITDA
D&A
What is EBIT
EBITDA - D&A
What should we add before EBIT(two items)
Financial items and Extraordinary items.
What is EBT
Stands for Earning before Tax.
What should we add before EBT
Taxes
What is Net Income
EBT - Taxes
How many key main sections does P&L contain and how many total sections
Total 12 and 6 sum sections.
The whole P&L has three main general phases what are they and mention the key details
Phase One: Ordering the sheets: Inside each year first remove the Totals and then put headers from P&L Account to Account number. Phase two: Database Creation: Add one column named Code which is combining the Account number with Partner Company—>Create and new sheet named Database and create a new table that has additional columns in years—> Copy and Paste the database column of all years and remove duplicates—> Use V-Lookup or better option is Index+Match and fill the new database sheet with information collected from other years. —> Use Sum-if to fill information from other years and put (-) before the formula—> Check the correctness by finding Net Income and reversing the positive and then back and see if sum of each year is Zero. Phase Three: Mapping and P&L—> Inside the Data Base sheet add one column and name is Mapping–> Group or Map the account names into groups which there is no specific rule for this and varies company to company. —> Create a new sheet named P&L Statement and copy and paste the Mapping to new sheet—> Remove duplicates —> Sort revenues( Net Income, Other Revenues, Recharges)—> Add a row named: Total revenue that sum of the revenues—> Put Direct Costs below—> Put a new row named: Gross Margin—> Organize Operating costs below like Lease, Other Incomes, Capital Costs and etc… —> Add a new row named EBIDTA(Shows the operating performance without the financial decisions)—> Put D&A below the EBIDTA —> Add a row named EBIT —> Put Financial Items and Extraordinary Items below —> Add a row named: EBT —> Put Taxes below —> Add a new row and name it Net Income
Review the P&Lvocabulary in one minute and the process for getting into these terms
Net Sales, Total Revenue, Gross Margin, Net Income
What is next after sorting the items in mapping
Add years in new columns and make table beautiful.
