Financial Modelling project (requires one part on excel and another part on python (jupyter interface)
Project 1 - Excel and Python TVM Nick DeRobertis January 27, 2020 Overview The Problem You work for new startup that is trying to manufacture phones. You are tasked with building a model which will help determine how many machines to invest in and how much to spend on marketing. Each machine produces noutput phones per year. Each phone sells for $pphone and costs $cphone in variable costs to produce. After nlife years, the machine can no longer produce output, but may be scrapped for $pscrap. The machine will not be replaced, so you may end up with zero total output before your model time period ends. Equity investment is limited, so in each year you can spend cmachine to either buy a machine or buy advertisements. In the first year you must buy a machine. Any other machine purchases must be made one after another (advertising can only begin after machine buying is done). Demand for your phones starts at d1. Each time you advertise, demand increases by gd%. The prevailing market interest rate is r. Notes • You may limit your model to 20 years and a maximum of 5 machines if it is helpful. • For simplicity, assume that cmachine is paid in every year, even after all machines have shut down. • Ensure that you can change the inputs and the outputs change as expected. The Model Inputs • noutput: Number of phones per machine per year • nmachines: Number of machines purchased • nlife: Number of years for which the machine produces phones • pphone: Price per phone • pscrap: Scrap value of machine • cmachine: Price per machine or advertising year • cphone: Variable cost per phone • d1: Quantity of phones demanded in the first year • gd: Percentage growth in demand for each advertisement • r: Interest rate earned on investments 1 Outputs • Cash flows in each year, up to 20 years • PV of revenues, years 1 - 20 Bonus Problem It is unrealistic to assume that price and demand are unrelated. To extend the model, we can introduce a relationship between price and demand, given by the following equation: d1 = dc − Epphone (1) • E: Price elasticity of demand • dc: Demand constant For elasticities and constants [(E = 500, dc = 900000), (E = 200, dc = 500000), (E = 100, dc = 300000)] (3 total cases), and taking the other model inputs in the Check your Work section, determine the optimal price for each elasticity, that is the price which maximizes the NPV. Notes • d1 is no longer an input, but an output. • This bonus requires optimization, which we have not yet covered in class. • In Excel, you can use Solver. • In Python, the scipy package provides optimization tools. You will probably want to use: – scipy.optimize.minimize_scalar – You will need to write a function which accepts price and returns NPV, with other model inputs fixed. ∗ Depending on how you set this up, functools.partial may be helpful for this. – It will actually need to return negative NPV, as the optimizer only minimizes, but we want maximum NPV. – No answers to check your work are given for this bonus. The Check your Work section only applies to without the bonus. Excel Exercise You must start from "Project 1 Template.xlsx" on Canvas. Ensure that you reference all inputs from the In- puts/Outputs tab. Also ensure that all outputs are referenced back to the Inputs/Outputs tab. Do not change any locations of the inputs or outputs. The final submission is your Excel workbook. Python Exercise You must start from "Project 1 Template.ipynb" on Canvas. I should be able to run all the cells and get the output of your model at the bottom. You should not change the ModelInputs cell and the ModelInputs cell should be the fifth cell. You need to define cash_flows as your output cash flows (numbers, not formatted), and npv as your NPV (number, not formatted). When you show your final outputs in the notebook, then they should be formatted. Page 2 of 3 https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.minimize_scalar.html#scipy-optimize-minimize-scalar https://www.learnpython.org/en/Partial_functions Grading Grading Breakdown Category Percentage Model Accuracy 70% Model Readability 20% Model Formatting 10% Bonus 5% Total Possible 105% Check your Work If you pass the following inputs (to the basic model, not bonus model): • noutput: $100,000 • pscrap: $50,000 • pphone: $500 • cmachine: $1,000,000 • cphone: $250 • nlife: 10 • nmachines: 5 • d1: $100,000 • gd: 20% • r: 5% You should get the following result: Cash Flows: Year 1: $24,000,000 Year 2: $24,000,000 Year 3: $24,000,000 Year 4: $24,000,000 Year 5: $24,000,000 Year 6: $29,000,000 Year 7: $35,000,000 Year 8: $42,200,000 Year 9: $50,840,000 Year 10: $61,208,000 Year 11: $73,699,600 Year 12: $74,050,000 Year 13: $49,050,000 Year 14: $24,050,000 Year 15: $-950,000 Year 16: $-1,000,000 Year 17: $-1,000,000 Year 18: $-1,000,000 Year 19: $-1,000,000 Year 20: $-1,000,000 NPV: $369,276,542 Page 3 of 3 Overview The Problem Notes The Model Inputs Outputs Bonus Problem Notes Excel Exercise Python Exercise Grading Check your Work