Exercise / Step 4.1 : Capacity base case
Calculate a capacity base case
From a spreadsheet which you will use as a framework, you will evaluate the profitability of a capacity investment: the company buys a machine to manufacture and sell products during a given period.
You got the following information:
- Investment lifetime 8 years
- Number of products sold 15,000 units per year
- Unit selling price $100
- Variable cost $40 per unit (only cash items)
- Fixed costs $400,000
- Machine purchasing price $1,200,000
- Corporate tax rate 35%
- WACC 7%
The machine is evenly depreciated over its expected lifetime.
The attached file discounts the cash-flows and accumulates them. Your task consists, then, to complete the cells in blue:
I suggest you use $k (‘000) for all calculations.
Exercise Summary
0 of 9 Questions completed
Questions:
Information
You have already completed the exercise before. Hence you can not start it again.
Exercise is loading…
You must sign in or sign up to start the exercise.
You must first complete the following:
Results
Results
0 of 9 Questions answered correctly
Your time:
Time has elapsed
You have reached 0 of 0 point(s), (0)
Earned Point(s): 0 of 0, (0)
0 Essay(s) Pending (Possible Point(s): 0)
Categories
- Not categorized 0%
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- Current
- Review
- Answered
- Correct
- Incorrect
-
Question 1 of 9
1. Question
Calculate (for the first year only, as the spreadsheet will automatically copy on the right for years 2 to 8), the annual sales figure in ‘000 dollars (cell C22)
CorrectIncorrectHint
Number of units times unit selling price (don’t forget to divide by 1,000 $k)
-
Question 2 of 9
2. Question
Calculate (for the first year only, as the spreadsheet will automatically copy on the right for years 2 to 8), the cash operating costs (Opex, in ‘000 dollars) (cell C23).
CorrectIncorrectHint
Fixed plus variable costs
-
Question 3 of 9
3. Question
Calculate (for the first year only, as the spreadsheet will automatically copy on the right for years 2 to 8), the EBITDA before tax generated by the business (C34)
CorrectIncorrectHint
Profit = sales – costs!
-
Question 4 of 9
4. Question
Calculate (for the first year only, as the spreadsheet will automatically copy on the right for years 2 to 8), the EBITDA after tax generated by the activity (C35)
CorrectIncorrectHint
Result question 3 minus tax
-
Question 5 of 9
5. Question
Calculate (for the first year only, as the spreadsheet will automatically copy on the right for years 2 to 8), the annual depreciation of the machine (C37)
CorrectIncorrectHint
The challenge consists in dividing by 8!
-
Question 6 of 9
6. Question
Calculate (for the first year only, as the spreadsheet will automatically copy on the right for years 2 to 8), the tax savings generated by the depreciation of the machine (C38)
CorrectIncorrectHint
The annual depreciation multiplied by the corporate tax rate as you save taxes
-
Question 7 of 9
7. Question
Calculate (for the first year only, as the spreadsheet will automatically copy on the right for years 2 to 8), the total cash in-flows generated by the machine (C40)
CorrectIncorrect -
Question 8 of 9
8. Question
Look at the formulas in the spreadsheet:
- Line 44 calculates the sum of cash in-flows and out-flows: negative flow when you invest, positive flow when you exploit
- Line 46 discounts the cash flow calculated in line 44, i.e. divides this cash flow by 1 plus the cost of capital (WACC, discount rate) at the power which represents the year when the cash flow is generated CFn / (1 + WACC)n
- Line 48 accumulates the discounted cash flows to figure out how the discounted cash flows progressively pay the initial investment back.
- The Net Present Value is the sum of all discounted cash flows (cell B51) or the result of accumulating these flows (cell J48)
- The Internal Rate of Return is calculated by the spreadsheet (look at the function in cell H51).
Excel gives you the Net Present Value (NPV) and the Internal Rate of Return (IRR)
How many years it takes to repay the initial investment with accumulated discounted cash-flows (G51, financial payback)
CorrectIncorrectHint
Simply when the accumulated discounted cash-flows become positive
-
Question 9 of 9
9. Question
Look at the formulas in the spreadsheet:
- Line 44 calculates the sum of cash in-flows and out-flows: negative flow when you invest, positive flow when you exploit
- Line 46 discounts the cash flow calculated in line 44, i.e. divides this cash flow by 1 plus the cost of capital (WACC, discount rate) at the power which represents the year when the cash flow is generated CFn / (1 + WACC)n
- Line 48 accumulates the discounted cash flows to figure out how the discounted cash flows progressively pay the initial investment back.
- The Net Present Value is the sum of all discounted cash flows (cell B51) or the result of accumulating these flows (cell J48)
- The Internal Rate of Return is calculated by the spreadsheet (look at the function in cell H51).
Excel gives you the Net Present Value (NPV) and the Internal Rate of Return (IRR)
How many years does it take without discounting the cash-flows (accounting payback)
CorrectIncorrectHint
Same question as 8/ but with a WACC equal to zero