Case Study: Al-Amal Manufacturing
Al-Amal Manufacturing is considering buying a new production machine to expand capacity. The CFO has asked you to evaluate the decision financially, and also to organize a supplier cost table using a lookup function.
Part 1: The investment decision (NPV / IRR)
| Item | Value |
|---|---|
| Machine cost (year 0) | EGP 600,000 |
| Expected incremental cash flow — year 1 | EGP 160,000 |
| Expected incremental cash flow — year 2 | EGP 190,000 |
| Expected incremental cash flow — year 3 | EGP 210,000 |
| Expected incremental cash flow — year 4 | EGP 190,000 |
| Expected incremental cash flow — year 5 | EGP 160,000 |
| Discount rate (cost of financing) | 13% |
Required: calculate NPV using the discount rate, calculate IRR, and decide whether the factory should buy the machine — explain why in one sentence.
Part 2: Supplier cost table (VLOOKUP)
You have a table of 5 supplier codes and their per-unit prices. Use VLOOKUP (or INDEX/MATCH) to pull any supplier’s price just by typing their code, instead of searching manually.
| Supplier Code | Supplier Name | Price per Unit |
|---|---|---|
| S-101 | Alpha Metals Supplier | 42 |
| S-102 | Beta Plastics Supplier | 18 |
| S-103 | Gamma Chemicals Supplier | 65 |
| S-104 | Delta Packaging Supplier | 9 |
| S-105 | Epsilon Glass Supplier | 31 |
What’s required
- Download the blank Excel template from the course overview page and build the NPV/IRR calculation in Part 1 using the formulas from this module.
- Build a VLOOKUP table in Part 2 so that changing the supplier code auto-updates the price.
- Compare your work to the downloadable “solved model” file.
- Test your understanding on the quiz page.