125.810 Case Studies in Corporate Finance and Risk Management
Case study 1: Anwal Gas Traders
Due: 5.00 pm December 10th, 2024
Submission instruction: To submit: goto Stream → Assessment → Case Study 1 Submission. This is an individual assignment. No collaboration is allowed. You will need to submit two Excel files for this assignment. No Word file is required. For discussion questions, please use the text box provided in the Excel files.
Assignment instruction:
1. The assignment will be graded based on the Excel formulas used to calculate the results, rather than the results themselves. Please ensure that you display your calculations in the formulas. Copying and pasting the results without showing the formulas will result in a score of 0..
2. When using Excel formulas for your calculations, you should reference the input values using reference cells, rather than typing the values directly into the formula. This is very IMPORTANT— failing to do so may lead to errors in your analysis. For example, in the "Static model" sheet of the "MonteCarloSimulation" spreadsheet, the NPV in cell C21 can be calculated as:=C18+NPV(C3,D18:M18)
Or =C18+NPV(12%,D18:M18)
In the formula highlighted in yellow, the input (discount rate) is referenced from cell C3, while in the formula highlighted in green, the input (discount rate) is typed as 12%. The yellow-highlighted formula is the preferred approach.
3. Provide your answers for each question in the corresponding sheet provided. For example, the answer to Question 1.1 should be placed in the sheet named "Question 1.1."
4. For questions that require you to comment, explain, interpret, etc., please type your answer in the text box provided. You can resize the text box if there is not enough space to type your answer.
Anwal Gas Traders
Anwal Gas Traders was a small, private-owned LPG (liquefied petroleum gas) distribution company in Pakistan that was founded in 1998. In 2003, the company acquired a licence from the country’s largest distributor, and in 2020 it had distribution licences for 19 companies, including majors brands.
The company dealt with four major LPG products: cylinders, stoves, cylinder regulators, and gas pipes. It also provided the primary service of delivering LPG-filled cylinders. In 2020, Pakistan had a total LPG consumption of 1.8 million metric tons (t), of which 61 per cent was consumed in Punjab, where Anwal Gas Traders was located. The company was one of the top LPG cylinder distributors in the local area.
The company has a solid customer base and its financial performance was encouraging. In 2020, the company had a net profit margin of 12 percent, a slight decrease of 0.7 percent from the previous year due to the COVID-19 pandemic. The operating profit margin was 15 percent in 2020, adecrease of 0.8 percent from the previous year (see Exhibit 1). The company made sales of Rs 94.6 million, with an Rs 11.4 million net profit (See Exhibit 2).
The expansion project:
The company is considering an expansion project to solve its low storage issue and any issues arising from supplier dependence. At the time, Anwal Gas Trader was both an LPG distributor and sub- distributor. By implementing this project, it would integrate backward to become an LPG marketing and distribution company. It would receive LPG directly to its storage tanks from the production site and no longer be a pure intermediary for the supplier (See Exhibit 3).
The company had accumulated a sizable amount of cash, and its bank was willing to extend a loan for 10 years at an interest rate of 16 percent, which would provide strong financial support for the expansion project.
Since there was no existing storage facility, the company needed to purchase and install a new plant which required a storage container, a pump, filling dispensers, LPG bottles, a supporting structure, and firefighting equipment. The cylinders, supporting structure, and firefighting equipment would be purchased from local companies (see Exhibit 4). Anwal Gas Trader planned to build a storage capacity of 480 t, therefore it would need four 100-t tanks and one 80-t storage tank. The overall life of the plant would be 20 years but Anwal would use this plant for 10 years and then sell it to the market at the end of the project’s life.
The manager of Anwal estimated that the company would need a Rs 95.1 million investment for the project. This amount included the cost of machinery and equipment, land and buildings, vehicles, factory and office furniture, and working capital. The company wanted to run this project for only 10 years. As the company wanted to maintain a 50-50 debt-to-equity capital structure, the WACC of the project was 14 percent. The company's overall WACC was 12.5 percent.
Since there was no existing storage facility, the company needed to purchase and install a new plant. The cylinders, firefighting equipment, and supporting structure would be purchase from local companies (see Exhibit 4). Anwal Gas Trader planned to build a storage capacity of 480t, therefore, it would need four 100-t tanks and one 80-t storage tanks. The overall life of the plant would be 20 years, but Anwal Gas Traders would use this plan for 10 years and then sell it to the market at the end of the project’s life.
Of the land that Anwal wanted to use for the project, 20% would be used for offices and the remaining would be used for the storage plant. The LPG distribution required a cargo pickup truck, and the office staff required a car and moytorcycles. The annual depreciation expenses would be Rs 3.63 million for all depreciable assets under the proposed project. Depreciation treatment for all items was on a straight- line basis. The expected salvage value of Fixed assets was Rs 35.15 million at the end of the project life.
With the storage facility, the estimated annual sales of the company would be 212,160 cylinders in the first year of the project. For one cylinder, the expected price and material costs in year 1 were Rs 1,307 and Rs 983, respectively. The expected increase in sales volume and material costs for one cylinder would be 2 percent annually. The price of the LPG cylinder was expected to increase by 1 percent annually.
The human resources department expected to hire administrative and site staff to run the storage plant. It would also hire experienced technical staff who could deal with highly flammable gas. A total of 21 employees would be hired and compensated at the market rate (see Exhibit 5).
Electricity and fuel were significant utility expenses for the business. Utilities for the first year would cost Rs 2,136,000 annually. The estimated selling and distribution expenses are 3% of annual sales. The estimated miscellaneous expenses would be Rs 180,000. Salaries, utility expenses, and miscellaneous expenses would increase by 5 percent annually. The firm’stax rate is 29%.
*Note: Case Exhibits can be found in Dataset1.xlsx and Dataset2.xlsx.
Part I: Use the Excel Spreadsheet Dataset1.xlsx for Part I
Question 1.1
What is the central issue facing Anwal Gas Trader?
Question 1.2
Perform the capital budgeting for the project and calculate the NPV, IRR, payback period, and profitability index.
*Hint: A sample format to estimate Free Cash Flow can be found in the Sheet “Question 1.1”. However, where necessary, you may need to add separate sheets/spaces to work on the values for some items that require several calculation steps. For example, you can add an additional sheet named “Salaries” to calculate expenses related to employees’ salaries and then use these results as input in the FCF calculation in Question 1.2. Please do NOT bury your answer in complicated Excel formulas. Clearly showing each working step is very important.
If you make any assumptions in your analysis, pleaseadd a text box and clearly state your assumptions along with the rationale behind them
Question 1.3
Based on your answer to Question 1.2, would you accept or reject the project? Please explain. Do you think the expansion project will help the company address the issues you identified in Question 1.1?
Do you think the estimates made by Anwal's managers are reasonable? If not, how would you adjust the estimates
Question 1.4
The Finance Department predicted that local bank interest rates would change and the new interest rates for long-term loans would range from 10% to 20%. Assume that other factors remain unchanged, perform. the sensitivity analysis on how NPV would change corresponding to the change in the interest rates and interpret the results.
Question 1.5
The Marketing Department further advised that current estimations of sales and unit prices would change depending on market scenarios. Under a good scenario, first-year sale is estimated to be 280,000 cylinders and the unit price in the first year is Rs 1250. Under a bad scenario, first-year sale is estimated to be 180,000 cylinders and the unit price is Rs 1350. Material cost is harder to predict as it depends on several macroeconomics factors so the Production Department expected that it can randomly be in the range from Rs 800 to Rs 1200 per unit in the first year.
Build a scenario model (similar to the one in the spreadsheet NewProductLaunch_ScenarioAnalysis that we discussed in class) to show how NPV would change under different scenarios. Assume that the probability of good scenario is 60% and bad scenario is 40% and other factors remain unchanged. Interpret the results of the scenario analysis.
Part II: Use the Excel Spreadsheet Dataset2.xlsx for Part II
The CFO at Anwal finds that assuming the estimated costs and revenues as static numbers (as in Part I) is not ideal. Therefore, here-estimates the costs and revenue as follows:
• The initial investment needed to start the project will follow a triangular distribution. The minimum estimated value of the initial investment is Rs 73 million, the maximum estimated value is Rs 125 million, and the most likely estimated value is Rs 98 million.
• Only s% of the expected sale in the first year will be realized, and s is a uniform random number between 75% to 125%.
• For one cylinder, the expected price in the first year would follow a triangular distribution, with a minimum value of Rs 1,180, a maximum value of Rs 1,500, and the most likely value of Rs 1,300
• Material costs for one cylinder in the first year would follow a triangular distribution, with a minimum value of Rs 915,a maximum value of Rs 1,260, and the most likely value of Rs 1,000
• The expected increase in sales volume and material costs for one cylinder from year 2 onwards would bex percent annually, in which x follows a normal distribution with a mean of 2.1% and a standard deviation of 0.3%.
• The price of the LPG cylinder was expected to increase by y percent annually, in which x follows a normal distribution with a mean of 0.9% and a standard deviation of 0.15%
• The utilities in year 1 are estimated to follow a triangular distribution, with a minimum value of Rs 1,850,000, a maximum value of Rs 2,500,000, and the most likely value of Rs 2,000,000. Salaries, utility expenses, and miscellaneous expenses would increase by z percent annually, in which z follows a normal distribution with a mean of 3.2% and a standard deviation of 0.5%.
• Except for the factors above, all other information remains the same as in Part I.
Question 2.1
Perform the capital budgeting for the project and calculate the NPV, IRR, payback period, and profitability index under the new estimation of the CFO.
*Note: Although the calculations will be very similar to those in PartI, you should NOT copy-paste the formulas in PartIto use in Part II as it may lead to calculation errors.
Question 2.2:
a) Run a Monte Carlo simulation with 2800 replications of the NPVs. Calculate the Average Profit of the Sample and the Standard Error.
b) Draw the cumulative NPV distribution (similar to the one in the Sheet Model with defined input of the spreadsheet MonteCarloSimulation that we discussed in class).
c) What is the probability of a negative NPV if we invest in the project? What is 10% VaR? What does that number (10% VaR) mean?
d) Based on the results from the simulation, are you going to accept or reject the project? Explain your investment decision. Discuss other factors that can affect your investment decision.
e) Is your investment decision different from the one that you made based on the static model in Question 1.1 Part I? Explain why/ why not. If the investment decisions are different, which one do you think is more accurate? Explain.
f) Do you think the assumptions on the distributions of uncertain inputs in Part II are reasonable? Explain? If the assumptions are not reasonable, how would you want to change them?