🏡 GuestVilla Monthly Consumption Report

A comprehensive Excel-based consumption tracking system for guest villas and hospitality facilities managing food, beverages, and supplies with automated monthly cleanup and cost analysis.

📋 What This Does

Solves the problem: How to track and manage all guest villa expenses (groceries, beverages, hookah supplies) with automatic calculations, monthly reporting, and seamless month-to-month transitions for hospitality management.

Key Benefits:

• Track all guest villa consumption in one template
• Automatic calculations for stock, usage, and costs
• Monthly summary dashboard with charts
• Detailed purchase order tracking
• Automatic month cleanup with VBA macro
• Complete audit trail for finance teams
• Invoice & purchase tracking
• Usage amount calculations

📸 View the File

Access GuestVilla Monthly Consumption Report

Password: 123 ⚠️ IMPORTANT: Enable Macros for VBA functionality Click "Enable Macros" to activate "Clean Sheet & Next Month" button

---

📸 System Screenshots

Monthly Consumption Summary Dashboard

Complete monthly summary dashboard showing grocery, bottle, and hookah consumption with usage amounts, closing stock calculations, and monthly trends chart comparing consumption across quarters.

Grocery Purchase Order Tracking

Comprehensive grocery purchase tracking sheet for recording all daily grocery purchases with invoice details, shop information, units, rates, and automatic daily consumption tallying system.

Bottle & Alcoholic Beverages Consumption

Detailed bottle consumption tracking for beers, whisky, and other beverages with invoice-level tracking, daily usage recording, and automatic closing stock & amount calculations.

---

✨ Main Features

1. Summary Dashboard

• One-page overview of all villa consumption
• Three consumption categories:
  • Guesthouse Grocery Purchase
  • Guesthouse Bottle Consumption
  • Guesthouse Hookah Consumption
• Automatic calculations from all sheets
• Order, Stock, Usage & Closing Stock for each category
• Monthly usage chart showing trends
• Quick month change button

2. Grocery Purchase Tracking

• 155 line items for detailed tracking
• Columns for:
  • SR. No. & DC Invoice No.
  • Purchase Date (DD-MM-YYYY)
  • Type (Dry Goods, Spices, Vegetables, Fruits, Dairy, etc.)
  • Shop Name
  • Particulars (item name)
  • Units & Rate
  • Opening & Order quantities
  • Automatic daily consumption tracking
  • Total Stock, Usage Stock, Closing Stock
  • Usage Amount & Closing Amount
  • Order Amount (auto-calculated)

3. Bottle Consumption Tracking

• 24 line items for beverages
• Track all types:
  • Beer varieties
  • Whisky brands
  • Wine selections
  • Other spirits & liqueurs
• Columns include:
  • Invoice details & purchase dates
  • Shop name
  • Particulars
  • Units & Rate
  • Opening & Order quantities
  • Automatic daily usage calculation
  • Stock management (Total, Usage, Closing)
  • Amount calculations (Usage, Closing, Order)

4. Hookah Consumption Tracking

• 26 line items for hookah supplies
• Track:
  • Hookah tobacco varieties
  • Charcoal & accessories
  • Flavor essences
  • Maintenance supplies
• Same detailed tracking as beverages
• Automatic stock & cost calculations

5. VBA Macro - Clean Sheet & Next Month

• One-click monthly cleanup
• Automatically:
  • Clears all daily consumption data
  • Maintains opening stock as new month opening
  • Advances month (Jan → Feb → Mar, etc.)
  • Updates all sheet headers
  • Resets purchase order lines
• No manual data deletion needed
• Seamless month transitions

6. Automatic Calculations

• Opening Stock: Carried from previous month's closing
• Order: Manual entry for purchases made
• Total Stock: Opening + Order
• Usage Stock: Daily consumption tracking
• Closing Stock: Total Stock - Usage Stock
• Usage Amount: Usage × Rate
• Closing Amount: Closing Stock × Rate
• Order Amount: Order × Rate

7. Monthly Reporting

• Summary dashboard with all totals
• Monthly comparison with previous months
• Chart visualization of spending trends
• Usage breakdown by category
• Cost analysis for budget planning

8. Audit Trail

• Invoice number tracking for all purchases
• Purchase date recording
• Shop name documentation
• Complete daily log of consumption
• Amount tracking for financial audit

🚀 How to Use

Quick Start (First Time)

Step 1: Enable Macros (IMPORTANT!)

1. Download file from link above
2. Open in Microsoft Excel
3. Allow Macros when prompted:
   • Click "Enable Macros" or "Enable Content"
   • VBA functions are required for cleanup button
4. Ready to use!

Step 2: Enter Monthly Data

1. Go to "Grocery Purchase" sheet
2. Enter daily grocery purchases:
   • Invoice number
   • Purchase date (DD-MM-YYYY)
   • Shop name
   • Item particulars
   • Units & rate
3. Same for "Bottle Consu." and "Hukka Consu." sheets

Step 3: View Summary

1. Go to "Summary" sheet
2. See all calculations automatically
3. View monthly usage chart
4. Print or export for reports

Step 4: End of Month Cleanup

1. Click "Clean the Sheet & Nxt Month" button
2. System automatically:
   • Clears all daily data
   • Advances month (Jan → Feb)
   • Updates all headers
   • Ready for next month

Daily Workflow

DAILY (Morning/Evening):
├─ Staff records grocery purchases
│  └─ Date, shop, items, amounts
├─ Staff records bottle consumption
│  └─ Types, quantities, amounts
└─ Staff records hookah usage
   └─ Items, quantities, amounts

WEEKLY (Optional):
├─ Finance reviews Summary
└─ Tracks spending trends

