Analysis of Japan Travel Expense (202608)

• Source: 111.xlsx (77 entries)

Reporting conventions: All amounts are in CNY, to two decimals; discounts are shown as negative numbers; rounding is round-half-up. The source sheet has no exchange-rate column, so no weighted-average exchange-rate analysis is presented.

Methodology and accounting conventions: All reported amounts are presented in CNY to two decimal places. Where underlying transaction amounts contain fractions of a cent, rounding follows the round-half-up rule.. Discounts are shown as negative numbers — per-item discounts, card coupon packages and bank cashback all appear in the single "Discount" column. Cash spending is recorded in the main ledger as entered. Tax refunds are recorded separately from the main ledger and are analysed in sections 8 and 9.

1. Overview

The main trip took place from 4 to 14 June 2026 (11 days), with six pre-trip expenses recorded between 28 May and 1 June (airfare, data SIM, insurance, Tokyo lodging, tickets and a Suica top-up). The ledger contains 77 entries: 73 regular purchase entries plus 4 discount and cashback offset entries, recorded on 14 separate dates between 28 May and 14 June 2026.

Ledger (CNY)

Across the 14 dates on which spending was recorded, average daily spending was CNY 927.23 after tax refunds and CNY 936.15 before refunds. Dividing the same post-refund total by the 11 days of the main trip gives CNY 1,180.11 per day.

The CNY 113.99 of discounts and offsets comprises CNY 13.76 of per-item discounts, CNY 80.00 of card coupon packages and CNY 20.23 of bank cashback. Two tax refunds totalling CNY 124.95 were received separately from the main ledger, hence CNY 13,106.15 − CNY 124.95 = CNY 12,981.20.

Structure: transportation (airfare, Shinkansen, Suica top-ups) accounted for the largest share, at 37.1%, followed by shopping at 23.7%, dining at 18.9% and lodging at 15.1%; entertainment, telecom and insurance together account for about 5.3%.

Payment and channels: card payments predominated, with cash at just 4.7% of net spending. Higher-value expenses were generally booked or paid for online (OTA average CNY 1,110.61 per entry), while smaller purchases were paid in store (average CNY 126.96 per entry); In-store purchases account for 50 of the 77 ledger entries and 48.4% of net spending.

Savings: discounts and refunds together saved CNY 238.94, or 1.8% of gross spending before discounts and offsets — approximately CNY 1.80 saved for every CNY 100 in gross spending.

Bottom line: spending is concentrated in a handful of large items and dispersed across many small ones; the top 10 entries account for 65.4% of net spending, so controlling the largest items controls the budget.

2. Ledger Reconciliation and Tax-Refund Adjustment

This section uses double-entry bookkeeping principles to combine the reconciliation of recorded spending with the tax-refund adjustment, setting out the accounts, the debit and credit direction and the balance rule. The identity is simple: every debit has a credit of equal amount (a reconciliation of the recorded amounts, rather than a complete set of accounting books).

2.1 Accounts and debit/credit direction

These account labels are used for analytical reconciliation in this report and are not intended to represent a formal accounting classification.

2.2 Journal entries

All figures in CNY.

Entry 1 — Reconciliation of recorded spending

Dr  Gross travel spending (6001)                          13,220.14
    Cr  Cash and bank balances · actual payment (1002)    13,106.15
    Cr  Discounts (6002)                                  113.99

Balanced: CNY 13,220.14 = CNY 13,106.15 + CNY 113.99, i.e. gross spending less discounts equals net spending.

Entry 2 — Recording tax refunds received

Dr  Cash and bank balances · refund received (1002)         124.95
    Cr  Tax refunds received (6003)                         124.95

Balanced: the CNY 124.95 debit to the bank equals the CNY 124.95 credit to tax refunds received. Net spending after refunds then follows: CNY 13,106.15 − CNY 124.95 = CNY 12,981.20.

2.3 Summary reconciliation

Debits equal credits at CNY 13,345.09 — the reconciliation balances.

2.4 Net derivation

