Statements

Statement Analysis

Common Size Analysis

Ratio Analysis

Ratio Introduction

Liquidity Ratios

Capital Structure/Solvency Ratios

Valuations

Discounted CashFlow Method

WACC Calculation

Valuation With Multiples

Terminal Value Method

Finalisation of Accounts

FAQs

FAQs on Equity Research

Charting Technique

Scroll Bar Charts

DropDown Chart

Charts with Check boxes

Chart with Vertical Line

Dual Axis Chart

Classification of Accounts

We can classify the accounts into 2 categories

(i)Personal (ii) Impersonal

Again Impersonal account is classified into (i)Real (ii) Nominal

Personal:

Applicable to humans
i.e., Debtors, Creditors,...

Real:

Applicable to Assets of the firm
i.e., Buildings, Plant and Machinery...

Nominal:

Applicable to Revenues and Expenses of the firm
i.e., Sales Paid, Commission Received...

Debit Balance and Credit Balance

Every account should start with either Debit Balance or Credit Balance

Accounting Treatment of Assets:

Accounting Treatment of Operating Expenses:

Accounting Treatment of Liabilities:

Accounting Treatment of Capital:

Accounting Treatment of Sales:

Structure of Trail Balance:

Rule to update Journals:

There must be two accounts (i) Debit (ii) Credit

Every DEBIT transaction of one account has Equivalent amount of CREDIT transaction of another account

Classification Entries

Opening Entries

Is used to transfer the closing entries of previous year to current year

Closing Entries

Rectification of Entries

Transfter Entries

Used to transfer the Gross Profit from Trading Account to Profit and Loss Account

Public sh As Worksheet, Accounts As Variant, NewSh As Worksheet
Function DefineWorksheet()
Set sh = ThisWorkbook.Sheets("Journal Ledger")
End Function

