Create accounting system with Microsoft excel
Create accounting system with Microsoft excel
Please download the sample file for your reference.
https://1drv.ms/x/s!At0ZS5gxdK0ChNRBu…
Content
19.366 -> The account chart is sharing in the description, please download from the sharing link for your reference.
25.266 -> Let's check the Account Chart first.
37.033 -> Check Account by statement
42.233 -> Check the chart of Account for Balance Sheet.
47.766 -> Check the Chart of Account of Profit or Loss.
61.933 -> Create a sheet for General Journal.
97.7 -> Set the date format as you like.
102.433 -> Type "Month/Date" and "Enter"
106.6 -> Journal No. is "YYMMDD-Serial no."
115.9 -> To create a double entry.
124.3 -> Use "Ctrl+D" to copy the content of the above line.
129.333 -> To key in Acc Code, let's check it in the chart of account.
138.6 -> We use the formula of "xlookup" to fill the "Account Title".
186.366 -> Set the format of amount.
190.8 -> Copy the "Debit Amount" to the "Credit Amount".
194.866 -> Set the "Month" from the "Date".
206.9 -> Use "Ctrl+D" copy from the above cell
210.366 -> Select the range, than format as table.
216.966 -> Set the Account Code at the center
219.966 -> Keep making entries
246.5 -> Another journal entry is finished.
255.6 -> Select all, and change the font of text to "Calibri".
271.6 -> One more journal entry is finished.
292.2 -> One more journal entry is finished.
311.8 -> One more journal entry is finished.
546.766 -> Add account.
604.633 -> Insert a "PivotTable" to calculate all journal entry.
659.8 -> Copy the balance sheet account to "Balance Sheet".
674.6 -> Set up the format of the balance sheet
752.2 -> Copy the profit or loss account to the sheet of the profit or loss.
790.533 -> Use the VLOOKUP formula to find the amount from PivotTable, then calculate it.
817.533 -> Let the Debit amount deduct the Credit amount.
844.866 -> Let the Credit amount deduct the Debit amount for Liabilities Account.
864.733 -> "Profit or Loss for Current Period" will be calculated from "Statement of Profit or Loss".
996.266 -> You also can create a depreciation sheet of the fixed assets when you create a fixed asset journal.
1042.966 -> Accumulated Depreciation up to 31 Oct 2021
1070.7 -> Add the sample journal entry of next month.
1121.733 -> Click the "Refresh All" to load new data.
1182.666 -> You also can create a depreciation sheet of the fixed assets when you create a fixed asset journal.
1282.9 -> "Total Liabilities and Equity" is equal to "Total Assets".
Source: https://www.youtube.com/watch?v=eU5HrLMjfy4