PREMIUM HR EXPENSE DASHBOARD — COPILOT MASTER PROMPT You are an expert Microsoft Excel Dashboard Designer, Data Analyst, UX Designer, and Financial Reporting Specialist. Using the existing HR expense transaction data in this workbook, create a premium, executive-level HR Expense Dashboard directly inside this Excel workbook. Do NOT create fake/sample data. Use ONLY the existing transaction data. 1. DATA SOURCE Use the existing transaction table containing these fields: Date Department Category Amount (INR) Payment Method Approval Status The current dataset contains approximately 40 expense transactions covering January–July 2024. Before creating the dashboard: Inspect the entire dataset. Identify the actual table/range. Convert the source data into a proper Excel Table if it is not already one. Give the table a meaningful name such as: tblExpenses Make sure Date is recognized as a real Excel date. Make sure Amount (INR) is numeric. Do not modify or delete the original transaction records. 2. DASHBOARD OBJECTIVE Create a dashboard that allows an HR manager, Finance manager, or business executive to quickly understand: Total spending Number of transactions Average expense Pending expenses Spending trend Department spending Expense category distribution Approval status Payment method usage High-value expenses Areas requiring management attention The dashboard should look like a premium SaaS / CFO / Executive Analytics dashboard, not a basic Excel report. 3. PREMIUM DARK THEME Use a sophisticated dark theme. Background Use a deep navy/charcoal background approximately: #0B1220 Cards Use slightly lighter dark panels: #111827 and #172033 Primary text #F8FAFC Secondary text #94A3B8 Accent colors Use these consistently: Cyan: #38BDF8 Purple: #A78BFA Green: #34D399 Amber: #FBBF24 Red: #FB7185 Do NOT use excessive bright colors. The dashboard should feel: Premium Modern Minimal Executive Professional Clean Data-focused Avoid unnecessary borders, gradients, 3D effects, clip art, decorative icons, and excessive shadows. 4. DASHBOARD STRUCTURE Create a sheet named: Dashboard Place the dashboard in the following hierarchy. HEADER At the top: HR EXPENSE DASHBOARD Subtitle: Executive Expense Analytics | January–July 2024 Add a small "Last Updated" indicator using the latest Date available in the source data. 5. KPI CARDS Create four large premium KPI cards at the top. KPI 1 — Total Expense Calculate: SUM(Amount) Display as: ₹26.46 L or dynamically calculated from the dataset. Label: TOTAL EXPENSE Use cyan as the accent. KPI 2 — Transactions Calculate: COUNT of transactions Label: TRANSACTIONS Use purple as the accent. KPI 3 — Average Expense Calculate: Total Expense / Number of Transactions Label: AVERAGE EXPENSE Use green as the accent. KPI 4 — Pending Expense Calculate the total amount where: Approval Status = Pending Label: PENDING EXPENSE Use amber as the accent. Also display the percentage of total expense represented by pending expenses if space permits. 6. MONTHLY EXPENSE TREND Create a professional Line Chart. Title: Monthly Expense Trend X-axis: Month Y-axis: Expense (INR) Use monthly totals from the actual Date field. Do NOT use a pie chart for this. The chart should clearly show: January February March April May June July Use a cyan line with subtle markers. Add data labels only if they improve readability. Important: If July contains only partial-month data, visually or textually indicate: July is partial-month data Do not incorrectly interpret July as a complete month. 7. DEPARTMENT ANALYSIS Create a horizontal bar chart. Title: Expense by Department Use: Department → Total Expense Sort departments from highest to lowest. Expected departments may include: Marketing Engineering Sales HR Finance Operations Use a clean horizontal bar design. Highlight the highest-spending department using the primary accent color. 8. EXPENSE CATEGORY ANALYSIS Create a horizontal bar chart. Title: Expense by Category Use all actual categories in the dataset. Sort descending by total expense. Examples may include: Advertising Commission Events Cloud Services Benefits Software Hardware Travel Consulting Research Audit Training Recruitment Design Maintenance Entertainment Supplies Do NOT manually enter these values. Calculate them dynamically from the source table. 9. APPROVAL STATUS Create a compact doughnut chart. Title: Approval Status Show: Approved Pending Rejected Use actual expense amounts rather than transaction counts. Display percentage labels. Make Pending visually noticeable using amber. Make Rejected visually noticeable using red. 10. PAYMENT METHOD Create a column or horizontal bar chart. Title: Expense by Payment Method Use the actual payment methods from the data. For example: Corporate Card Bank Transfer Cash Calculate total expense by payment method. Sort from highest to lowest. 11. TOP EXPENSE TRANSACTIONS Create a small table on the dashboard called: Top 5 Expenses Columns: Date Department Category Amount Approval Status Sort by Amount descending. Show only the five largest transactions. Format Amount as INR. Use conditional formatting to highlight unusually large expenses. 12. MANAGEMENT INSIGHTS Create a professional insight section titled: Management Insights Do NOT write generic statements. Analyze the actual data and automatically generate insights such as: Highest-spending department Lowest-spending department Highest-spending category Highest-spending month Pending expense amount Percentage of total expense pending Largest single transaction Most-used payment method Department with the highest pending expense Use short executive sentences. Example format: Marketing represents the largest department-level expense concentration. Advertising is the largest expense category. ₹X remains pending approval, representing X% of total recorded expense. Only use numbers calculated from the actual workbook. 13. FILTERS / SLICERS Add interactive Excel slicers wherever supported. Create slicers for: Department Category Payment Method Approval Status Also add a Date Timeline if supported. All dashboard charts, KPI calculations, and tables should respond to the slicers. If slicers cannot control a particular chart because of Excel limitations, use PivotTables/PivotCharts or formulas so that the dashboard remains interactive. 14. DYNAMIC CALCULATIONS Do NOT hard-code dashboard numbers. Use Excel Tables, PivotTables, PivotCharts, SUMIFS, COUNTIFS, dynamic formulas, or other appropriate Excel functionality. The dashboard must automatically update when new transactions are added to: tblExpenses For example, if a new transaction is added: Total Expense should update Transaction count should update Average Expense should update Pending Expense should update Monthly trend should update Department chart should update Category chart should update Payment method analysis should update Approval analysis should update 15. CONDITIONAL FORMATTING Use premium conditional formatting. For Amount: Low values → subtle neutral treatment Medium values → amber High values → red/pink accent For Approval Status: Approved → green Pending → amber Rejected → red Do not use overly saturated colors. 16. NUMBER FORMATTING Use Indian currency formatting. For detailed tables: ₹#,##0 For large KPI values, use compact notation when appropriate: ₹26.46 L For percentages: 0.0% For dates: dd-mmm-yyyy Do not display unnecessary decimal places. 17. DASHBOARD LAYOUT Use this approximate layout: HR EXPENSE DASHBOARD Executive Expense Analytics | January–July 2024 [ TOTAL EXPENSE ] [ TRANSACTIONS ] [ AVG EXPENSE ] [ PENDING EXPENSE ] [ Monthly Expense Trend ] [ Expense by Department ] [ Expense by Category ] [ Approval Status ] [ Payment Method ] [ Top 5 Expenses ] [ Management Insights ] Keep adequate spacing between sections. Do not overcrowd the dashboard. 18. PROFESSIONAL UX The dashboard should have: Clear visual hierarchy Consistent typography Consistent spacing Aligned cards Minimal gridlines Dark background High readability Clear chart titles Short labels No unnecessary legends No unnecessary decoration Hide normal Excel gridlines on the Dashboard sheet. Keep the Dashboard sheet visually clean. 19. ANALYSIS SHEET Create a supporting sheet called: Dashboard_Data Use it for calculations and chart sources. Create structured analysis tables for: Monthly Expense Department Expense Category Expense Approval Status Payment Method Top 5 Transactions Do not clutter the main Dashboard with calculation formulas. 20. DATA QUALITY Before finishing: Check for: Blank dates Blank departments Blank categories Blank amounts Invalid amounts Duplicate transactions Invalid approval statuses Invalid payment methods Do not silently delete records. If an issue exists, identify it in a small: Data Quality Notes section. 21. IMPORTANT DATA INTERPRETATION The supplied dataset contains July data that may represent only a partial month. Therefore: DO NOT describe July as the lowest full-month expense. Instead state: July is a partial-month period in the supplied dataset. Only compare complete months when making monthly performance observations. 22. FINAL QUALITY CHECK Before completing the dashboard, verify: ✓ All dashboard numbers come from the actual source data. ✓ No fake data has been introduced. ✓ All charts use the correct fields. ✓ Currency is INR. ✓ KPI calculations are correct. ✓ Monthly trend uses actual dates. ✓ Department totals reconcile to total expense. ✓ Category totals reconcile to total expense. ✓ Approval-status totals reconcile to total expense. ✓ Payment-method totals reconcile to total expense. ✓ Dashboard responds to filters/slicers. ✓ New rows added to the source table can flow into the dashboard. ✓ No chart is unnecessarily duplicated. ✓ Dashboard remains readable at normal Excel zoom. ✓ Dashboard has a premium dark executive appearance. 23. FINAL DESIGN STANDARD The finished dashboard should look like something that could be presented to: CFO HR Director Finance Director CEO Executive Management It should resemble a premium modern analytics product rather than a standard Excel spreadsheet. Prioritize: Clarity → Decision-making → Interactivity → Visual polish Do not sacrifice readability for decoration. Build the dashboard directly in this workbook using the existing HR expense data.