financial model

# financial model - MGT356Simulation...

• Notes
• 36

This preview shows page 1 - 10 out of 36 pages.

1 MGT 356 Simulation Financial Models & Value at Risk Example 11.5 Developing a New Car at GF Auto (Cash Flow Model) (New Car Development) Example 11.7 Investing for Retirement (Retirement1, Retirement2) Subscribe to view the full document.

Example 11.5 Developing a New Car at GF Auto n General Ford (GF) Auto Corporation is developing a new  model of a compact car n Assumed to generate sales for 5 years n Objective:   n Determine NPV of after-tax cash flows over the 5-year horizon  by simulating the cash flows 2 Assumptions n Fixed cost:   n \$ 700 million incurred at the beginning of year 1 n Margin per car  (selling price – variable cost) :   n \$4,000 in year 1.  n Will decrease by 4% each year after year 1 n Sales  (number of cars sold) n Triangular (50,000/75,000/85,000) in year 1. n Decrease by a percentage which is triangular (5%, 8%, 10%)  n Percentage decreases in successive years are independent of  one another 3 Subscribe to view the full document.

Assumptions -- continued n Depreciation:  n Development cost is depreciated on a straight-line basis over the  lifetime of the car  n Taxes:   n The corporate tax rate is 40% n Discount rate: n Cost of capital is 10% 4 Inputs & Outputs n Inputs: n Parameters – fixed cost, margin per car (1st year and decrease  rate), tax rate, discount rate  n Random variable  n Sales in the first year (triangular) n Sales decrease rate for each subsequent year (triangular) n Output: n NPV of after-tax cash flows over 5 years 5 Subscribe to view the full document.

Computation of NPV n NPV: -\$700 million + NPV of cash flows at end of year 1, 2, 3, 4,  5.   n NPV of cash flows: n Cash flows are after-tax profits n Tax is 40% of (profit - depreciation) n Profit is sales*margin n Sales is the random input variable 5 5 1 2 5 1 (0.10, , ,..., ) 1.10 t t t CF NPV NPV CF CF CF = = = Computations in the Model n Generate 1st year sales from a triangular distribution n Decrease subsequent years’ sales by a triangularly distributed  decay rate n Margin = 4000 for 1st year, 4000(100%-4%) for 2nd year,  etc. n Profit = Sales*Margin n Taxable Profit = Profit – Depreciation n Depreciation = \$700 million/5 n Tax = 40% of Taxable Profit n Cash Flow = Profit - Tax n NPV = -\$700 million + NPV(0.10, Cash flows from 5 years) 7 Subscribe to view the full document.

Developing the Spreadsheet Model n Inputs:  Enter the various inputs in the shaded cell.  (Optional: Name them) n Unit sales n First-year sales in cell B12 =RISKTRIANG(E5,F5,G5) n 2nd-year sales in cell C12 = first-year sales * (1-decay rate)  = B12*(1-RISKTRIANG(\$E\$6,\$F\$6,\$G\$6)).  Copy across the row.   n Contributions    n First-year margin in cell B13 = \$4,000 (=B5) n 2nd-year in cell C13 = first-year margin * (1-4%) = B13*(1-\$B\$6).  Copy across the row.   n Profit in row 14 = Margin*Unit Sales 8 Developing the Spreadsheet Model --  continued n Depreciation  = Fixed cost/5 n Taxable profit Subscribe to view the full document. ### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern