This kakeibo template allows you to record and analyze your assets in addition to daily records.
Based on the "Free Template with Pipot Tables for a Solid Analysis," I have added more analytical functions.
-
-
Google Spreadsheet is also OK! Household account book with pivot table for solid analysis (free templates available)
I want to keep track of my household finances in a Google Spreadsheet.I also want to do a thorough analysis. Do you have such a ...
The goal of a kakeibo template is to be able to control your money.
What you need to do to achieve this
- Measure money correctly
- Visualize the flow of money
- Analyze how you spend your money
This version uses more graphs to make it easier to see the flow of money and to analyze it thoroughly.
Advantages of using Google Spreadsheets
- You do not need to purchase Excel to use it.
- You can input and check your financial data from anywhere with internet access.
- Can be shared with family members.
For these reasons, I created Google Spreadsheet kakeibo.
Features of Google Spread Kakeibo
Input Sheet
All entries, including asset entries, fund transfers, and daily household accounts are made on the input sheet.
Monthly sheet
Entering a month by selecting the month allows you to analyze the household accounts for the selected month.
Annual sheet
Displays changes in assets, amount spent by account, and annual changes in income and expenses.
Income and Expenditure Analysis Sheet
Based on the record of income and expenses on the annual sheet, the data is displayed in a graph by expense category.
Assets sheet
Graphical display of current asset status, amount spent by account, and asset trends.
Budget sheet
Enter your budget by month, and graphs show you at a glance which items you are overspending on.
Special Expense Sheet
Recorded special expenses for the year.
- Expenses Item can be set freely.
- Displays the amount spent each month by account.
- Current asset status and money flow for the month can be seen at a glance.
- Every time you record a household account, your assets are linked.
- Displays monthly credit card usage and charges.
- Displays a pie chart for each monthly item, showing which items you are overspending on.
- Monthly income and expenses are listed and graphed to show monthly trends at a glance.
- Monthly asset transition and consumption rate by asset are displayed in a table and graph, allowing you to see the monthly transition at a glance.
- The start date can be easily set to coincide with a payday or other date.
Differences between paid and free versions
In the free version, you can register accounts and know the amount spent by account, but you cannot manage assets.
In the paid version, the current asset amount is entered at the beginning, so the current asset amount can be grasped in conjunction with the account.
In the paid version, you can use many graphs to grasp and analyze the flow of your money.
The paid version costs US$20 for the full version.
How to use the Kakeibo
Download, copy and save
Download the PDF file and click on the Asset Kakeibo link.
This Kakeibo template uses one file per year.
Please copy and save the downloaded file before use.
After creating the original, click on "Make a Copy" for further use and name it "2023 AssetKakeibo" or something similar.
Setting
Enter expense item name
- Income
- Taxes
- Savings
- Fixed Expenses
- Variable expenses
Please enter expense names so that there are no duplicates.
For example, "Husband" for income name, "Husband" for savings name, etc.
- Fixed expenses: expenses that must be incurred every month (e.g., housing and education expenses)
- Variable expenses: Expenses that are incurred every month but vary in amount
Enter account name and store name.
Account names should be entered separately for asset and liability accounts.
Next to the liability account, there is a field to enter the closing date and payment date.
This is primarily for credit card closing dates and payment dates.
By entering the closing date and billing date, the credit card billing amount will appear on the monthly sheet.
In Store Name, enter the name of the store you frequent.
If you own a home, enter "house" in the Assets account; if you have an outstanding loan, enter "house loan" in the Liabilities account, etc.
Date Setting
You can change the start date to match your payday or other date.
Selecting a start date will be reflected in the month and annual sheets.
For year, select the year in which you want to start the household account.
Input Sheet
After completing the setup sheet, move to the input sheet.
Enter asset and liability amounts
First, enter the current asset and liability amounts.
You can only enter the asset and liability amounts once.
- Date
- Deposits and Withdrawals
- Assets
- Account
For deposits and withdrawals, select Deposits, and for accounts, select Accounts from the tabs. (Accounts reflect the contents of the input sheet.)
Debt account amounts should be shown as a negative number.
For the asset value of owner-occupied homes and cars, please enter the current valuation.
Entering Income and Expenses
Enter daily income and expense items.
- Date
- Item 1
Income, Expenses - Item 2
Income1, Taxes, Savings, Fixed Expenses, Variable Expenses - Category
- Amount
- Item name
- Name of store
- Account
Double-click on a date item to display the calendar.
When Item 1 is selected, Item 2 and the expense item name are linked.
Transfer funds
- Transfer funds to electronic money, etc.
- Transfer funds for savings
- Make a credit card payment
- Make a car or house loan payment
The funds transfer item is used primarily in these situations.
The information entered in the funds item is not included in consumption as funds are transferred between accounts.
To transfer money from an account to electronic funds, etc.
Select the date, select "Deposit" for the Deposit and Withdrawal items, enter the amount in the Asset Amount, and select "E-money" for the account.
On the same date, select "Withdrawal", enter the same amount, and for the account name, select the account to be transferred to e-money.
When transferring funds for savings
For example, if you want to transfer funds from "Bank2" to "Bank4, select "Expenses" and "Savings", then select "Bank2" for the account.
On the same day, select "Deposit", enter the same amount entered in Savings, and select "Bank4" for the account.
Since the savings amount is deducted from Bank2 in the Savings section, enter only the Deposit section in the Funds Transfer.
To make a credit card payment
On the date of the credit card payment, transfer funds from the account from which the payment was made to the credit card account.
Repay a home or car loan.
Enter the amount to be repaid in the Fixed Cost Car Expenses field and select the source of the account.
Select the funds transfer deposit, enter the same amount, and select "Car Loan".
Fixed Expenses sheet
The same format as the input sheet is provided.
Fixed expenses and other entries are entered on this sheet to eliminate the need for monthly entries for items other than variable expenses.
Dates are left blank, and fund transfers are also entered on this sheet for items that are required each month.
At the time the month changes, copy and paste everything except the headings into the input sheet.
After pasting to the input sheet, enter the date and correct any changes in amounts.
The product name and store name can be left blank.
Copies should be copied without headings.
Month sheet
There is one sheet for the month.
Select the year and month and enter a number from "1 to 12".
The year value will reflect the year entered in the settings.
For example, if you set the year as 2023, starting on the 1st of the month in the settings sheet, the income and expenditure status will be displayed for the period from January 1, 2023 to January 31, 2023.
Display Items
- Consumption by expense item
- Income/Expense Balance Bar Graph
- Amount of debt claims
- Assets of the month
- Pie Chart of Consumption Ratio by Expenses
- Liabilities billed this month
- Consumption by account
- Consumption by store name
- Percentage consumption by account and store name pie chart
- Variable expense calendar
- Variable Expenses Weekly Bar Graph
Consumption by expense category
Income and Expenditure Balance Graph
Consumption Percentage by Expenses Pie Chart
Debt Billing Amount
The amount billed will be displayed based on the closing date entered in the settings.
The closing date is the date when the credit card usage amount is totaled, and the billing date is the payment date.
If this month is March and the month ends on the 15th of each month and payment is due on the 10th of the following month,
the amount used from January 16 to February 15 will be paid on March 10.
Therefore, the exact billing amount will not be displayed for the first two months after you start the household account book.
Also, if you have not entered a closing date in the settings, the billing amount will not be displayed for debt accounts.
Depending on the start date you have set, there is a possibility that the accurate invoice amount will not be displayed even in the third month.
If you set the start date as the 25th, the household account will start on January 25th, so if the closing date is before the 25th, the exact billing date cannot be calculated.
Assets for this month
Amount Spent by Account
Consumption Amount by Store Name
Consumption Percentage Graph by Account and by Store Name
Variable Expenses Calendar
Click on the filter in cell B92 and uncheck (blank).
Variable Expenses Weekly
Budget sheet
Select the filter in cell B11 and uncheck (blank).
Manually enter only the budgeted items for each month.
The variance shows actual minus budget, and if the budget is exceeded, the graph will be yellow.
Special expense sheet
Special expenses are entered on the input sheet, but a special expenses sheet is provided to make it easier to see a list of special expenses for the year.
Enter the event name, budget, and actual expenses for each month.
The actual amount spent should also be entered on the input sheet.
Annual sheet
There are no input fields.
The information entered on the input sheet is displayed on a monthly basis.
- Assets
- Consumption Amount by Account
- Consumption Amount by Expenses
Click the filter in cell B14 and uncheck (blank).
Asset Fluctuations
Asset changes for brokerage firms and individual stocks are not supported.
If you want to keep a good record, please manually enter the monthly asset amount to correct it.
Spending by Seat
Spending by Fee Section
Income and Expenses Analysis Sheet
The graph shows the annual change in consumption by expense category.
Displayed items
- Income and Expenditure Balance
- Income
- Taxes
- Savings
- Fixed Expenses
- Variable expenses
- Special expenses
- Consumption by account, item, and store
Annual Trends from Store Name, Item Name, Account, and Remarks
Displays individual annual trends from slicers of store name, item name, account, and remarks.
To narrow down the list of store names, click on "Clear" in the store name slicer.
Once cleared, select a specific store name and click "OK".
Click the + button placed to the left of item 2 for more information.
To undo, click "Select All".
Asset Analysis Sheet
Display Items
- Target Assets
- Current assets
- Achievement rate
- Current asset allocation (bar graph)
- Amount spent by account (bar graph)
- Assets (bar graph)
Target Assets
Please enter your target assets.
Enter your target assets for this year and for the next several years.
Current Assets
Amount to be achieved
A bar graph displays the amount of assets remaining relative to the target assets set.
Current Asset Allocation
Amount Spent by Account
Changes in Assets
How to change currency units
The default currency unit is the dollar.
I use a macro to allow you to easily change the currency unit to your country's currency unit yourself.
To change the currency unit, do so on a sheet-by-sheet basis.
Click on Extensions,Macros to see the macro names.
The macro name is named after each sheet; if you have the Input sheet open, click on the Input macro.
The point where the currency unit is to be changed is automatically selected.
Click on Format,Number,Currency.
Select the currency unit for your country and click ”Apply".
Do all of this work for each sheet.
If you use a macro for the first time, a Required will be displayed. Click "Continue", "Allow" to use the macro.
Download
This Kakeibo sales are paid by credit card.
Payment is made through Stirpe.
Price: US$20
