Business Finance:Budget and Profit and Loss Statement
The instructions for the assignment is as follows (USE the attached Excel Template to complete this assignment):
Your company is thinking about acquiring another corporation. You have two choices – the cost of each choice is 250,000. You cannot spend more than that, so
acquiring both corporations is not an option, The following is your critical data:
Corp. A:
Revenues = 100K in year one, increasing by 10% each year.
Expenses = 20K in year one, increasing by 15% each year.
Depreciation Expense = 5K each year.
Tax Rate = 25%
Discount Rate = 10%
Corp. B:
Revenues = 150K in year one, increasing by 8% each year.
Expenses = 60K in year one, increasing by 10% each year.
Depreciation Expense = 10K each year.
Tax Rate = 25%
Discount Rate = 11%
Note — the above items have been done for you in the template in that the focus is to be on the capital budgeting analysis.
**********************WHAT IS REQUIRED************************
For both companies (show separate calculations for each company — to be placed in the highlighted area of the template) calculate:
1) A 5 year projected income statement — done in the template
2) A 5 year projected cash flow — done in the template
3) Net Present Value
4) Internal Rate of Return
5) Payback Period
6) Profitability Index
7) Discounted Payback Period
8) Modified Internal Rate of Return
9) Based on items 1 through 8, which company should you acquire? (state this at the end of the spreadsheet template)
Capital Budgeting Mini Case Template
CORPORATION A
YEAR 0 1 2 3 4 5
Revenues 100.00 110.00 121.00 133.10 146.41
Expenses 20.00 23.00 26.45 30.42 34.98
Dep. 5.00 5.00 5.00 5.00 5.00
EBIT 75.00 82.00 89.55 97.68 106.43
Tax 18.75 20.50 22.39 24.42 26.61
Net Income 56.25 61.50 67.16 73.26 79.82
Add:
Depreciation 5.00 5.00 5.00 5.00 5.00
CASH FLOW (250.00) 61.25 66.50 72.16 78.26 84.82
Discount rate = 10.00%
In this template your are to ONLY fill in the yellow highlighted area with the correct calculation/formula.
NPV ….save and submit as your individual assignment
IRR
PB years
PI
DPB years
MIRR
PV (55.68) (54.96) (54.22) (53.45) (52.67) (270.98)
FV (89.68) (88.51) (87.32) (86.09) (84.82) (436.41)
CORPORATION B
YEAR 0 1 2 3 4 5
Revenues 150.00 162.00 174.96 188.96 204.07
Expenses 60.00 66.00 72.60 79.86 87.85
Dep. 10.00 10.00 10.00 10.00 10.00
EBIT 80.00 86.00 92.36 99.10 106.23
Tax 20.00 21.50 23.09 24.77 26.56
Net Income 60.00 64.50 69.27 74.32 79.67
Add:
Depreciation 10.00 10.00 10.00 10.00 10.00
CASH FLOW (250.00) 70.00 74.50 79.27 84.32 89.67
Discount rate = 11.00%
In this template your are to ONLY fill in the yellow highlighted area with the correct calculation/formula.
NPV ….save and submit as your individual assignment
IRR
PB years
PI
DPB years
MIRR
PV (63.06) (60.47) (57.96) (55.55) (53.22) (290.25)
FV (106.26) (101.89) (97.67) (93.60) (89.67) (489.09)