Sub Create_Ledger_Accounts()
DefineWorksheet
'=================Trading and Profit and Loss Account copy========
Dim TradeSH As Worksheet
Set TradeSH = ThisWorkbook.Sheets.Add(after:=Sheets("Transactions"))
TradeSH.Name = "Trading and ProfitLoss Account"
TradeSH.Activate
RemoveGridLines
rownumber = 2
Do Until sh.Range("Z" & rownumber).Value = ""
TradeSH.Range("B" & rownumber + 3).Value = sh.Range("Z" & rownumber).Value
rownumber = rownumber + 1
Loop
rownumber = 2
Do Until sh.Range("AA" & rownumber).Value = ""
TradeSH.Range("E" & rownumber + 3).Value = sh.Range("AA" & rownumber).Value
rownumber = rownumber + 1
Loop
TradingLastRow = 5 + TradeSH.UsedRange.Rows.Count - 1
ISMStartingRow = TradingLastRow + 5
r = ISMStartingRow - 2
rownumber = 2
Do Until sh.Range("AE" & rownumber).Value = ""
TradeSH.Range("B" & r + rownumber).Value = sh.Range("AE" & rownumber).Value
rownumber = rownumber + 1
Loop
rownumber = 2
r = ISMStartingRow - 2
Do Until sh.Range("AF" & rownumber).Value = ""
TradeSH.Range("E" & r + rownumber).Value = sh.Range("AF" & rownumber).Value
rownumber = rownumber + 1
Loop
ISMLastRow = 5 + TradeSH.UsedRange.Rows.Count - 1
'=======Create the Names to Trading and Profit and Loss Account==========
For c = 1 To TradeSH.UsedRange.Columns.Count
If c = 1 Or c = 4 Then
For r = 1 To TradeSH.UsedRange.Rows.Count
If TradeSH.UsedRange.Cells(r, c).Value <> "" Then
'ThisWorkbook.Names.Add Name:=TradeSH.UsedRange.Cells(r, c).Value, RefersTo:=TradeSH.UsedRange.Range(Cells(r, c), Cells(r, c + 1))
ThisWorkbook.Names.Add Name:=NameAfterFormatting(TradeSH.UsedRange.Cells(r, c).Value), RefersTo:=TradeSH.UsedRange.Range(Cells(r, c), Cells(r, c + 1))
RemoveGridLines
End If
Next
End If
Next
'======Copy Balance Sheet========
Dim BSH As Worksheet
Set BSH = ThisWorkbook.Sheets.Add(after:=Sheets("Trading and ProfitLoss Account"))
BSH.Name = "Balance Sheet"
BSH.Activate
RemoveGridLines
rownumber = 2
Do Until sh.Range("AI" & rownumber).Value = ""
BSH.Range("B" & rownumber + 3).Value = sh.Range("AI" & rownumber).Value
rownumber = rownumber + 1
Loop
rownumber = 2
Do Until sh.Range("AJ" & rownumber).Value = ""
BSH.Range("E" & rownumber + 3).Value = sh.Range("AJ" & rownumber).Value
rownumber = rownumber + 1
Loop
BSHLastRow = 5 + BSH.UsedRange.Rows.Count - 1
'====Create the Names to Balance Sheet==
For c = 1 To BSH.UsedRange.Columns.Count
If c = 1 Or c = 4 Then
For r = 1 To BSH.UsedRange.Rows.Count
If BSH.UsedRange.Cells(r, c).Value <> "" Then
'ThisWorkbook.Names.Add Name:=TradeSH.UsedRange.Cells(r, c).Value, RefersTo:=TradeSH.UsedRange.Range(Cells(r, c), Cells(r, c + 1))
ThisWorkbook.Names.Add Name:=NameAfterFormatting(BSH.UsedRange.Cells(r, c).Value), RefersTo:=BSH.UsedRange.Range(Cells(r, c), Cells(r, c + 1))
End If
Next
End If
Next
'================== =
'================Create Sheets for Ledgers== ===
'======== ============
For i = 20 To 22
rownumber = 2
Do Until sh.Cells(rownumber, i).Value = ""
If sh.Cells(rownumber, i).Value <> "Balance b/d" Then
ThisWorkbook.Sheets.Add after:=Sheets(ThisWorkbook.Sheets.Count)
Set NewSh = ActiveSheet
RemoveGridLines
NewSh.Name = sh.Cells(rownumber, i).Value
Formatting_Ledger
End If
rownumber = rownumber + 1
Loop
Next
'======================================
'===Post the amounts to Ledger==========
'=========================
For r = 2 To sh.Range("A2").End(xlDown).Row
sh.Activate
sh.Range("B" & r).Activate
'MsgBox sh.Range("B" & r).Value
If InStr(sh.Range("B" & r).Value, "Balance b/d") = 0 Then
If Mid(sh.Range("B" & r).Value, 1, 1) = " " Then
sheetname = Left(sh.Range("B" & r).Value, Len(sh.Range("B" & r)) - 5)
sheetname = Right(sheetname, Len(sheetname) - 9)
ThisWorkbook.Sheets(sheetname).Activate
Set NewSh = ActiveSheet
Lastrow = NewSh.Range("F" & Rows.Count).End(xlUp).Row + 1
NewSh.Cells(Lastrow, 6).Value = sh.Range("A" & r - 1).Value
NewSh.Cells(Lastrow, 7).Value = "By " & sh.Range("B" & r - 1).Value
NewSh.Cells(Lastrow, 8).Value = sh.Range("D" & r - 1).Value
End If If Mid(sh.Range("B" & r).Value, 1, 1) <> " " Then
sheetname = Left(sh.Range("B" & r).Value, Len(sh.Range("B" & r)) - 5)
ThisWorkbook.Sheets(sheetname).Activate
Set NewSh = ActiveSheet
Lastrow = NewSh.Range("B" & Rows.Count).End(xlUp).Row + 1
NewSh.Cells(Lastrow, 2).Value = sh.Range("A" & r + 1).Value
NewSh.Cells(Lastrow, 3).Value = LTrim(sh.Range("B" & r + 1).Value)
NewSh.Cells(Lastrow, 4).Value = sh.Range("E" & r + 1).Value
End If
End If
Next
'======= Trading and Profit and Loss - Debit Side=====
r = 5
TradeSH.Activate
Do Until TradeSH.Range("B" & r).Value = ""
If TradeSH.Range("B" & r + 1).Value <> "" Then
ThisWorkbook.Sheets(TradeSH.Range("B" & r).Value).Activate
Set NewSh = ActiveSheet
SheetLastRow = 3 + NewSh.UsedRange.Rows.Count - 1
If TradeSH.Range("B" & r).Value = "Purchase Returns" Then
NewSh.Range("H" & SheetLastRow + 2).Value = "=sum(H5:H" & SheetLastRow & ")"
NewSh.Range("D" & SheetLastRow + 2).Value = NewSh.Range("H" & SheetLastRow + 2).Value
Deductions = WorksheetFunction.Sum(Range("D5:D" & SheetLastRow))
NewSh.Range("H" & SheetLastRow + 1).Value = NewSh.Range("H" & SheetLastRow + 2).Value - Deductions
NewSh.Range("G" & SheetLastRow + 1).Value = NewSh.Name & " " & "c\f"
Amount = "-" & NewSh.Range("H" & SheetLastRow + 1).Value
Else:
NewSh.Range("D" & SheetLastRow + 2).Value = "=sum(D5:D" & SheetLastRow & ")"
NewSh.Range("H" & SheetLastRow + 2).Value = NewSh.Range("D" & SheetLastRow + 2).Value
Deductions = WorksheetFunction.Sum(Range("H5:H" & SheetLastRow))
NewSh.Range("H" & SheetLastRow + 1).Value = NewSh.Range("H" & SheetLastRow + 2).Value - Deductions
NewSh.Range("G" & SheetLastRow + 1).Value = NewSh.Name & " " & "c\f"
Amount = NewSh.Range("H" & SheetLastRow + 1).Value
End If
FormatBodyOfLedgers
TradeSH.Activate
TradeSH.Range("B" & r).Offset(0, 1).Value = Amount
End If
If TradeSH.Range("B" & r + 1).Value = "Gross Profit" Then
r = r + 5
End If
r = r + 1
Loop
'====Trading and Profit and Loss - Credit side====
r = 5
TradeSH.Activate
For r = 5 To ISMLastRow
If TradeSH.Range("E" & r).Value <> "Profit b/d" And _
TradeSH.Range("E" & r).Value <> "" Then
ThisWorkbook.Sheets(TradeSH.Range("E" & r).Value).Activate
Set NewSh = ActiveSheet
SheetLastRow = 3 + NewSh.UsedRange.Rows.Count - 1
If TradeSH.Range("E" & r).Value = "Sales Returns" Then
NewSh.Range("D" & SheetLastRow + 2).Value = "=sum(D5:D" & SheetLastRow & ")"
NewSh.Range("H" & SheetLastRow + 2).Value = NewSh.Range("D" & SheetLastRow + 2).Value
Deductions = WorksheetFunction.Sum(Range("H5:H" & SheetLastRow))
NewSh.Range("H" & SheetLastRow + 1).Value = NewSh.Range("H" & SheetLastRow + 2).Value - Deductions
NewSh.Range("G" & SheetLastRow + 1).Value = NewSh.Name & " " & "c\f"
Amount = "-" & NewSh.Range("H" & SheetLastRow + 1).Value
Else:
NewSh.Range("H" & SheetLastRow + 2).Value = "=sum(H5:H" & SheetLastRow & ")"
NewSh.Range("D" & SheetLastRow + 2).Value = NewSh.Range("H" & SheetLastRow + 2).Value
Deductions = WorksheetFunction.Sum(Range("D5:D" & SheetLastRow))
NewSh.Range("D" & SheetLastRow + 1).Value = NewSh.Range("D" & SheetLastRow + 2).Value - Deductions
NewSh.Range("C" & SheetLastRow + 1).Value = NewSh.Name & " " & "c\f"
Amount = NewSh.Range("D" & SheetLastRow + 1).Value
End If
FormatBodyOfLedgers
TradeSH.Activate
'TradeSH.Range(TradeSH.Range("B" & r).Value).Columns(2).Value = Amount
TradeSH.Range("E" & r).Offset(0, 1).Value = Amount
End If
Next
'======Calculate Gross Profit and Net Profit====
TradeSH.Activate
TradeSH.Cells(TradingLastRow + 2, 6).Value = "=sum(F5:F" & TradingLastRow & ")"
'TradeSH.Cells(r + 2, 6).Value = "=sum(F7:F" & r & ")"
TradeSH.Cells(TradingLastRow + 2, 3).Value = TradeSH.Cells(TradingLastRow + 2, 6).Value
Deduction = WorksheetFunction.Sum(Range("C5:C" & TradingLastRow - 1))
TradeSH.Cells(TradingLastRow, 3).Value = TradeSH.Cells(TradingLastRow + 2, 3).Value - Deduction
GrossProfitRowNumber = r ' Used to copy the data in P & L Account
TradeSH.Range("Profitbd").Columns(2).Value = TradeSH.Range("GrossProfit").Columns(2).Value
TradeSH.Cells(ISMLastRow + 2, 6).Value = "=sum(F" & ISMStartingRow & ":F" & ISMLastRow & ")"
TradeSH.Cells(ISMLastRow + 2, 3).Value = TradeSH.Cells(ISMLastRow + 2, 6).Value
Deduction = WorksheetFunction.Sum(Range("C" & ISMStartingRow & ":C" & ISMLastRow - 1))
TradeSH.Cells(ISMLastRow, 3).Value = TradeSH.Cells(ISMLastRow + 2, 3).Value - Deduction
'==Finalisation of Balance Sheet Assets==========
r = 5
BSH.Activate
Do Until BSH.Range("B" & r).Value = ""
If BSH.Range("B" & r).Value <> "Add Net Profit" Then
ThisWorkbook.Sheets(BSH.Range("B" & r).Value).Activate
Set NewSh = ActiveSheet
SheetLastRow = 3 + NewSh.UsedRange.Rows.Count - 1
NewSh.Range("H" & SheetLastRow + 2).Value = "=sum(H5:H" & SheetLastRow & ")"
NewSh.Range("D" & SheetLastRow + 2).Value = NewSh.Range("H" & SheetLastRow + 2).Value
Deductions = WorksheetFunction.Sum(Range("D5:D" & SheetLastRow))
NewSh.Range("D" & SheetLastRow + 1).Value = NewSh.Range("D" & SheetLastRow + 2).Value - Deductions
NewSh.Range("C" & SheetLastRow + 1).Value = NewSh.Name & " " & "c\f"
Amount = NewSh.Range("D" & SheetLastRow + 1).Value
FormatBodyOfLedgers
BSH.Activate
'TradeSH.Range(TradeSH.Range("B" & r).Value).Columns(2).Value = Amount
BSH.Range("B" & r).Offset(0, 1).Value = Amount
End If
r = r + 1
Loop
'=====Accounts finalisation Liabilities=====
r = 5
BSH.Activate
Do Until BSH.Range("E" & r).Value = ""
ThisWorkbook.Sheets(BSH.Range("E" & r).Value).Activate
Set NewSh = ActiveSheet
SheetLastRow = 3 + NewSh.UsedRange.Rows.Count - 1
NewSh.Range("D" & SheetLastRow + 2).Value = "=sum(D5:D" & SheetLastRow & ")"
NewSh.Range("H" & SheetLastRow + 2).Value = NewSh.Range("D" & SheetLastRow + 2).Value
Deductions = WorksheetFunction.Sum(Range("H5:H" & SheetLastRow))
NewSh.Range("H" & SheetLastRow + 1).Value = NewSh.Range("H" & SheetLastRow + 2).Value - Deductions
NewSh.Range("G" & SheetLastRow + 1).Value = NewSh.Name & " " & "c\f"
Amount = NewSh.Range("H" & SheetLastRow + 1).Value
FormatBodyOfLedgers
BSH.Activate
'TradeSH.Range(TradeSH.Range("B" & r).Value).Columns(2).Value = Amount
BSH.Range("E" & r).Offset(0, 1).Value = Amount
r = r + 1
Loop
TradeSH.Activate
Balancesheet_TradingAccount_Format
BSH.Range("C6").Value = TradeSH.Range("NetProfit").Columns(2).Value
BSH.Activate
BSH.Range("F" & BSHLastRow + 2).Value = "=sum(F5:F" & BSHLastRow & ")"
BSH.Range("C" & BSHLastRow + 2).Value = "=sum(C5:C" & BSHLastRow & ")"
Balancesheet_TradingAccount_Format
'BodyFormat
End Sub