• Net cash outflow = CNY 13,106.15 − CNY 124.95 = CNY 12,981.20 (the net reduction in bank balances attributable to these transactions).
• Net spending = CNY 13,220.14 − CNY 113.99 − CNY 124.95 = CNY 12,981.20 (the expense-side net).
• Both routes agree at CNY 12,981.20, confirming that net spending after refunds = net spending in the main ledger − tax refunds received.

3. Category Deep Dive

The table covers the 73 regular purchase entries (after per-item discounts, excluding the 4 discount and cashback offset entries) and totals CNY 13,206.38. That is deliberately not the same as the CNY 13,106.15 of net spending: subtract the 4 coupon and cashback offsets (80.00 + 20.23 = 100.23) and the result is CNY 13,206.38 − CNY 100.23 = CNY 13,106.15, matching the ledger in section 2.

Category shares are calculated against the CNY 13,206.38 total for the 73 regular purchase entries, after per-item discounts but before coupon and cashback offsets. They therefore do not use the CNY 13,106.15 net ledger total as the denominator.

What the spending covered — the three largest expenses in each category

• Transportation (CNY 4,901.18, 37.1%, 15 entries): the round-trip airfare of CNY 2,921.50 alone is 59.6% of the category. The two Shinkansen legs add CNY 1,214.34 (580.25 + 634.09), 11 Suica top-ups amount to CNY 718.54, and the airport taxi CNY 46.80. The high share is essentially an airfare effect; local transport was inexpensive.
• Shopping (CNY 3,125.67, 23.7%, 9 entries): Montbell Osaka at CNY 1,672.57 is 53.5% of the category, followed by Montbell Ginza at CNY 309.06 and Narita duty-free heated tobacco at CNY 287.64. The remainder comprises Uniqlo at Narita (CNY 230.74), Sevenstar (CNY 186.60), two Akihabara 4F purchases worth CNY 351.43 and two drugstore items at Senso-ji worth CNY 87.63. The sheet records the shop and the amount only — no item-level detail; the two Montbell purchases generated tax refunds (section 8).
• Dining (CNY 2,494.87, 18.9%, 37 entries): the highest number of entries in the ledger (48% of all entries), with individual items mostly between CNY 20 and CNY 170 — frequent, relatively small transactions. One cash entry of CNY 622.14 (24.9% of the category) is recorded as a single lump sum rather than split by receipt. The largest card-paid dining expense is the grilled beef dinner at CNY 169.23, followed by the Mt. Fuji day-tour lunch at CNY 168.21, a purchase of bread, beer, yogurt and other items at a 7-Eleven in Tokyo at CNY 138.88, and a Yang Guofu malatang dinner in Osaka at CNY 120.39.
• Lodging (CNY 1,989.42, 15.1%, 7 entries): Tokyo 3 nights (Jun 4–7) at CNY 720.82, Tokyo 2 nights (Jun 11–13) at CNY 415.84, Osaka 2 nights (Jun 8–10) at CNY 402.14, Fujisawa 1 night (Jun 6–7) at CNY 246.78 and Osaka 1 night (Jun 10–11) at CNY 165.19, plus two hotel laundries totalling CNY 38.65. Lodging spending was concentrated in a small number of bookings, with several nights paid for at a time.
• Entertainment (491.77) / telecom (119.47) / insurance (84.00): together CNY 695.24, or 5.3%. The SIM was bought once for the whole trip, insurance was a single policy, and entertainment consisted of ticket-related expenses.

4. Daily Spending Patterns

Daily amounts are summed from the raw entries and then rounded (round-half-up); the cumulative column rolls those daily figures forward. The 14 dates add up to CNY 13,106.16 — CNY 0.01 above the ledger total of CNY 13,106.15 — because several days contain half-cent entries (65.285, 41.635, 402.135 and others) that are rounded day by day. The final cumulative figure is therefore a daily-rounded cumulative, not a second measure of total spending. For reconciliation purposes, the ledger total of CNY 13,106.15 is the authoritative figure; the CNY 0.01 difference arises from rounding daily subtotals separately.

