Section 6-P&L from Scratch

Question 1

What is P&L

Answer 1

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.

Question 2

What elements are inside Data Source and what is Data Source

Answer 2

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.

Question 3

What is the process of Ordering Source Worksheets(years)

Answer 3

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.

Question 4

Whatassociated information is in Name of Partner company segment

Answer 4

External companies, Related companies and total of related.

Question 5

What is the specification of Amount

Answer 5

+ is for Expenses and (-) for Revenues.

Question 6

What is code and what is it’s creation purpose

Answer 6

Code is a unique id for each transaction.

Question 7

What is the process of creating codes and hints

Answer 7

+(Account Number)&(Partner Number) —then—> Paste special for only value and remove the formula.

Question 8

The code contains two parts which one is first and which one is the second

Answer 8

First Account number and second Partner number and should not be replaced.

Question 9

Creating database with V-Lookup has 8 main steps, what are they

Answer 9

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.

Question 10

Which elements and whyshould be fixed inside V-Lookup function

Answer 10

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.

Question 11

Which header columns are not pasted into Database

Answer 11

Amount and Account number. But we should separate columns for each year for the Amount.

Question 12

What is the process for building up years amounts

Answer 12

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.

Question 13

Which function should we use for completing database amounts

Answer 13

Sum-If

Question 14

Which elements should be fixed inside Sum-Iffunction

Answer 14

Column of reference and column of sum reference.

Question 15

How to make revenues positive in database

Answer 15

Add (-)before sum if.

Question 16

How to test the Database

Answer 16

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).

Question 17

What is Mapping

Answer 17

There is no ruling for mapping but the whole idea is grouping accounts into one related group.

Question 18

How to build-up the structure of P&L

Answer 18

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….

Question 19

Now we have mapping in our Data base what’s next

Answer 19

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.

Question 20

What is total revenues

Answer 20

Has three main segments: Net Sales, Other Revenues, Recharges

Question 21

What is below the total revenues

Answer 21

Direct costs

Question 22

What is below the direct costs

Answer 22

Gross Margin

Question 23

What is Gross Margin

Answer 23

Difference between Total revenues and Direct costs(Total revenues-Direct costs)

Question 24

What is below the Gross Margin

Answer 24

Operating costs

Question 25

What is the nature of Operating costs

Answer 25

Are not variable in nature.

Question 26

Give some examples from Operating costs

Answer 26

Leasing, Other operating costs, other incomes, capitalized costs…

Question 27

What is below the Operating costs

Answer 27

EBIDTA

Question 28

What is EBITDA

Answer 28

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.

Question 29

What does EBITDA show

Answer 29

It evaluates company’s performance without(or before) considering it’s financial decisions, tax environment and other accounting decisions.

Question 30

What is below the EBITDA

Answer 30

D&A

Question 31

What is EBIT

Answer 31

EBITDA - D&A

Question 32

What should we add before EBIT(two items)

Answer 32

Financial items and Extraordinary items.

Question 33

What is EBT

Answer 33

Stands for Earning before Tax.

Question 34

What should we add before EBT

Answer 34

Taxes

Question 35

What is Net Income

Answer 35

EBT - Taxes

Question 36

How many key main sections does P&L contain and how many total sections

Answer 36

Total 12 and 6 sum sections.

Question 37

The whole P&L has three main general phases what are they and mention the key details

Answer 37

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

Question 38

Review the P&Lvocabulary in one minute and the process for getting into these terms

Answer 38

Net Sales, Total Revenue, Gross Margin, Net Income

Question 39

What is next after sorting the items in mapping

Answer 39

Add years in new columns and make table beautiful.