Goals and OKR Spreadsheet with Progress Dashboard
ESCOPO
This prompt turns your real planning data into a complete technical specification for building a professional goals and OKR spreadsheet in Microsoft Excel. It guides the creation of tabs, structured tables, formulas, indicators, traffic-light statuses, charts, and an automatically updated executive dashboard.
Ideal for leaders, PMOs, HR, strategy teams, sales managers, and analysts who need to track corporate objectives, key results, individual goals, and initiatives in monthly, quarterly, or annual cycles. The result is practical: copyable instructions you can apply directly in Excel, including optional VBA and a reporting slide outline.
Conteudo
Prompt principal
Act as a senior expert in corporate Microsoft Excel, performance management, OKRs, indicators, and executive dashboards. Based on the data and context I will provide, design a robust spreadsheet solution for goals and OKR management with visual progress tracking, ready to be implemented directly in Microsoft Excel. Before developing the solution, analyze the information below. If there are gaps that prevent a relevant decision, ask at most 8 objective, prioritized questions. If the data is already sufficient, do not ask questions: make reasonable assumptions, state them briefly, and proceed. DATA AND CONTEXT TO BE PASTED BY THE USER: - Company, area, responsible audience, and confidentiality level: - Tracking period (year, quarter, month, or cycle): - Estimated number of objectives, KRs, teams, and owners: - Strategic objectives and their respective Key Results, with unit of measure: - Initial value, target, current value, and update frequency for each KR: - Desired direction of the indicator (higher is better, lower is better, or ideal range): - Weights by objective/KR, if any: - Initiatives, projects, milestones, risks, and relevant dependencies: - Current status rules, attainment bands, and corporate colors: - Excel version and usage environment (Windows desktop, Mac, or Microsoft 365): - Need for VBA, Power Query, sheet protection, collaboration, or export to PowerPoint: - Sample data, existing spreadsheets, or columns already in use: Follow this working method exactly: 1. Diagnosis and logical design: define the appropriate granularity (one record per KR per period, or another justified approach), the relationship between Objectives, KRs, initiatives, and owners, and the calculation assumptions. Explicitly differentiate cumulative metrics, percentages, binary milestones, and indicators where reduction represents improvement. 2. File architecture: propose a scalable tab structure. As a reference, evaluate the tabs "Instructions", "Lists", "OKRs", "Updates", "Dashboard", "Action Plan" and "Auxiliary_Base". For each recommended tab, specify purpose, users, columns, data type, required fields, validations, and whether it should be converted into an Excel Table. Name the tables and columns using consistent technical patterns, without spaces when that makes formulas and VBA easier. 3. Excel implementation: provide ready-to-paste formulas, using structured references from Excel Tables and functions compatible with the reported version. Include formulas for progress percentage, weighted progress, attainment percentage, status, trend versus prior period, days remaining, overdue update alert, and consolidation by objective, area, and owner. Handle errors, blank values, zero target, inverted targets, and 0% to 100% limits. When there is an alternative between modern and legacy functions, provide both and identify compatibility. 4. User experience and controls: detail drop-down lists, data validation, traffic-light conditional formatting, data bars, icons, locked versus editable fields, freezing panes, filters, slicers, and recommended protection. Define an objective legend for statuses, for example: critical, attention, on track, completed, and no update. 5. Executive dashboard: describe the layout cell by cell or by blocks, including main KPIs, recommended charts, slicers, timeline, and filters. The dashboard must be readable in under two minutes: weighted average percentage, number of KRs by status, progress by objective, time trend, owners with the highest risk, and upcoming milestones. Specify the source of each visual, the chart type, and the required setup. Avoid decorative or hard-to-interpret charts. 6. Optional automation: if VBA is compatible and useful, provide complete, commented, and safe VBA code for at least one relevant automation, such as registering an update with a date/time stamp, refreshing the dashboard, clearing a form, or exporting the panel to PDF. Specify exactly where to insert the code, how to save as .xlsm, and limitations. If VBA is not appropriate, propose an alternative with Power Query, Office Scripts, or native features. 7. Executive report: create a structure of 3 to 5 slides for PowerPoint based on the dashboard, with the title of each slide, key message, indicators to display, and suggested narrative for the follow-up meeting. EXACT RESPONSE FORMAT: A. Assumptions and diagnosis; B. Tab structure in a Markdown table; C. Data dictionary and input rules; D. Ready-to-copy formulas, indicating destination cell/column and brief explanation; E. Visual settings, validations, and conditional formatting; F. Detailed dashboard specification; G. Optional VBA/automation, with code when applicable; H. Slide outline; I. Final implementation and testing checklist. Maintain a corporate, auditable, and scalable approach. Do not deliver generic recommendations: turn each decision into actionable instructions in Excel. Use Brazilian Portuguese, clear naming, formulas in the language of the Excel version provided by the user, and the appropriate argument separator. Do not invent data; when necessary, flag assumptions and use examples clearly identified as fictional.
