show all work in formula in excel format

30 Scenario Analysis (Auto-fill) 31 Scenario analysis is used to determine the range of possible outcomes for a project. Typically, the base case, best (optimistic) case, and worst (pessimistic) case values are 32 33 Expected case Pessimistic Optimistic 34 Unit sales: $ 35 Price per unit $ 2,000,000 $ 1,900,000 $ 2,200,000 36 Variable costs per unit: s 1,000,000 $ 1,200,000 $ 800,000 37 Fbced costs per year: $ 1,940,000,000 $2,000,000,000 $1,740,000,000 38 39 Initial cost: $ 1,500,000,000 $1,900,000,000 $1,000,000,000 40 Project life (years): 5 5 5 41 Required return; 15% 15% 42 Tax rate: 21% 215 215 15% 44 With these values, we need to calculate the base case, best case, and worst case NPVs and IRRs. First, we want to calculate the NPV and IRR with the base case projections, 45 Base Case Income Statement Sales Variable costs Fixed costs Depreciation EBIT Taxes (21%) Net income 50 51 52 53 54 55 56 57 OCF NPV 30 Scenario Analysis (Auto-fill) 31 Scenario analysis is used to determine the range of possible outcomes for a project. Typically, the base case, best (optimistic) case, and worst (pessimistic) case values are 32 33 Expected case Pessimistic Optimistic 34 Unit sales: $ 35 Price per unit $ 2,000,000 $ 1,900,000 $ 2,200,000 36 Variable costs per unit: s 1,000,000 $ 1,200,000 $ 800,000 37 Fbced costs per year: $ 1,940,000,000 $2,000,000,000 $1,740,000,000 38 39 Initial cost: $ 1,500,000,000 $1,900,000,000 $1,000,000,000 40 Project life (years): 5 5 5 41 Required return; 15% 15% 42 Tax rate: 21% 215 215 15% 44 With these values, we need to calculate the base case, best case, and worst case NPVs and IRRs. First, we want to calculate the NPV and IRR with the base case projections, 45 Base Case Income Statement Sales Variable costs Fixed costs Depreciation EBIT Taxes (21%) Net income 50 51 52 53 54 55 56 57 OCF NPV