Campaign Feasibility Analysis
Overview
This Excel spreadsheet is designed to help analyze the feasibility of a campaign offering 5% cashback
for supermarket purchases to Birbank Cashback credit cardholders.
By inputting certain assumptions and utilizing provided formulas,
users can evaluate the potential net profit/loss and return on investment (ROI) of the campaign.
Assumptions
1. Average spending per Birbank Cashback credit cardholder at supermarkets: $200
2. Percentage of cardholders expected to utilize the cashback offer: 50%
3. Cost of the cashback program: $10,000
Calculations
- Projected increase in supermarket spending due to the cashback offer = Average spending * Percentage of cardholders * Cashback rate
- Total cashback expenses = Projected increase in spending * Cashback rate
- Net profit/loss = Total cashback expenses - Cost of the cashback program
Financial Analysis
- ROI = (Net profit / Cost of the cashback program) * 100
Excel Spreadsheet Setup
| Cardholder ID | Supermarket Spending | Cashback Earned | Total Cashback Expenses | Net Profit/Loss |
|---------------|----------------------|-----------------|-------------------------|-----------------|
| 1 | $200 | $10 | =C2 * 5% | =D2 - $10,000 |
| 2 | $250 | $12.50 | =C3 * 5% | =D3 - $10,000 |
| ... | ... | ... | ... | ... |
| Totals | =SUM(B2:B1000) | =SUM(C2:C1000) | =SUM(D2:D1000) | =E2 - $10,000 |
- Utilize the provided table structure with columns for Cardholder ID, Supermarket Spending, Cashback Earned, Total Cashback Expenses, and Net Profit/Loss.
- Adjust the number of rows based on the number of cardholders in your data.
- Use the provided formulas to calculate cashback earned, total cashback expenses, and net profit/loss for each cardholder.
- Utilize the SUM function to calculate totals for all cardholders.
Example
In the provided example:
- The spreadsheet assumes 1,000 cardholders. Adjust this number based on your actual data.
- Formulas are provided for the first cardholder. Repeat these formulas for each cardholder and adjust the ranges accordingly.
- The setup allows for easy adjustment of assumptions to see the impact on net profit/loss and ROI.
Additional Considerations
- You can enhance the analysis by incorporating additional factors such as marketing costs,
potential revenue increase from increased card usage, etc.,
depending on the availability of data and specific requirements.
By using this Excel spreadsheet, you can efficiently analyze the feasibility of the campaign offering 5% cashback for
supermarket purchases and make informed decisions based on the calculated net profit/loss and ROI.
ismat-samadov / business_analytics_interview Goto Github PK
View Code? Open in Web Editor NEWbusiness analytics interview