excel workshop level 4 presentation
TRANSCRIPT
-
8/20/2019 Excel Workshop Level 4 Presentation
1/51
Excel Workshop Level 4:Preparing a Complete Set of Financial Statements
Using Excel Workbooks
Saturday, November 8, 20141: P! to ":" P!
Fiterman #all$ %oom F1&
'!CC
-
8/20/2019 Excel Workshop Level 4 Presentation
2/51
(fter completing this )orkshop$st*+ents )ill be able to:
• Create 'alance Sheets$ ,ncomeStatements an+ Statements ofStockhol+er-s E.*it/0
• S*mmarie 2ata )ith S*btotals0
• Use Excel to check their )ork0
• !ake a )orkbook compatible )ith
earlier versions of Excel0• %evie) the skills ac.*ire+ in the last
three levels )ith this Excel )orkshop0
-
8/20/2019 Excel Workshop Level 4 Presentation
3/51
(++ing 'or+ers
• ,n man/ portions of this exercise$ cellbor+ers sho*l+ be a++e+ to thebottom of certain cells to +isting*ish
them as special cells that signif/ the*se of a f*nction in the cell belo)3totals0
• 5hese +isting*ishe+ cells serve animportant p*rpose in the preparationof the 6nancial statements that /o*
)ill learn abo*t later0
-
8/20/2019 Excel Workshop Level 4 Presentation
4/51
,n this example$ singleline bor+ers )ill bea++e+ to Cells C7$C14 an+ C180
( 2o*ble Line )ill be
a++e+ to cell C90
-
8/20/2019 Excel Workshop Level 4 Presentation
5/51
Steps
• 5o a++ a bor+er$ highlight a cell 3inthe example$ cell C7 is highlighte+0%ight click the cell an+ select Format
Cell from the +rop+o)n men*0 ( ne))in+o) )ill appear on the screen0
-
8/20/2019 Excel Workshop Level 4 Presentation
6/51
Click on FormatCells.
(lternativel/$
/o* can alsoFormat Cells b/accessing theFormat Cellsicon in the #ome
5ab$ above theCells ribbon0
-
8/20/2019 Excel Workshop Level 4 Presentation
7/51
,nitiall/$ Presets )ill beset at ;E0 ( samplecell belo) is sho)n+irectl/ belo)0
Click on the o* can select +i?erent
bor+er st/les from the St/leribbon$ incl*+ing the singleline an+ +o*ble line bor+ers0
SingleLine'or+er
2o*bleLine'or+er
-
8/20/2019 Excel Workshop Level 4 Presentation
8/51
Steps 3Contin*e+
• Select a part of the cell in which youwant a border >o* can +o this b/ selectingone the icons s*rro*n+ing the sample cell$ or
b/ selecting one of the preset icons0• Select a border style from the Style
!ibbon 5he st/le /o* selecte+ )ill be sho)non the sample cell +irectl/ to the right0
•
;nce /o* are 6nishe+ choosing a bor+er@st/le$click ;A0 ( bor+er )ill be a++e+ to /o*rhighlighte+ cells0
-
8/20/2019 Excel Workshop Level 4 Presentation
9/51
Cell )ith a single linebor+er
Cell )ith a +o*bleline bor+er
-
8/20/2019 Excel Workshop Level 4 Presentation
10/51
Creating FinancialStatements
• 5he ,ncome Statement$ 'alanceSheet an+ Statement of Cash Flo)are the three most *sef*l 6nancial
statements in the 6el+ of acco*nting0 5o start creating these three vital+oc*ments$ /o* )ill start )ith a trial
balance 3list of transactions an+gra+*all/ transfer the +ata to threene) )orksheets to create the three
6nancial statements0
-
8/20/2019 Excel Workshop Level 4 Presentation
11/51
Steps
• ;pen the !S Excel File containing the(+B*ste+ 5rial 'alance for 2ress %ightClothing Corp0
• 'egin b/ highlighting onl/ the +ata portionof the trial balance0 ext$ /o* )ill sort the+ata 3from smallest to largest in the)orksheet b/ *sing the Sort tool *n+er
the "ata tab on the ribbon0Use this to sortthe +ata fromSmallest toLargest
-
8/20/2019 Excel Workshop Level 4 Presentation
12/51
( *ick ote
'efore contin*ing to step "$ create a ne) )orksheet 3Select Sheet 2
on the lo)er left corner of the screen0
• Cop/ all of the text@+ata from the (+B*ste+ 5rial 'alance an+ pasteit to the ne) )orksheet0
• 5o paste the +ata into the ne) sheet$ make Sheet 9 the active)orksheet 3click on it an+ then right click an/ cell in the)orksheet0
• Select #aste Special an+ a ne) )in+o) )ill appear0
• Select the $alues and Number Formats option *n+er the pasteoptions to have Excel paste the entiret/ of the +ata 3)hile retaining
the same n*mber format from the 6rst )orksheet to the secon+0• Finall/$ right click Sheet 9 an+ select rename$ Change the title ofSheet 9 to %&' (anual0
We )ill refer to this )orksheet later on0
-
8/20/2019 Excel Workshop Level 4 Presentation
13/51
When the PasteSpecial men* pops*p$ change thePaste ;ption to:$alues andNumber Formats0
-
8/20/2019 Excel Workshop Level 4 Presentation
14/51
Steps 3Contin*e+
• ;nce the +ata has been sorte+$highlight the +ata 3incl*+ing thehea+ers an+ create a table b/
going to the ,nsert 5ab an+ selectingthe icon0 When the ne) )in+o)appears$ check the box stating
table has hea+ers0=
-
8/20/2019 Excel Workshop Level 4 Presentation
15/51
Check
-
8/20/2019 Excel Workshop Level 4 Presentation
16/51
Steps 3Contin*e+
• ;nce /o* have create+ a table$ go tothe "esi)n &ab*&able &ools ribbonon the top of the screen an+ select
Convert to !an)e 3above the 5ools%ibbon0
• 5his )ill restore the +ata in the table
into reg*lar +ata$ b*t the col*mnhea+ers )ill remain in tact0
Click this
option0
-
8/20/2019 Excel Workshop Level 4 Presentation
17/51
-
8/20/2019 Excel Workshop Level 4 Presentation
18/51
;nce /o* click ok$ Excel
)ill create Subtotals foreach acco*nt0 >o* cantoggle thro*gh the vario*ss*btotals@s*mmaries Excelhas create+ b/ clicking on
the ne) b*ttons 31$ 9$"that appear on the left0
-
8/20/2019 Excel Workshop Level 4 Presentation
19/51
S*btotals for each acco*nt are no) liste+ *n+er the last cell ofeach acco*nt0
-
8/20/2019 Excel Workshop Level 4 Presentation
20/51
>o* have B*st 6nishe+ one of the most important parts in creatinga 6nancial statement0 We )ill no) leave the a+B*ste+ trialbalance an+ move onto the 9n+ )orksheet that )e title+ %&'(anual0
-
8/20/2019 Excel Workshop Level 4 Presentation
21/51
Steps
• #ighlight the range %4/"14 an+right click an/ cell0 Select FormatCells0 Do to the *mber tab0 Un+er
Categories$ select *mber0 ;n theright si+e$ set the 2ecimal place to an+ select the re+ set of n*mbers
*n+er parentheses *n+er egative*mbers0
-
8/20/2019 Excel Workshop Level 4 Presentation
22/51
Setting 2ecimalplaces to
-
8/20/2019 Excel Workshop Level 4 Presentation
23/51
Steps 3Contin*e+
• ;bserve the +ata in this )orksheet0!an*all/ Chan)e every numberunder the credit column into a
ne)ative number and then moveit into the "ebit column 3there areman/ shortc*ts to +oing this$
incl*+ing: *sing (*toFill beforecop/ing an+ pasting large sets ofn*mbers0
-
8/20/2019 Excel Workshop Level 4 Presentation
24/51
int/
,n a cell a+Bacent toan/ cell containing acre+it$ enter < 0=
(fter the G sign$ clickon the cell containingan amo*nt cre+ite+3this )ill pro+*ce a cell)ith a negativen*mber0
!ove the c*rsor to thebottom right corner ofthe cell0
When the c*rsorchanges to a small+ark
-
8/20/2019 Excel Workshop Level 4 Presentation
25/51
-
8/20/2019 Excel Workshop Level 4 Presentation
26/51
-
8/20/2019 Excel Workshop Level 4 Presentation
27/51
Creating the o* )ill *se (*toFill an+ theHL;;AUP f*nction in this )orksheetto verif/ the amo*nts that
correspon+s to each acco*nt0
-
8/20/2019 Excel Workshop Level 4 Presentation
28/51
Steps
• Create a ne) )orksheet b/ clicking Sheet "on the lo)er left si+e of the screen0 %ightclick the tab entitle+ Sheet " an+ selectrename0 ame the )orksheet
-
8/20/2019 Excel Workshop Level 4 Presentation
29/51
-
8/20/2019 Excel Workshop Level 4 Presentation
30/51
Steps 3Contin*e+
• ,n cell 27$ insert a HL;;AUP F*nction to have Excelsearch the (5' !an*al Worksheet for the amo*nt thatcorrespon+s to the acco*nt n*mber0 IEnter:HL;;AUP3(7$000J
•
(fter the cell containing the acco*nt n*mber$ select the%&' (anual 5or3sheet an+ highlight the range%6/"170$ then enter a comma0 Enter for col*mn in+exan+ t/pe False for %ange Look*p0 I5he form*la in cell 27sho*l+ rea+: HL;;AUP3(7$ K(5' 1K(4:2148$"$F(LSEJ
•
,n the 5able (rra/ portion of the HL;;AUP f*nction$ a++ a
-
8/20/2019 Excel Workshop Level 4 Presentation
31/51
Steps 3Contin*e+
• Create totals for each acco*nt *sing theSU! F*nction 3Cells: 219$ 29$ 29N$ 2"1$2"N$ 24&$ etc0
•Use the %utoSum f*nction on cell 291 to+etermine the s*m of the a+B*ste+ trialbalance0
• &he rand &otal must match therand &otal listed in the ori)inal%dusted &rial 'alance +& (S&AD%: 0?
-
8/20/2019 Excel Workshop Level 4 Presentation
32/51
>o*r )orksheet sho*l+ resemble theimage belo)0
-
8/20/2019 Excel Workshop Level 4 Presentation
33/51
Creating an ,ncomeStatement
• 5he +ncome Statement or pro6tan+ loss acco*nt is one of the6nancial statements of a compan/
an+ sho)s the compan/Ks reven*esan+ expenses +*ring a partic*larperio+0
• We )ill no) learn ho) to create an,ncome Statement *sing Excel0
-
8/20/2019 Excel Workshop Level 4 Presentation
34/51
Steps
• Create a ne) )orksheet 3)orksheet 40ame the )orksheet
-
8/20/2019 Excel Workshop Level 4 Presentation
35/51
Steps 3Contin*e+
• 5/pe: Sales !evenue in Cell (Q$ Cost of oods Sold incell (7$ ross #roEt in cell (&$ ;peratin) Apense incell (8$ ;peratin) +ncome in cell (17$ ;ther +ncome=epense in cell (1& an+ Net +ncome in cell (90
•
(++ 4 spaces before t/ping the follo)ing into theirrespective cells: Salaries in cell (1$ Supplies in cell(11$ !ent in cell (19$ "epreciation in cell (1"$ 'ad"ebt in cell (14$ !ent Apense in cell (1N an+ +nterest!evenue in cell (180
•
#ighlight cells C7$ '14 an+ C14$ '18 an+ C180 %ight clickthe cells an+ select Format Cells0 Select the bor+er 5ab$an+ then a++ a single bor+er to the bottom of the cell0(++ a +o*ble line bor+er 3*n+er st/le to cell C90
-
8/20/2019 Excel Workshop Level 4 Presentation
36/51
-
8/20/2019 Excel Workshop Level 4 Presentation
37/51
Steps 3Contin*e+
• Cop/ the n*mbers for each acco*nt in the ,ncomeStatement b/ +o*ble clicking each cell$ selecting theDro*ps Worksheet an+ selecting the total for eachacco*nt liste+ in the Dro*ps )orksheet0
•
Ex: 5o enter the +ata for Sales !evenue$ +o*ble clickcell CQ$ then select the Dro*ps Worksheet 3to make itactive0 ;n the Dro*ps Worksheet$ select cell 219Q an+press Enter0 5he form*la in cell C7 on the ,ncomeStatement sho*l+ be: -9roups?"127=0 Since sales
reven*e cannot be a negative val*e$ t/pe
-
8/20/2019 Excel Workshop Level 4 Presentation
38/51
Steps 3Contin*e+
• Note/ Cells C&$ C1Q$ C17 an+ C9 sho*l+ be blank at this time0
• 5he form*la for ross #roEt 3in this scenario is e.*al to Sales!evenue G Cost of oods Sold0 (++ a form*la to cell C& to reRectthat e.*ation0
• Use the Sum function in cell %17 to a++ the acco*nts liste+ as
;perating Expenses 3Salaries$ S*pplies$ %ent$ 2epreciation an+ 'a+2ebt Expense0
• 5he form*la for ;peratin) +ncome is e.*al to ross #roEt G &otal;peratin) Apenses0 5he form*la in cell C17 sho*l+ reRect thisform*la 3C&C1Q
• et ,ncome is e.*al to the ;peratin) +ncome %ny %dditional
!evenue G %ny %dditional Apenses0 5herefore$ the form*la in cellC9 sho*l+ reRect this form*la 3C17C1NC18
• #ighlight the range CQ:C90 %ight click the cells an+ select FormatCells0 Un+er the Categories$ select Currency0 Set the "ecimal #lacesto 0$ set the S/mbol to @ an+ select the fo*rth option 319"4 *n+eregative *mbers0
( complete+ ,ncome Statement for this
-
8/20/2019 Excel Workshop Level 4 Presentation
39/51
( complete+ ,ncome Statement for thisexercise0
Congrat*lations$ /o* have no) create+ a sample
income statement >o* are no) )ell on /o*r )a/ tocreating the last t)o 6nancial statements0
-
8/20/2019 Excel Workshop Level 4 Presentation
40/51
Creating a Statement of
Stockhol+er-s E.*it/
• 5he Statement of Stoc3holderHsAIuity is the 6nancial statement that+etails changes to the e.*it/ portion of
the balance sheet$ incl*+ing retaine+earnings$ common an+ preferre+ shares3as )ell as treas*r/ stock$ an+ othercomprehensive income0
• We )ill no) prepare a sample Statementof Stockhol+er-s E.*it/ in Excel0
-
8/20/2019 Excel Workshop Level 4 Presentation
41/51
Steps
• Create a ne) )orksheet 3)orksheet 40 amethe )orksheet
-
8/20/2019 Excel Workshop Level 4 Presentation
42/51
Steps 3Contin*e+
• 5/pe 'alance at July 1, 2011 in cell (7$ +ssue of Common Stoc3 incell (&$ et ,ncome for *l/ 911 in cell (N$ Less: 2ivi+en+s in cell (8$an+ 'alance at July 1, 2011 in cell (10
• #ighlight cells C8 an+ 280 %ight click the cells an+ select Format Cells0Select the bor+er 5ab$ an+ then a++ a single bor+er to the bottom of thecell0 (++ a +o*ble line bor+er 3*n+er st/le to cells '1$ C1 an+ 210
• ,n this scenario$ no common stock )as iss*e+ at *l/ 1$ 911 an+ no+ivi+en+s )ere pai+ +*ring the perio+0 Anter -0. in cells '6, ' andF0
• For the ,ss*e of Common Stock an+ %etaine+ Earnings 'alance at *l/ 1$911$ *se the +ata in the Dro*ps )orksheet to give /o* a val*e0
• Ex: 5o enter the +ata for +ssue of Common Stoc3 $ +o*ble click cell '&$then select the Dro*ps Worksheet 3to make it active0 ;n the Dro*psWorksheet$ select cell 211" an+ press Enter0 5he form*la in cell '& onthe ,ncome Statement sho*l+ be: -9roups?"11=0 Since commonstock cannot be a negative val*e$ t/pe
-
8/20/2019 Excel Workshop Level 4 Presentation
43/51
Steps 3Contin*e+
• &he value for Net +ncome under !etainedAarnin)s (S& be ta3en from thecorrespondin) cell in the +NC;(AS&%&A(AN&?
• Use the (*toS*m tool to generate the 'alanceat July 1, 2011 for cells '1$ C1 an+ 210
• Note/ Cells C8, "K and " should be blan3?
• 5he 6nishe+ Statement of Sharehol+er-s E.*it/sho*l+ match the image in the next sli+e0
-
8/20/2019 Excel Workshop Level 4 Presentation
44/51
-
8/20/2019 Excel Workshop Level 4 Presentation
45/51
Creating a 'alance Sheet
• 5he 'alance Sheet is a s*mmar/ ofthe 6nancial balances of a b*sinessorganiation are liste+ as of a
speci6c +ate$ s*ch as the en+ ofits 6nancial /ear or the en+ of aperio+0
• We )ill no) prepare a sample'alance Sheet in Excel0
-
8/20/2019 Excel Workshop Level 4 Presentation
46/51
Steps
• Create a ne) )orksheet an+ renameit: 'alance Sheet0 !ake the 'alanceSheet the active )orksheet0
• Enter the appropriate text in cell tomatch the image in the next sli+e0
-
8/20/2019 Excel Workshop Level 4 Presentation
47/51
-
8/20/2019 Excel Workshop Level 4 Presentation
48/51
Steps 3Contin*e+
• #ighlight the range C&:2"90 %ight click the cells an+select Format Cells0 Un+er the Categories$ selectNumbers0 Set the "ecimal #laces to 0$ an+ selectthe fo*rth option 319"4 *n+er egative *mbers0
• #ighlight cells C1$ 21"$ C1&$ 21&$ 294$ C98$ 2"10%ight click the cells an+ select Format Cells0 Selectthe bor+er 5ab$ an+ then a++ a single bor+er to thebottom of the cell0 (++ a +o*ble line bor+er 3*n+erst/le in cell 2"90
• ;nce again$ *se the +ata from the roups5or3sheet to 6ll the val*es in col*mn 2 3Cells "/KL8, 11L1, 21L24, and 2K0
-
8/20/2019 Excel Workshop Level 4 Presentation
49/51
Steps 3Contin*e+
• Follo) the same proce+*re )ith cells CL10 and C16L1K0 5he acco*nts in these cells contain an amo*nt +ebite+ an+an amo*nt cre+ite+T therefore$ the amo*nt cre+ite+ m*st bes*btracte+ from the amo*nt +ebite+0 Use the Sumfunction in cells 21 an+ 21& to 6n+ the correct amo*nt
+ebite+0• Use the +ata from the Statement of Sharehol+er-s E.*it/ to
6n+ the val*es for cell C98 an+ C"0 Use the S*m f*nctionin cell 2"1 to 6n+ the &otal ShareholderHs AIuity
• &otal :iabilities and ShareholderHs AIuity is e.*al to
&otal ShareholderHs AIuity Current :iabilities :on) &erm :iabilities0 ,n cell 2"9$ a++ the n*mbers incells 29Q$ 2"1 an+ 29& to calc*late 5otal Liabilities an+Sharehol+er-s E.*it/0
-
8/20/2019 Excel Workshop Level 4 Presentation
50/51
-
8/20/2019 Excel Workshop Level 4 Presentation
51/51