• Highest-spending day: 28 May at CNY 3,124.97 — airfare 2,921.50, data SIM 119.47 and insurance 84.00, all paid before departure, bringing cumulative spending to approximately CNY 3,125 in a single day.
• Top three days: 28 May (CNY 3,124.97), 9 June (CNY 1,875.97, driven by the CNY 1,672.57 Montbell Osaka purchase) and 10 June (CNY 1,686.40, driven by the CNY 634.09 Shinkansen fare and CNY 415.84 Tokyo lodging). Together CNY 6,687.34, or 51.0% of net spending.
• Lowest-spending day: 4 June at CNY 161.04 — departure day, entirely snacks, McDonald's at Hong Kong airport, a taxi and a Suica top-up.
• Rhythm: 28 May to 1 June was the pre-trip booking window at CNY 4,032.88 (30.8%). 4 June, the departure day, cost just CNY 161.04. 5–6 June formed a Tokyo mini-peak (shopping, tickets, lodging bookings) at CNY 2,121.57 combined. 7 June was the transfer to Osaka (CNY 580.25 Shinkansen). 9 June was the Osaka shopping peak at CNY 1,875.97, and 10 June the return to Tokyo (CNY 634.09 Shinkansen). 11–12 June fell back to CNY 285.50 and CNY 184.18. 13 June included final purchases at airport duty-free shops, CNY 918.19, and 14 June was the journey home at CNY 627.78 (net of a CNY 30.00 coupon offset). The 71 entries recorded during the main trip total CNY 9,073.27, or 69.2% of net spending.

5. Payment Methods

Reporting conventions note: the current sheet has no "payment method" column. The breakdown above is based on the previously compiled transaction-level records; it totals exactly CNY 13,106.15, matching net spending, and is shown for reference only. Entry counts include the 4 coupon and cashback offsets (which are not separate purchases), so the column totals 77 entries rather than 73 purchase entries.

• Agricultural Bank of China (ABC) debit card: 28 entries, CNY 4,851.25, CNY 173.26 per entry — the largest by amount, used for online bookings such as airfare and the day tour, and for most Suica top-ups.
• Shanghai Pudong Development Bank (SPD) credit card: CNY 4,414.99 net (CNY 4,515.22 of spending less CNY 100.23 of coupon and cashback offsets) — the card used for the largest number of entries, covering everyday food, shopping and transport.
• China Guangfa Bank (CGB) credit card: CNY 3,154.81 at CNY 242.68 per entry, used for several high-value purchases. The CCB debit card and Huabei handled only small amounts.
• Cash: CNY 622.14 (¥15,000 at an implied rate of CNY 0.04148 per JPY), just 4.7% of net spending — a trip in which card payments predominated and cash played a supporting role.
• Total: CNY 13,106.15, matching net spending.

6. Spending by Purchase Channel

Purchases are classified by purchase channel rather than by payment method. "In-store" refers to purchases made at physical retail or service locations, while "official websites" and "OTA" refer to purchases booked or completed online. For example, a Shinkansen ticket purchased through an official website is classified as an online purchase through the official channel, even though the journey itself takes place in person.

Entry counts include the 4 coupon and cashback offsets, so the channel table also totals 77 entries.

• In-store (48.4%): 50 entries at CNY 126.96 each — convenience stores, drugstores, restaurants, department stores and duty-free shops. Highest number of entries and the main channel for everyday purchases.
• Official websites (26.1%): 24 entries at CNY 142.76 each — lodging, Shinkansen tickets, attraction tickets and Suica top-ups purchased or arranged directly through official channels; second by count, small in value but frequent.
• Online travel agency (OTA; 25.4%): 3 entries at CNY 1,110.61 each — airfare, the data SIM and the Mt. Fuji day tour. Fewest entries, largest amounts.
• Together they total CNY 13,106.15. In short, higher-value expenses were generally paid online, while everyday purchases were more commonly made in person.

7. Top 10 Highest-Value Entries

