Scribd
Upload
8 views5 pages

5-Year Financial Model for ABS Ltd

ABS Ltd plans to launch a new product with an initial debt investment of $500,000 and forecasts sales revenue of $1 million in the first year, growing at 20% annually for two years and 35% for the last two years. The financial model includes variable costs at 8% of revenue and fixed costs of $2,000 per year, with varying tax rates over the five years. The net earnings for each year are calculated, highlighting the highest net earning year in green.

Uploaded by

chinnuafsana
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
8 views5 pages

5-Year Financial Model for ABS Ltd

ABS Ltd plans to launch a new product with an initial debt investment of $500,000 and forecasts sales revenue of $1 million in the first year, growing at 20% annually for two years and 35% for the last two years. The financial model includes variable costs at 8% of revenue and fixed costs of $2,000 per year, with varying tax rates over the five years. The net earnings for each year are calculated, highlighting the highest net earning year in green.

Uploaded by

chinnuafsana
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
You are on page 1/ 5

Imagine you're tasked with creating a financial model for a start-up company (ABS Ltd) that plans to launch

a new
product in the market. The company expects an initial investment in the form debt of $500,000 for product
development and marketing. They forecast sales revenue of $1 million in the first year, with a 20% growth rate
annually for the next 2 years and 35% for the last 2 years. However, they anticipate variable costs to be 8% of
total revenue for all years, and fixed costs to be $2000 per year. Below are the expenses:Interest expenses are
8% for all years and Tax will be 10% of EBT (Earnings before taxes) for the first 2 years, 15% for the 3rd and 4th
years, and 20% for the last year. Calculate the net earnings for all five years. Prepare a financial model for all five
years highlighting the highest net earning year with green color. Note: The amount mentioned in the Excel sheet
should be in dollars.
Expenses Year 1 Year 2 Year 3 Year 4 Year 5
Advertising
and 800 900 1211 1234 1256
promotion
Depreciation
and 1000 1000 1000 1000 1000
Amortization
Insurance 1500 1500 1500 1500 1500
Rent 2000 2100 2200 2300 2400
Wages and
30000 32000 35000 38000 40000
salaries
Travel 200 200 200 200 200
Other utilities 250 300 350 400 450
Maintenance 650 740 790 850 900
Expenses Year 1 Year 2 Year 3 Year 4 Year 5

Advertising
800 900 1211 1234 1256
and promotion

Depreciation
and 1000 1000 1000 1000 1000
Amortization
Insurance 1500 1500 1500 1500 1500
Rent 2000 2100 2200 2300 2400
Wages and
30000 32000 35000 38000 40000
salaries
Travel 200 200 200 200 200
Other utilities 250 300 350 400 450
Maintenance 650 740 790 850 900
DETAILS 2020A 2021E 2022E 2023E
REVENUE ₹ 1,000,000 ₹ 1,200,000 ₹ 1,440,000 ₹ 1,944,000
FIXED COST ₹ 2,000 ₹ 2,000 ₹ 2,000 ₹ 2,000
VARIABLE COST ₹ 80,000 ₹ 96,000 ₹ 115,200 ₹ 155,520

Advertising
₹ 800 ₹ 900 ₹ 1,211 ₹ 1,234
and promotion

Depreciation
and ₹ 1,000 ₹ 1,000 ₹ 1,000 ₹ 1,000
Amortization
Insurance ₹ 1,500 ₹ 1,500 ₹ 1,500 ₹ 1,500
Rent ₹ 2,000 ₹ 2,100 ₹ 2,200 ₹ 2,300
Wages and
₹ 30,000 ₹ 32,000 ₹ 35,000 ₹ 38,000
salaries
Travel ₹ 200 ₹ 200 ₹ 200 ₹ 200
Other utilities ₹ 250 ₹ 300 ₹ 350 ₹ 400
Maintenance ₹ 650 ₹ 740 ₹ 790 ₹ 850
TOTAL EXP ₹ 118,400 ₹ 136,740 ₹ 159,451 ₹ 203,004
EBIT ₹ 881,600 ₹ 1,063,260 ₹ 1,280,549 ₹ 1,740,996
INT ₹ 40,000 ₹ 40,000 ₹ 40,000 ₹ 40,000
EBT ₹ 841,600 ₹ 1,023,260 ₹ 1,240,549 ₹ 1,700,996
TAX 10% 10% 15% 15%
TAX IN RS ₹ 84,160 ₹ 102,326 ₹ 186,082 ₹ 255,149
EAT ₹ 757,440 ₹ 920,934 ₹ 1,054,467 ₹ 1,445,847
2024E
₹ 2,624,400
₹ 2,000
₹ 209,952

₹ 1,256

₹ 1,000

₹ 1,500
₹ 2,400
₹ 40,000
₹ 200
₹ 450
₹ 900
₹ 259,658
₹ 2,364,742
₹ 40,000
₹ 2,324,742
20%
₹ 464,948
₹ 1,859,794

You might also like