Accounting Cycle & Excel

$$ The file $($HW$1)$ is available at Pilot

$$ The file contains $4$ worksheets:

$$ Chart $($of accounts$)$

$$ Ending balances as of $11/30/2023$

$$ Transactions $($of December $2023)$

$$ Combined $($Beginning balances & Transactions$)$

$2$

Relevant Excel functions

$$ Reference Functions

$$ Linking between cells in the same worksheet

$$ Linking between cells in different workbooks

$$ Linking to a $($range$)$ name in another workbook

$$ Relative or Absolute referencing

$$ VLOOKUP$($v$1,$ v$2,$ v$3)$

$$ v$1$: the code; v$2$: the coding system; v$3$: the column# in the

coding system where the needed information is retained

$$ Range Name $($purpose: to make it user$-$friendly$)$

$1.$ Highlight the data range

$2.$ Click $$Formulas$$

$3.$ Click $$Name Manager$$

$4.$ Define the range name

$3$

Relevant Excel functions $($cont$.)$

$$ Data sort $($purpose: to organize data$)$

$1.$ Highlight the data range

$2.$ Click $$Data$$

$3.$ Click $$Sort$$

$4.$ Define the sorting orders $($GL$,$ Sub, & Date$)$

$4$

Excel HW #$1(5$ points$)$

$$ Download HW$1.$xlsx from the Content Menu at Pilot to your own

device

$$ Open the file from your own device

$$ Use the $$Combined$$ worksheet

$$ One of the transactions on $12/2/2023($rows $61$ & $62)$ is listed

below:

Cash $4,000$

Sales $4,000$

$$ Use the last digit of your WID $($i$.$e$.,$ w###abc; not UID$),$ multiply

the last digit by $202,$ add the product to $2,024,$ and use the result

to replace the $4,000$ in the above transaction

$$ Create a range name for your chart of accounts & use your last

name $($up to $4$ characters$)$ to name the range

$$ Use vlookup to display the accounts $($description$)$ & use the

range name for the $2$nd variable in the vlookup function

$5$

Excel HW #$1($cont$.)$

$$ Use Data Sort to convert the $$Combined$$ worksheet into general

ledgers

$$ Calculate the ending balance of your cash account

$$ Select $$Save As$$ and add your last name $($up to $4$characters plus

a space$)$ in front of $$HW$1$ to create a new Excel file showing

your work

$$ Submit the above file to the $($HW$1)$ drop box by $9/3(7$:$00$ pm$)$

Note: Similar to the typical order in the general ledger, your cash

account must show the beginning balance first followed by all the

December transactions $($in chronological order$)$ and the correct ending