The top 10 total CNY 8,569.26, or 65.4% of net spending — 10 of the 77 ledger entries (13.0%) account for nearly two-thirds of net spending, so controlling them controls the budget.

The breakdown is as follows: 3 transport entries (airfare plus two Shinkansen legs) at CNY 4,135.84, 3 lodging entries at CNY 1,538.80, 2 shopping entries at CNY 1,981.63, 1 dining entry at CNY 622.14 and 1 entertainment entry at CNY 290.85.

8. Discounts and Tax Refunds

In the ledger, discounts and offsets are shown as negative amounts; in the savings summary, they are shown as positive amounts to indicate savings.

The CNY 113.99 of discounts sits in entry 1 as a credit to discounts, reducing gross spending; the CNY 124.95 of tax refunds is recorded separately from the main ledger and credited to tax refunds received in entry 2. Together CNY 238.94, or 1.8% of gross spending before discounts and offsets — approximately CNY 1.80 saved for every CNY 100 in gross spending. The ratio is modest, but coupon discounts and cashback reduced the cost of purchases that were already planned, while tax refunds are a rebate received after purchase on eligible goods, not a discount granted at the point of sale.

Both tax refunds are noted as received in the sheet, totalling CNY 124.95 — 4.0% of shopping spend. The Osaka Montbell tax refund of CNY 105.56 alone is 6.3% of that purchase.

9. Cash Usage

• ¥20,000 in cash was brought to Japan; ¥15,000 (recorded as CNY 622.14) was actually spent and is recorded in the main ledger (2026-06-14, under Dining, in-store).
• Cash accounts for only 4.7% of net spending — card payments predominated, and cash was used mainly as a backup payment method.
• On the rate: the recorded amount of CNY 622.14 prevails. The implied rate is 622.14 ÷ 15,000 ≈ 0.041476, shown as CNY 0.04148 per JPY; multiplying 15,000 by the displayed rate gives CNY 622.20, a difference of CNY 0.06 caused by displaying the exchange rate to five decimal places, which requires no adjustment.
• About ¥5,000 remained at the end of the trip (roughly CNY 200), as noted in the source sheet.
• The ¥15,000 is recorded as one lump sum under Dining. Splitting it by receipt would sharpen the category mix slightly, but the amount is too small to change the overall structure.

10. Exchange Rate Note

The dataset (111.xlsx) does not record yen amounts or exchange rates per item — all amounts are recorded directly in CNY — so no weighted-average exchange-rate analysis is presented. The cash figure of ¥15,000 = CNY 622.14 (implied rate CNY 0.04148 per JPY) is set out in section 9. The conversion rates applied to card transactions depend on the applicable card-network and card-issuer conversion and settlement rules, which are outside the scope of this report.

11. Conclusions and Recommendations

• Structure: transportation (airfare), shopping and lodging together account for 75.9% — large, concentrated items plus dispersed daily spending. Dining has the highest number of entries but the most fragmented amounts, and the ledger captures day-to-day spending in considerable detail.
• Payment: the division of spending across the three cards worked well: ABC for larger expenses, SPD for everyday purchases, and CGB for supplementary spending. Coupons and cashback were applied successfully. Future trips could concentrate large purchases on whichever card offers the best cashback at the time.
• Cash: usage below 5% suggests carrying less cash next time, reducing the need for currency exchange and the inconvenience of handling leftover cash (about ¥5,000 remains this time).
• Refunds: both Montbell tax refunds were received, totalling CNY 124.95. Consolidating eligible purchases at the same retailer in one visit saves time at the refund counter and may make it easier to meet the minimum purchase threshold for a tax refund.
• Bookkeeping: 77 entries across 14 dates with spending is a fine level of detail. Keeping discounts negative and offsets on their own rows makes the ledger self-checking — the reconciliation in section 2 is simply a summary of this sheet.

---

In short: The 11-day trip to Japan had a final recorded net cost of CNY 12,981.20 across 77 ledger entries, after discounts and tax refunds. — about CNY 927 per date with spending, or CNY 1,180 per trip day — with 65.4% of it concentrated in just 10 entries.