Checklist: Setting Up Your Financial Dashboard (Spreadsheet)

This checklist guides you through the initial setup and regular use of the Automated Financial Dashboard spreadsheet.

Initial Setup:

• Understand the Sheets: Familiarize yourself with the three main sheets: Dashboard, IncomeLog, and ExpenseLog.
• Review Categories (Income): Go to the IncomeLog sheet. Review the default Category examples (Service, Digital Product, Affiliate Marketing). Add, remove, or rename categories to perfectly match your business income streams. Tip: Keep categories consistent for accurate reporting.
• Review Categories (Expenses): Go to the ExpenseLog sheet. Review the default Category examples (Software, Supplies, Marketing, Services, Travel). Customize these to reflect your specific business expenses.
• Clear Sample Data: Delete the sample rows provided in both the IncomeLog and ExpenseLog sheets. Do not delete the header row or the table structure itself.
• Input Historical Data (Optional but Recommended): For a complete picture, enter your income and expense data for the past few months or the year-to-date into the IncomeLog and ExpenseLog sheets. Ensure all required columns (Date, Source/Vendor, Amount, Category) are filled.
• Verify Dashboard Formulas: Go to the Dashboard sheet. Check that the formulas in the Value column (B2:B5) are referencing the correct tables (IncomeLog[Amount], ExpenseLog[Amount]). They should update automatically as you add data to the logs.
• Create Pivot Tables/Charts (Manual Step in Excel/Sheets):
  • Create a PivotTable based on the IncomeLog table to show SUM of Amount by Category (and potentially by Month). Insert a Pie or Bar chart based on this PivotTable onto the Dashboard sheet (replacing the placeholder).
  • Create a PivotTable based on the ExpenseLog table to show SUM of Amount by Category (and potentially by Month). Insert a Pie or Bar chart based on this PivotTable onto the Dashboard sheet (replacing the placeholder).
  • Create a summary table (or use PivotTables) to calculate total Income and Expenses per month. Create a Line or Bar chart showing Income vs. Expenses over time and insert it onto the Dashboard sheet (replacing the placeholder).
• Save As Template: Save a clean version of the spreadsheet with your customized categories and chart setups (but no transactional data) as a template for future use.

Regular Use (Daily/Weekly/Monthly):

• Log Income: As income is received, add a new row to the IncomeLog sheet, filling in the Date, Source, Amount, and Category.
• Log Expenses: As expenses are incurred, add a new row to the ExpenseLog sheet, filling in the Date, Vendor, Amount, and Category.
• Refresh Pivot Tables: If you created PivotTables for your charts, remember to refresh them periodically (usually via the Data or PivotTable Analyze tab in Excel) to ensure the charts on the Dashboard reflect the latest data.
• Review Dashboard: Regularly check the Dashboard sheet to monitor key metrics: Total Income, Total Expenses, Net Profit, Profit Margin, and category breakdowns via your charts.
• Analyze Trends: Use the dashboard and charts to identify trends in income sources, expense categories, and overall profitability over time.