Function Formatting_Ledger()
With NewSh.Range("B3:H3")
.Merge
.Value = NewSh.Name & " Account"
.Interior.ColorIndex = 9
.HorizontalAlignment = xlCenter
.Font.Name = "Century"
.Font.Size = 20
.Font.ColorIndex = 2
.Font.Bold = True
End With
Union(NewSh.Range("B4"), NewSh.Range("F4")).Select
Selection.Value = "Date"
Union(NewSh.Range("C4"), NewSh.Range("G4")).Select
Selection.Value = "Particulars"
Union(NewSh.Range("D4"), NewSh.Range("H4")).Select
Selection.Value = "Amount"
With NewSh.Range("B4:H4")
.HorizontalAlignment = xlCenter
.Font.Name = "Century"
.Font.Size = 15
.Font.ColorIndex = 9
.Font.Bold = True
NewSh.UsedRange.Columns.AutoFit
.Columns(4).ColumnWidth = 0.5
End With
End Function

Function FormatBodyOfLedgers()
With ActiveSheet.UsedRange
.Font.Size = 15
.Font.Name = "Century"
.Columns.AutoFit
.Columns(4).Interior.ColorIndex = 9
End With
End Function

Function NameAfterFormatting(ResultString)
For i = 1 To Len(ResultString)
If UCase(Mid(ResultString, i, 1)) Like "[A-Z]" Then
NameAfterFormatting = NameAfterFormatting + Mid(ResultString, i, 1)
End If
Next
End Function

