# DFC.xlsx - Logic & Formula Analysis

## Overview
**DFC (Daily Food Cost)** - Daily food cost tracking and analysis system with automatic calculations, benchmarking, and variance analysis.

---

## Column Structure & Headers

### Main Headers (Row 4):
- **E**: DATE
- **F**: CASH SALE NET
- **G**: CATERING BTC SALE DETAILS
- **H**: (Credit/Other Sale)
- **I**: TOTAL FOOD SALE (CASH + CREDIT)
- **J**: HOUSEKEEPING / LUMPSUM
- **K**: LAUNDRY
- **L**: TOTAL SALE
- **M**: (Food Cost Component 1)
- **N**: (Food Cost Component 2)
- **O**: TOTAL FOOD COST
- **P**: FOOD COST %
- **Q**: (Additional Cost)
- **R**: MANPOWER COST %
- **S**: (Manpower Days/Units)
- **T**: MANPOWER COST
- **U**: CYLINDER COST
- **V**: MANPOWER COST PER SALE
- **W**: CYLINDER COST PER SALE
- **X**: (Cylinder Units/Quantity)
- **Y**: TOTAL CYLINDER COST
- **Z**: CYLINDER RATE
- **AA**: CYLINDER COST %
- **AB**: EXPECTED FOOD COST (Based on Benchmark)
- **AC**: VARIATION (Actual vs Expected)
- **AF**: BENCHMARK RATE
- **AG**: TOTAL COST

---

## Key Formulas & Logic

### 1. **Total Food Sale (Column I)**
```
Formula: =F7+G7+H7
Logic: Cash Sale Net + Catering BTC Sale + Credit Sale
```

### 2. **Total Sale (Column L)**
```
Formula: =I7+J7+K7
Logic: Total Food Sale + Housekeeping + Laundry
```

### 3. **Total Food Cost (Column O)**
```
Formula: =M7+N7
Logic: Food Cost Component 1 + Food Cost Component 2
```

### 4. **Food Cost Percentage (Column P)**
```
Formula: =(M7+N7)/I7*(100)%
Logic: (Total Food Cost / Total Food Sale) × 100
Purpose: Shows food cost as percentage of food sales
```

### 5. **Manpower Cost Percentage (Column R)**
```
Formula (Row 7): =(J7+K7)/L7*(100)%
Formula (Row 8+): =Q8/(F8+G8)
Logic: 
  - Row 7: (Housekeeping + Laundry) / Total Sale × 100
  - Row 8+: Additional Cost / (Cash Sale + Catering Sale) × 100
```

### 6. **Manpower Cost (Column T)**
```
Formula: =11000/26*S7
Logic: (11000 / 26) × Manpower Days/Units
Purpose: Calculates daily manpower cost based on monthly salary (11000) divided by 26 working days
```

### 7. **Manpower Cost Per Sale (Column V)**
```
Formula: =T7/L7
Logic: Manpower Cost / Total Sale
Purpose: Shows manpower cost efficiency per unit of sale
```

### 8. **Cylinder Cost Per Sale (Column W)**
```
Formula: =U7/L7
Logic: Cylinder Cost / Total Sale
Purpose: Shows cylinder cost efficiency per unit of sale
```

### 9. **Total Cylinder Cost (Column Y)**
```
Formula: =Z7*X7
Logic: Cylinder Rate × Cylinder Units/Quantity
```

### 10. **Cylinder Rate (Column Z)**
```
Formula (Row 7): =AA4
Formula (Row 8+): =Z7 (copies from previous row)
Logic: Gets rate from cell AA4 (master rate) or carries forward from previous row
```

### 11. **Cylinder Cost Percentage (Column AA)**
```
Formula: =Y7/L7
Logic: Total Cylinder Cost / Total Sale
Purpose: Shows cylinder cost as percentage of total sales
```

### 12. **Expected Food Cost (Column AB) - Benchmark Calculation**
```
Formula: =N7*AF7
Logic: Food Cost Component 2 × Benchmark Rate
Purpose: Calculates expected food cost based on benchmark percentage
```

### 13. **Variation (Column AC) - Actual vs Expected**
```
Formula: =AB7-(M7+N7+Q7)
Logic: Expected Food Cost - (Actual Food Cost Component 1 + Component 2 + Additional Cost)
Purpose: 
  - Positive value = Over budget (spent more than expected)
  - Negative value = Under budget (spent less than expected)
```

