30$ Offline Excel Quotation Creation
Budget: $10 – $30 USD
PROJECT: Excel-Based Quotation System with Admin Control (MODA Windows & Doors)
We need to develop a quotation system using Microsoft Excel (offline), with structured pricing logic, admin-controlled pricing tables, and manual PDF export. The system must be easy for sales users to operate while maintaining full control of pricing by the admin.
1. USER ROLES & ACCESS CONTROL
There are two types of users:
Admin
Has full access to all sheets, including Pricing List and Customer Database. Can edit all pricing, unlock protected areas, and manage the system.
User (Sales)
Has access only to:
- Customer List (restricted editing)
- Quotation Sheet (data entry only)
User cannot:
- Access or modify Pricing List
- Change formulas or pricing logic
- Delete or edit customers without admin password
- Edit saved quotations without admin password
Excel protection must be implemented using sheet protection and password locking.
2. ADMIN PRICING LIST (PROTECTED SHEET)
A hidden or password-protected sheet accessible only by Admin.
Contains all pricing tables:
Designs Table
Fields:
- Design Name
- Category (Opening / Sliding / Bi-Folding / Fixed / Door)
- Price per m²
Materials Table
- EURO UPVC
- EURO ALUMINIUM
- SEMI EURO ALUMINIUM
- STANDARD ALUMINIUM
Each has fixed price per m²
Colors Table (linked to Material)
- Each color belongs to a material category
- Each color has its own price per m²
Glass Table
- Glass Type (e.g. 6mm clear, 8mm clear, 10mm green etc.)
- Price per m²
Screen Table
- Screen Type (Opening / Sliding / Accordion)
- Price per m²
Key Lock
- Fixed price (same for all doors)
Installation Table
- Installation price per m² per material
Delivery Rate
- Price per km (e.g. 30 THB/km)
3. CUSTOMER DATABASE (SHEET)
User can create New Customer with:
- First Name
- Last Name
- Address
- Google Maps link (optional)
- Phone Number
Rules:
- Customer records are permanent
- User cannot delete or modify existing customers without admin password
- Use Excel data validation/search (dropdown with search or filter)
4. NEW QUOTATION WORKFLOW
When starting a new quotation:
Auto-generated:
- Quotation Number (incremental, using formula or macro)
- Quotation Date (today’s date)
- Expire Date (Quotation Date + 15 days)
User selects:
- Customer (from dropdown/search)
- Salesperson Name (manual input)
- Remarks / Special Notes
Customer details are auto-filled from the database.
5. LINE ITEM STRUCTURE
Each quotation contains multiple line items (rows).
Each line includes:
- Design
- Material
- Color (dependent on Material)
- Glass
- Screen option:
- No Screen
- With Screen → then select type (based on Design category)
- Key Lock:
- Yes / No (only for doors)
- Width
- Height
- Quantity
6. PRICING LOGIC (PER LINE)
All pricing is based on m².
Final price per m² = Design + Material + Color + Glass + Screen (if selected)
Line m² = Width × Height
Unit Price per item = (Line m² × Final price per m²) + Key Lock (if selected)
Line Total = Unit Price × Quantity
Key Lock:
- Fixed price per unit
- Multiplied by Quantity
7. PROJECT TOTAL
Subtotal = Sum of all Line Totals
8. INSTALLATION (PROJECT LEVEL)
Installation is optional (Yes / No)
If Yes:
- System checks all materials used
- Finds highest installation rate per m²
- Applies it to total m² of the project
Installation = Total m² × Highest Installation Rate
9. DELIVERY (PROJECT LEVEL)
User inputs distance in km.
Rules:
- If distance ≤ 30 km → Delivery = 0
- If distance > 30 km → Delivery = Total km × Rate
(No subtraction of first 30 km)
10. VAT & FINAL TOTAL
Before VAT = Subtotal + Installation + Delivery
VAT = 7%
Grand Total = Before VAT + VAT
11. SAVE & PDF EXPORT
When quotation is complete:
- File is saved manually as Excel
- User exports quotation as PDF manually (File → Save as PDF)
Recommended:
- Standard print layout format for clean PDF output
- Predefined print area
- Professional quotation layout
12. EDIT CONTROL
- Saved quotations must be protected (locked cells)
- User cannot edit without admin password
- Admin can unlock sheet when needed
13. OUTPUT FORMAT
Quotation must include:
- Company details
- Customer details
- Quotation number
- Quotation date
- Expire date
- Salesperson name
- Item table
- Subtotal
- Installation
- Delivery
- VAT 7%
- Grand Total
- Remarks
No images required. Text-based professional layout only.
We need to develop a quotation system using Microsoft Excel (offline), with structured pricing logic, admin-controlled pricing tables, and manual PDF export. The system must be easy for sales users to operate while maintaining full control of pricing by the admin.
1. USER ROLES & ACCESS CONTROL
There are two types of users:
Admin
Has full access to all sheets, including Pricing List and Customer Database. Can edit all pricing, unlock protected areas, and manage the system.
User (Sales)
Has access only to:
- Customer List (restricted editing)
- Quotation Sheet (data entry only)
User cannot:
- Access or modify Pricing List
- Change formulas or pricing logic
- Delete or edit customers without admin password
- Edit saved quotations without admin password
Excel protection must be implemented using sheet protection and password locking.
2. ADMIN PRICING LIST (PROTECTED SHEET)
A hidden or password-protected sheet accessible only by Admin.
Contains all pricing tables:
Designs Table
Fields:
- Design Name
- Category (Opening / Sliding / Bi-Folding / Fixed / Door)
- Price per m²
Materials Table
- EURO UPVC
- EURO ALUMINIUM
- SEMI EURO ALUMINIUM
- STANDARD ALUMINIUM
Each has fixed price per m²
Colors Table (linked to Material)
- Each color belongs to a material category
- Each color has its own price per m²
Glass Table
- Glass Type (e.g. 6mm clear, 8mm clear, 10mm green etc.)
- Price per m²
Screen Table
- Screen Type (Opening / Sliding / Accordion)
- Price per m²
Key Lock
- Fixed price (same for all doors)
Installation Table
- Installation price per m² per material
Delivery Rate
- Price per km (e.g. 30 THB/km)
3. CUSTOMER DATABASE (SHEET)
User can create New Customer with:
- First Name
- Last Name
- Address
- Google Maps link (optional)
- Phone Number
Rules:
- Customer records are permanent
- User cannot delete or modify existing customers without admin password
- Use Excel data validation/search (dropdown with search or filter)
4. NEW QUOTATION WORKFLOW
When starting a new quotation:
Auto-generated:
- Quotation Number (incremental, using formula or macro)
- Quotation Date (today’s date)
- Expire Date (Quotation Date + 15 days)
User selects:
- Customer (from dropdown/search)
- Salesperson Name (manual input)
- Remarks / Special Notes
Customer details are auto-filled from the database.
5. LINE ITEM STRUCTURE
Each quotation contains multiple line items (rows).
Each line includes:
- Design
- Material
- Color (dependent on Material)
- Glass
- Screen option:
- No Screen
- With Screen → then select type (based on Design category)
- Key Lock:
- Yes / No (only for doors)
- Width
- Height
- Quantity
6. PRICING LOGIC (PER LINE)
All pricing is based on m².
Final price per m² = Design + Material + Color + Glass + Screen (if selected)
Line m² = Width × Height
Unit Price per item = (Line m² × Final price per m²) + Key Lock (if selected)
Line Total = Unit Price × Quantity
Key Lock:
- Fixed price per unit
- Multiplied by Quantity
7. PROJECT TOTAL
Subtotal = Sum of all Line Totals
8. INSTALLATION (PROJECT LEVEL)
Installation is optional (Yes / No)
If Yes:
- System checks all materials used
- Finds highest installation rate per m²
- Applies it to total m² of the project
Installation = Total m² × Highest Installation Rate
9. DELIVERY (PROJECT LEVEL)
User inputs distance in km.
Rules:
- If distance ≤ 30 km → Delivery = 0
- If distance > 30 km → Delivery = Total km × Rate
(No subtraction of first 30 km)
10. VAT & FINAL TOTAL
Before VAT = Subtotal + Installation + Delivery
VAT = 7%
Grand Total = Before VAT + VAT
11. SAVE & PDF EXPORT
When quotation is complete:
- File is saved manually as Excel
- User exports quotation as PDF manually (File → Save as PDF)
Recommended:
- Standard print layout format for clean PDF output
- Predefined print area
- Professional quotation layout
12. EDIT CONTROL
- Saved quotations must be protected (locked cells)
- User cannot edit without admin password
- Admin can unlock sheet when needed
13. OUTPUT FORMAT
Quotation must include:
- Company details
- Customer details
- Quotation number
- Quotation date
- Expire date
- Salesperson name
- Item table
- Subtotal
- Installation
- Delivery
- VAT 7%
- Grand Total
- Remarks
No images required. Text-based professional layout only.