Function RemoveGridLines()
ActiveWindow.DisplayGridlines = False
End Function

Function Balancesheet_TradingAccount_Format()
Set NewSh = ActiveSheet
With NewSh.Range("B3:F3")
.Merge
.Value = NewSh.Name
.Interior.ColorIndex = 9
.HorizontalAlignment = xlCenter
.Font.Name = "Century"
.Font.Size = 20
.Font.ColorIndex = 2
.Font.Bold = True
End With
Union(NewSh.Range("B4"), NewSh.Range("E4")).Select
Selection.Value = "Particulars"
Union(NewSh.Range("C4"), NewSh.Range("F4")).Select
Selection.Value = "Amount"
With NewSh.Range("B4:F4")
.HorizontalAlignment = xlCenter
.Font.Name = "Century"
.Font.Size = 15
.Font.ColorIndex = 9
.Font.Bold = True
End With
BodyFormat
NewSh.UsedRange.Columns(3).ColumnWidth = 0.5
NewSh.UsedRange.Columns(3).Interior.ColorIndex = 9
RemoveGridLines
End Function

Sub AddValuesToUserForm()
' Define worksheet
DefineWorksheet
'Create an Array
Accounts = Array("Personal", "Real", "Nominal")
'Add array to the List box1 and Listbox2
UserForm1.ListBox1.List = Accounts
UserForm1.ListBox2.List = Accounts
UserForm1.TextBox2.Value = "MM/DD/YYYY"
UserForm1.Show
End Sub

Sub Delete_Worksheets()
Application.DisplayAlerts = False
For Each sh In ThisWorkbook.Sheets
sh.Activate
Set sh = ActiveSheet
If sh.Name <> "Introduction" And _
sh.Name <> "Journal Ledger" And _
sh.Name <> "Transactions" Then
sh.Delete
End If
Next
ThisWorkbook.Worksheets("Journal Ledger").Activate
Application.DisplayAlerts = True
End Sub

Function BodyFormat()
NewSh.UsedRange.Font.Size = 15
NewSh.UsedRange.Font.Name = "Century"
NewSh.UsedRange.Columns.AutoFit
End Function