### 14. **Benchmark Rate (Column AF)**
```
Formula (Row 7): =AB5
Formula (Row 8+): =AF7 (copies from previous row)
Logic: Gets benchmark rate from cell AB5 (master benchmark) or carries forward
```

### 15. **Total Cost (Column AG)**
```
Formula: =O7+Q7
Logic: Total Food Cost + Additional Cost
```

---

## Key Business Logic

### 1. **Food Cost Percentage Calculation**
- **Purpose**: Track food cost efficiency
- **Formula**: `(Total Food Cost / Total Food Sale) × 100`
- **Benchmark**: Typically compared against a benchmark (e.g., 30%)
- **Analysis**: Lower percentage = better cost control

### 2. **Manpower Cost Calculation**
- **Monthly Salary**: ₹11,000
- **Working Days**: 26 days per month
- **Daily Rate**: ₹11,000 ÷ 26 = ₹423.08 per day
- **Formula**: `Daily Rate × Number of Days/Units`

### 3. **Variation Analysis**
- **Expected Cost**: Based on benchmark rate × food cost component
- **Actual Cost**: Sum of all food cost components
- **Variation**: Difference between expected and actual
- **Positive Variation**: Over budget (needs attention)
- **Negative Variation**: Under budget (good performance)

### 4. **Cost Per Sale Metrics**
- **Manpower Cost Per Sale**: `Manpower Cost / Total Sale`
- **Cylinder Cost Per Sale**: `Cylinder Cost / Total Sale`
- **Purpose**: Efficiency indicators - lower is better

### 5. **Cylinder Cost Calculation**
- **Rate Source**: Master rate in cell AA4
- **Calculation**: `Rate × Quantity`
- **Percentage**: `Total Cylinder Cost / Total Sale × 100`

---

## Data Flow

1. **Input Data**:
   - Date (Column E)
   - Cash Sale Net (F)
   - Catering BTC Sale (G)
   - Credit Sale (H)
   - Food Cost Components (M, N)
   - Housekeeping (J)
   - Laundry (K)
   - Manpower Days (S)
   - Cylinder Quantity (X)
   - Additional Costs (Q)

2. **Calculated Totals**:
   - Total Food Sale (I) = F + G + H
   - Total Sale (L) = I + J + K
   - Total Food Cost (O) = M + N

3. **Percentage Calculations**:
   - Food Cost % (P) = (O / I) × 100
   - Manpower Cost % (R) = varies by row
   - Cylinder Cost % (AA) = (Y / L) × 100

4. **Cost Analysis**:
   - Manpower Cost (T) = (11000/26) × S
   - Cylinder Cost (Y) = Z × X
   - Expected Cost (AB) = N × AF
   - Variation (AC) = AB - (M + N + Q)

5. **Efficiency Metrics**:
   - Manpower Cost Per Sale (V) = T / L
   - Cylinder Cost Per Sale (W) = U / L

---

## Key Features

1. **Automatic Calculations**: All formulas auto-calculate when input data changes
2. **Benchmark Comparison**: Compares actual costs against benchmark rates
3. **Variance Tracking**: Shows positive/negative variations from expected costs
4. **Cost Efficiency Metrics**: Per-sale cost calculations for analysis
5. **Daily Tracking**: Date-wise entries for daily food cost monitoring
6. **Master Data**: Benchmark rates and cylinder rates stored in fixed cells (AA4, AB5)

---

## Important Notes

1. **Row 7** uses different formulas for some columns (e.g., Manpower Cost %)
2. **Master Rates**: 
   - Cylinder Rate: Cell AA4
   - Benchmark Rate: Cell AB5
3. **Carry Forward**: Some values (Z, AF) copy from previous row or master cell
4. **Percentage Format**: All percentage columns use `*(100)%` formula
5. **Total Cost**: Includes both food cost and additional costs

---

## Summary

DFC.xlsx is a comprehensive daily food cost tracking system that:
- Calculates total sales (food + housekeeping + laundry)
- Tracks food costs and calculates percentages
- Monitors manpower and cylinder costs
- Compares actual costs against benchmarks
- Shows variations (over/under budget)
- Provides efficiency metrics (cost per sale)
- Enables daily cost analysis and control

