Expense Log (Personal)

• All columns identical to Research Expense Log, but with Category ID referencing only “Personal” type categories.

FORMULAS REQUIRED

• Budget vs Actual (Dashboard): =SUMIFS(ExpenseLog[Amount], ExpenseLog[Category ID], BudgetCategories[Category ID]) – to dynamically calculate actual spending per category.
• Remaining Budget: =Budgeted Amount - SUMIFS(ExpenseLog[Amount], ExpenseLog[Category ID], [@Category ID])
• Total Research Expenditure: =SUMIFS(ExpenseLog[Amount], ExpenseLog[Type], "Research")
• Income vs Expenses Ratio: =Total Income / Total Expenses (formatted as %)
• Funding Coverage Rate: =SUMIFS(ExpenseLog[Amount], ExpenseLog[Funding Source ID], "<>") / SUM(ExpenseLog[Amount]) – Shows percentage of research expenses covered by external funding.
• Month-to-Date Savings: =SUM(IncomeTracker[Amount]) - SUMIFS(ExpenseLog[Amount], ExpenseLog[Type], "Personal") - SUMIFS(ExpenseLog[Amount], ExpenseLog[Type], "Research")

CONDITIONAL FORMATTING

• Red fill: If Remaining Budget < 0 (overspent)
• Yellow fill: If Remaining Budget ≤ 10% of Budgeted Amount (warning threshold)
• Green fill: If Remaining Budget ≥ 50% of Budgeted Amount
• If Status = "Reimbursed" → Green border on entire row
• If Receipt Attached? = "No" and Status ≠ "Reimbursed" → Bold red text on Description column
• Income Tracker: Highlight “Research Grant” income in gold to distinguish from personal income.

USER INSTRUCTIONS

Begin by entering all your funding sources (e.g., university stipend, NIH grant, private foundation) on the Funding Sources sheet. Then define your monthly budget categories under Budget Categories, marking each as Research or Personal. Link each research expense to a specific funding source for audit compliance. Update your income weekly and expenses daily—this template thrives on consistency.

At the end of each month, review the Dashboard Overview for key metrics: Total Research Spending, % Coverage by Grants, Personal Savings Rate. If you're overspending on “Lab Supplies,” investigate if a cheaper vendor exists or if you can reallocate from a low-priority category. Use the Reports & Analytics sheet to export PDF summaries for your advisor or funding committee.

Never forget to attach digital receipts! The template flags missing documentation, critical for grant compliance and tax purposes.

EXAMPLE ROWS

• Budget Categories: 1, "Conference Travel", Research, $800, High, NSF Grant #XYZ123
• Income Tracker: 6/15/2024, NSF Grant Disbursement (Q2), Research Grant, $3500.00, USD
• Expense Log (Research): 6/21/2024, 1, "Flight to Neuron Conference", $678.50, USD, Yes, Approved, NSF Grant #XYZ123
• Expense Log (Personal): 6/18/2024, Rent - Apartment 3B, Personal Rent, $950.00, USD
• Savings & Goals: “Emergency Fund: Target $5K — Current: $3240 (65%)”

RECOMMENDED CHARTS & DASHBOARDS

The Dashboard Overview includes a responsive set of charts:

• Pie Chart: "Research vs Personal Spending" – Shows allocation balance.
• Stacked Column Chart: “Monthly Income Sources” – Reveals dependency on grants vs side work.
• Line Chart: “Cumulative Research Expenditure vs Budget” – Tracks adherence over time.
• Gauge Meter: “Funding Coverage Rate” – Visualizes % of research costs covered by grants (target: ≥70%).
• Sparklines: Embedded in the Budget Categories table to show spending trends per category over 6 months.

This template transforms chaotic research finances into an organized, auditable, and strategic asset. Whether you’re navigating a one-year postdoc or managing a multi-year PhD fellowship, the Financial View ensures you don’t just survive—you thrive—by aligning your personal economic health with your research ambitions.