MONTH-END:
├─ Final review of Summary
├─ Generate reports
├─ Email to admin/finance team
└─ Print for records

NEXT MONTH:
├─ Click "Clean Sheet & Next Month" button
└─ All data cleared, month advanced
   └─ Ready to start fresh

📊 What You'll See

Summary Dashboard Shows:

For Each Category (Grocery, Bottle, Hookah):

• Order: How many units ordered
• Total Stock: Opening + Order
• Usage Stock: Consumed during month
• Closing Stock: Remaining for next month
• Usage Amount: Cost of consumption
• Closing Amount: Value of remaining stock
• Order Amount: Cost of purchases

Example Monthly Report:

GUESTVILLA CONSUMPTION REPORT - JANUARY 2026

GUESTHOUSE GROCERY PURCHASE:
├─ Order: 0 units
├─ Total Stock: 0
├─ Usage Stock: 0
├─ Closing Stock: 0
├─ Usage Amount: ₹-
├─ Closing Amount: ₹-
└─ Order Amount: ₹-

GUESTHOUSE BOTTLE CONSUMPTION:
├─ Order: 0 units
├─ Total Stock: 0
├─ Usage Stock: 0
├─ Closing Stock: 0
├─ Usage Amount: ₹-
├─ Closing Amount: ₹-
└─ Order Amount: ₹-

GUESTHOUSE HOOKAH CONSUMPTION:
├─ Order: 0 units
├─ Total Stock: 0
├─ Usage Stock: 0
├─ Closing Stock: 0
├─ Usage Amount: ₹-
├─ Closing Amount: ₹-
└─ Order Amount: ₹-

MONTHLY USAGE TRENDS:
├─ Apr-25: ₹33,786.31 (Grocery) + ₹1,93,285 (Bottle) + ₹3,780 (Hookah) = ₹2,30,851.31
├─ May-25: ₹3,624.00 + ₹19,000.00 + ₹3,190.00 = ₹25,814.00
├─ Jun-25: ₹35,380.81 + ₹29,700.00 + ₹18,410.00 = ₹83,490.81
└─ ... (continuing through all months)

💡 Key Benefits

✅ All-in-One Tracking: Everything in one monthly template
✅ Automatic Calculations: No manual math needed
✅ Three Consumption Types: Grocery, Beverage, Hookah tracking
✅ Invoice-Level Details: Complete audit trail
✅ Easy Month Transition: One-click cleanup with VBA macro
✅ Automatic Stock Management: Opening → Usage → Closing
✅ Cost Analysis: Know exactly what guests consume
✅ VBA Macro Enabled: No manual data deletion
✅ Multi-Month Tracking: Compare spending trends
✅ Finance Ready: Complete reports for accounting

🔧 Customization

Add New Consumption Type:

1. Duplicate any consumption sheet
2. Rename for new category
3. Update Summary to reference new sheet
4. All calculations work automatically

Add New Item:

1. Go to relevant sheet
2. Enter new row with:
   • Item name
   • Shop/vendor
   • Units
   • Rate
3. Summary includes it automatically

Change Month Names:

• Dates are pulled from purchase date column
• Change any purchase date
• All headers update automatically

Multiple Villas:

1. Copy entire file
2. Rename for new villa
3. Start tracking separately
4. Or aggregate in master file

📈 Real-World Scenarios

Scenario 1: Cost Control for Events

Director plans event with 50 guests for 3 days
Uses GuestVilla to track all consumption
Grocery: ₹15,000 for meals
Beverages: ₹45,000 for drinks
Hookah: ₹3,500 for entertainment
Total: ₹63,500
Cost per guest: ₹1,270
Accurate for future event budgeting

Scenario 2: Monthly Expense Analysis

Finance reviews Summary sheet
Notices bottle consumption up 40% in June
Investigates: Found corporate event on Jun 15-18
Expected high spending for that period
Adjusts budget for similar events
Reduces baseline consumption analysis

Scenario 3: Vendor Performance Tracking

GM reviews all purchases by shop name
Finds Shop A prices higher than Shop B
Negotiates better rates with Shop A
Switches some purchases to Shop B
Saves ₹5,000+ monthly
Tracks savings in subsequent months

💼 Technical Skills Demonstrated

• Excel advanced template design
• VBA macro programming
• Automatic calculation formulas
• Month-to-month data management
• Multi-sheet data consolidation
• Conditional formatting
• Chart creation & visualization
• Hospitality management knowledge
• Inventory & stock tracking
• Cost analysis & reporting
• Data validation & protection

🎓 Training & Support

How to Use Macro Button:

Q: "Clean Sheet & Nxt Month" not working? A:

• Check macros are enabled
• File menu → Info → "Enable all content"
• Or Trust Center settings → Enable macros
• Click button at month-end

Q: How to enter purchase details? A: Go to Grocery/Bottle/Hookah sheet → Fill yellow cells only → All calculations automatic

Q: How to track daily consumption? A: Enter items purchased with invoice details → Daily totals calculate automatically → Summary updates instantly

Q: Can I add more items? A: Yes, each sheet has 155+ rows → Add new rows as needed → All calculations extend automatically

Q: How to send monthly reports? A: Go to Summary → Print or export to PDF → Email to finance/admin team → Archive for audit

📞 Contact

For customization or questions:

• GitHub
• Email: suubhamghadge@gmail.com
• LinkedIn

📄 License

MIT License - Free to use and modify

---

🌟 Project Stats

---

🏡 Track All Villa Consumption - Manage Costs - Generate Reports - Easy Month Transitions

⭐ If this helps your hospitality business, please star this repository!

💬 Questions? Open an issue and I'll help you get started!

Password: 123 | Enable Macros | VBA Enabled