At most one email a week. Unfollow anytime with one click.
Transcript
The full narration of this video. The current sentence is highlighted while the video plays; click any sentence to jump there.
Show transcript
Introduction
This is the final result we will create, a March cashbook complete with running balance, total income and expenses, summary of expenses per category, surplus or deficit status, and a pie chart. Everything is automatically calculated, and it's ready to print. Let's start. Hello, welcome to the Excel tutorial for beginners, final project: cashbook and monthly report. In this final video of the series, we will combine everything we've learned, table formatting, formulas, SEM, SEMIP, IP, charts, up to print settings, to create a neat monthly financial report.
Step 1: March Cash Book Notes
This is a one-month cashbook record, date, description, category, income, and expenses. The data is still plain. We will turn it into a complete report. First, let's tidy up the appearance as we learned in the table formatting tutorial.
Step 2: Table Header, Border Lines, and Column Width
Select the table header, A1 to F1, press Ctrl B, then Ctrl 1, Tab Fill, and choose light blue color. Then, bold and add borders to the entire table via the Border tab. Finally, widen all columns by double-clicking on the column boundary line.
Step 3: Date Format and Indonesian Rupiah
Next, format the numbers. Select the date column, A2 to A15, press Ctrl 1, Custom, then type the date format DD, Space, MMM, Space, YYYY. Then select D2 to E16 and use the same currency format as before.
Step 4: Running Balance
Now the balance column. In F1, type balance. In F2, type equals D2 minus E2. In F3, the formula is different, equals F2 plus D3 minus E3, which is the previous balance plus income minus expenses. Copy the F3 formula down, and we now have a running balance.
Step 5: Total Row
In row 16, type total. In D16, sum up the income using SEM, then in E16, sum up the expenses. In F16, the final balance is D16 minus E16. Bold the total row with Ctrl B.
Step 6: Summary of Expenses with SUMIF
Now the summary of expenses per category. In cell H1, type category, and in cell I1, type expenditure. In cells H2 to H5, type food, bills, transport, and others. In cell I2, type SEMIP, open parenthesis, C2 to C15, semicolon, H2, semicolon, E2 to E15, close parenthesis. Use F4 to lock the range C and E SEMIP sums up expenditure only for matching categories. Copy down.
Step 7: Remaining Balance and Status with IF
Two more important pieces of information. In cell H7, type remaining balance, and in cell I7, type equal to F16. In cell H8, type status, and in cell I8 use IP. If remaining balance is greater than or equal to 0, display surplus, if not, display deficit. With this, we can know the financial condition of this month at a glance.
Step 8: Pie Chart of Expenses
It's time for the chart. Select H1 to I5, then open the insert tab and choose pie chart. Move the chart next to the summary table, and give it the title 'Expenditure per Category'. Now we can see which category spends the most money.
Step 9: Print Ready and Saving
Finally, prepare for print. Open the page layout tab, then change orientation to landscape so all columns fit on one page. Press Ctrl P to view the print preview. Press Escape to return, then save with Ctrl S. Our monthly financial report is now complete.
Done! Monthly Financial Report is Complete
Done. We have created a complete checkbook, with formatting, running balance, total, summary SEM IP, status IP, pie chart, and print settings. Congratulations, you have completed the Excel for beginners series. Thank you for watching, see you in other tutorial series. Thank you for watching, see you in other tutorial series.
Ask the class material
Type a question or keyword; we find the part of this episode that explains it and jump the video there.
My notes
Notes are saved with the current video time and are only visible to you.
Questions & answers
Stuck on a step? Ask here; the instructor and other learners can answer.
No questions yet.
Your instructor
Cerdas Inovasi Digital Team
Software developers, BIM & 3D engineers, and AI practitioners
53 video classes1,286 episodes
We build software, BIM models, games and AI automations for clients every day, and turn that hands-on work into step-by-step classes: every step is shown on screen and comes with a PDF guide you can keep.
