Title: Build an Automated Excel Sheet for Life Insurance Invoice Reconciliation (Bank Use)
Budget: $10 – $30 USD
Project Description:
I work at a bank. Every month, I need to pay a Life Insurance company using money from multiple bank accounts. Each account belongs to a specific Loan Type. I need a clean, automated Excel file that handles all the calculations for me.
Our Loan Types (5 types):
8% Type
Low
Pro Mortgage
Staff
UnSecured
Each Loan Type has one or more bank accounts linked to it.
How the process works — step by step:
Step 1 — We receive invoices
At the start of each month, the Life Insurance company sends us invoices.
Each invoice belongs to one Loan Type.
Example:
Loan TypeInvoice Amount8% Type100,000 EGPLow250,000 EGPPro Mortgage180,000 EGPStaff60,000 EGPUnSecured320,000 EGPTotal910,000 EGP
Step 2 — We check account balances
Each Loan Type has its own bank account(s). We check how much money is available.
Example:
Loan TypeTotal Balance in Accounts8% Type120,000 EGPLow200,000 EGPPro Mortgage190,000 EGPStaff40,000 EGPUnSecured280,000 EGPTotal830,000 EGP
Step 3 — Compare invoice vs balance per Loan Type
Loan TypeBalanceInvoiceSurplus / Deficit8% Type120,000100,000+20,000 surplusLow200,000250,000-50,000 deficitPro Mortgage190,000180,000+10,000 surplusStaff40,00060,000-20,000 deficitUnSecured280,000320,000-40,000 deficit
Step 4 — Use surpluses to cover deficits
Total surplus = 20,000 + 10,000 = 30,000 EGP
Total deficit = 50,000 + 20,000 + 40,000 = 110,000 EGP
After using all surpluses → remaining deficit = 110,000 − 30,000 = 80,000 EGP
Step 5 — Draw the remaining amount from the Cover Account
We have a special Cover Account with no fixed limit. The Excel sheet must calculate exactly how much to withdraw from it.
In this example → withdraw 80,000 EGP from Cover Account.
What I already have:
I have an existing Excel file with these tables:
BalancesTable — I manually enter each account's balance and its Loan Type
InvoicesTable — I enter the invoices received per Loan Type
PivotTable1 — shows accounts grouped by Loan Type with their balances
PivotInvoicesTable — shows total invoice per Loan Type
PivotTable3 — shows balance vs invoice vs difference per Loan Type
I will share this file with the selected freelancer.
What I need you to build:
1. Automated calculation logic:
Each Loan Type uses its own account balance first to pay its invoice
Surplus from one Loan Type automatically helps cover the deficit of another
After all surpluses are used, the sheet shows exactly how much to withdraw from the Cover Account
2. Clean summary section showing:
Balance, Invoice, Surplus/Deficit per Loan Type
How much each surplus covered from other deficits
Final amount needed from Cover Account
3. Monthly ready-to-use template:
I only enter balances and invoices each month
Everything else is calculated automatically
No manual calculations needed
Requirements:
- Excel (.xlsx) only — no Google Sheets
- Simple and clean design — easy for anyone to open and understand
- Must work correctly if a Loan Type has more than one bank account
- Flexible enough to add more Loan Types in the future
I work at a bank. Every month, I need to pay a Life Insurance company using money from multiple bank accounts. Each account belongs to a specific Loan Type. I need a clean, automated Excel file that handles all the calculations for me.
Our Loan Types (5 types):
8% Type
Low
Pro Mortgage
Staff
UnSecured
Each Loan Type has one or more bank accounts linked to it.
How the process works — step by step:
Step 1 — We receive invoices
At the start of each month, the Life Insurance company sends us invoices.
Each invoice belongs to one Loan Type.
Example:
Loan TypeInvoice Amount8% Type100,000 EGPLow250,000 EGPPro Mortgage180,000 EGPStaff60,000 EGPUnSecured320,000 EGPTotal910,000 EGP
Step 2 — We check account balances
Each Loan Type has its own bank account(s). We check how much money is available.
Example:
Loan TypeTotal Balance in Accounts8% Type120,000 EGPLow200,000 EGPPro Mortgage190,000 EGPStaff40,000 EGPUnSecured280,000 EGPTotal830,000 EGP
Step 3 — Compare invoice vs balance per Loan Type
Loan TypeBalanceInvoiceSurplus / Deficit8% Type120,000100,000+20,000 surplusLow200,000250,000-50,000 deficitPro Mortgage190,000180,000+10,000 surplusStaff40,00060,000-20,000 deficitUnSecured280,000320,000-40,000 deficit
Step 4 — Use surpluses to cover deficits
Total surplus = 20,000 + 10,000 = 30,000 EGP
Total deficit = 50,000 + 20,000 + 40,000 = 110,000 EGP
After using all surpluses → remaining deficit = 110,000 − 30,000 = 80,000 EGP
Step 5 — Draw the remaining amount from the Cover Account
We have a special Cover Account with no fixed limit. The Excel sheet must calculate exactly how much to withdraw from it.
In this example → withdraw 80,000 EGP from Cover Account.
What I already have:
I have an existing Excel file with these tables:
BalancesTable — I manually enter each account's balance and its Loan Type
InvoicesTable — I enter the invoices received per Loan Type
PivotTable1 — shows accounts grouped by Loan Type with their balances
PivotInvoicesTable — shows total invoice per Loan Type
PivotTable3 — shows balance vs invoice vs difference per Loan Type
I will share this file with the selected freelancer.
What I need you to build:
1. Automated calculation logic:
Each Loan Type uses its own account balance first to pay its invoice
Surplus from one Loan Type automatically helps cover the deficit of another
After all surpluses are used, the sheet shows exactly how much to withdraw from the Cover Account
2. Clean summary section showing:
Balance, Invoice, Surplus/Deficit per Loan Type
How much each surplus covered from other deficits
Final amount needed from Cover Account
3. Monthly ready-to-use template:
I only enter balances and invoices each month
Everything else is calculated automatically
No manual calculations needed
Requirements:
- Excel (.xlsx) only — no Google Sheets
- Simple and clean design — easy for anyone to open and understand
- Must work correctly if a Loan Type has more than one bank account
- Flexible enough to add more Loan Types in the future