# Template Quality Rubric

**Last Updated:** 2025-11-20

## Overview

This rubric defines quality standards for Excel templates generated by the Ordio template generator system. Templates are evaluated across multiple dimensions to ensure they meet high standards for usability, compliance, and branding.

## Quality Dimensions

### 1. Structure & Organization (25 points)

**Sheet Organization:**

- ✅ Clear sheet structure (5-7 sheets max) - 5 points
- ✅ Logical sheet order (Instructions → Overview → Data → Calculations → Reports) - 5 points
- ✅ Descriptive sheet names (German, max 31 chars) - 3 points
- ✅ Appropriate sheet types assigned - 2 points

**Cell Organization:**

- ✅ Clear data entry areas - 3 points
- ✅ Separated calculation areas - 3 points
- ✅ Consistent column widths - 2 points
- ✅ Proper spacing between sections - 2 points

**Score: \_\_/25**

---

### 2. Formulas & Calculations (20 points)

**Formula Quality:**

- ✅ Formulas documented - 3 points
- ✅ Named ranges used for complex formulas - 3 points
- ✅ Error handling (IFERROR/WENNFEHLER) implemented - 4 points
- ✅ No hard-coded values in formulas - 3 points
- ✅ Formulas reference formula library where applicable - 2 points

**Formula Correctness:**

- ✅ Formulas calculate correctly - 3 points
- ✅ Formulas use German locale (SUMME, WENN, etc.) - 2 points

**Score: \_\_/20**

---

### 3. Data Validation (15 points)

**Validation Rules:**

- ✅ Dropdown lists implemented where appropriate - 4 points
- ✅ Date ranges validated - 3 points
- ✅ Number ranges validated - 3 points
- ✅ German-specific validations (working hours, minimum wage) - 5 points

**User Experience:**

- ✅ Clear error messages (German) - 2 points
- ✅ Input prompts provided - 2 points

**Score: \_\_/15**

---

### 4. Styling & Branding (15 points)

**Ordio Branding:**

- ✅ Uses Ordio brand colors (#4D8EF3 primary) - 4 points
- ✅ Uses Ordio fonts (Inter, Gilroy-Bold) - 3 points
- ✅ Consistent styling throughout - 3 points
- ✅ Style presets used appropriately - 2 points

**Visual Design:**

- ✅ Proper column widths set - 2 points
- ✅ No header borders (cleaner look) - 1 point

**Score: \_\_/15**

---

### 5. Compliance & Legal (15 points)

**German HR Compliance:**

- ✅ ArbZG requirements validated (working hours, breaks, rest periods) - 6 points
- ✅ Minimum wage validation (€12.82/hour) - 3 points
- ✅ Break requirements validated (30min after 6h, 45min after 9h) - 3 points
- ✅ Rest period validation (11 hours between shifts) - 3 points

**Score: \_\_/15**

---

### 6. Conditional Formatting (5 points)

**Error Highlighting:**

- ✅ Errors highlighted (red background) - 2 points
- ✅ Warnings highlighted (yellow background) - 2 points
- ✅ Compliance violations highlighted - 1 point

**Score: \_\_/5**

---

### 7. User Experience (5 points)

**Documentation:**

- ✅ Instructions sheet included - 2 points
- ✅ Clear step-by-step instructions (German) - 2 points
- ✅ FAQ or troubleshooting section - 1 point

**Score: \_\_/5**

---

## Scoring Guide

**Total Score: 100 points**

### Quality Levels

- **Excellent (90-100 points):** Production-ready, exceeds standards
- **Good (75-89 points):** Production-ready, meets standards
- **Acceptable (60-74 points):** Needs minor improvements
- **Needs Work (40-59 points):** Requires significant improvements
- **Poor (<40 points):** Major revisions needed

## Review Checklist

### Pre-Generation Checklist

- [ ] Template definition follows JSON schema
- [ ] Metadata complete (name, category, version, description)
- [ ] Sheet structure planned and logical
- [ ] Formulas planned and documented
- [ ] Data validation rules defined
- [ ] Compliance requirements identified

### Post-Generation Checklist

- [ ] Template validates against schema
- [ ] Excel file opens without errors
- [ ] All formulas calculate correctly
- [ ] Data validation works as expected
- [ ] Conditional formatting displays correctly
- [ ] Styling consistent with Ordio branding
- [ ] Compliance validations work correctly
- [ ] Instructions sheet is clear and helpful
- [ ] Tested in Excel, Google Sheets, LibreOffice

### Quality Assurance Checklist

- [ ] No hard-coded values in formulas
- [ ] Error handling implemented for lookups
- [ ] German language used throughout
- [ ] Ordio branding colors used
- [ ] Column widths appropriate
- [ ] No header borders
- [ ] Compliance checks implemented
- [ ] Instructions are clear

## Common Issues & Solutions

### Issue: Hard-coded values in formulas

**Problem:**

```excel
=SUMME(A1:A10)*1.19  // Hard-coded VAT rate
```

**Solution:**

```excel
=SUMME(A1:A10)*(1+Mehrwertsteuer)  // Use named range
```

**Points Deducted:** -3 points

---

### Issue: Missing error handling

**Problem:**

```excel
=SVERWEIS(A2;Daten!A:B;2;FALSCH)  // Can return #N/A
```

**Solution:**

```excel
=WENNFEHLER(SVERWEIS(A2;Daten!A:B;2;FALSCH);"Nicht gefunden")
```

**Points Deducted:** -4 points

---

### Issue: Inconsistent styling

**Problem:** Different colors/fonts used across sheets

**Solution:** Use style presets from branding config consistently

**Points Deducted:** -3 points

---

### Issue: Missing compliance validation

**Problem:** No checks for ArbZG requirements or minimum wage

**Solution:** Add compliance formulas and conditional formatting

**Points Deducted:** -6 points

---

### Issue: Poor sheet organization

**Problem:** Too many sheets, unclear structure

**Solution:** Limit to 5-7 sheets, follow standard order

**Points Deducted:** -5 points

---

### Issue: Missing instructions

**Problem:** No instructions sheet or unclear instructions

**Solution:** Add clear instructions sheet with step-by-step guide

**Points Deducted:** -2 points

## Improvement Guide

### For Templates Scoring 60-74 (Acceptable)

**Priority Improvements:**

1. Add missing compliance validations
2. Implement error handling in formulas
3. Improve styling consistency
4. Add clear instructions

### For Templates Scoring 40-59 (Needs Work)

**Priority Improvements:**

1. Restructure sheets (reduce count, improve organization)
2. Replace hard-coded values with named ranges
3. Add comprehensive data validation
4. Implement all compliance checks
5. Improve branding consistency

### For Templates Scoring <40 (Poor)

**Major Revisions Needed:**

1. Complete redesign of sheet structure
2. Rewrite all formulas with proper error handling
3. Implement complete compliance system
4. Redesign styling to match Ordio branding
5. Add comprehensive documentation

## Template Review Process

1. **Initial Review:** Check schema compliance and basic structure
2. **Functional Review:** Test all formulas and validations
3. **Compliance Review:** Verify all German HR requirements
4. **Branding Review:** Check styling consistency
5. **User Testing:** Test with real users if possible
6. **Final Score:** Calculate total score and determine quality level

## References

- **Best Practices:** `docs/systems/excel-generator/EXCEL_BEST_PRACTICES.md`
- **Schema:** `v2/systems/excel-template-generator/data/template-definitions/schema.json`
- **Branding:** `v2/systems/excel-template-generator/config/template-branding.php`
- **Formulas:** `v2/systems/excel-template-generator/data/template-formulas/hr-formulas.json`