$1$

$2$ Yellow highlighted cells are cells for inputs. Team should verify all other calculations & formats

$3$ C D E F G H I J K L M N O P Q R S T

$4$ Inputs

$5$ ATSV old @ t$=0$ ATSV formula $=$ Names of all members who contributed to overall project: Include an NPV Profile

$6$ Equipment $1$ Note: Only $1$ NPV Profile for base case model should be completed

$7$ Tax Credit $2$

$8$ Depreciaton per year $3$

$9$ Sales period $1$ growth: g yrs $2-3=15\%$ g yrs $4-6=5\%4$

$10$ CoGS $\%$of sales $5$

$11$ SG&A exp. $\%$of sales $6$

$12$ ATSV new @ t$=6$

$13$

$14$ Operating Life CFs

$15$ Time $0123456$

$16$ Sales

$17-$ COGS

$18-$ SG&A expenses

$19-$ Depreciation

$20=$ EBIT

$21-$Taxes $(40\%)$

$22=$ Net Income

$23+$ Depreciation

$24=$ Operating CF

$25$

$26$ Time $0$ Investments NPV Analysis Grid: NPV vs Discount Rate & Cost of Goods Sold $($CoGS$)$ Percent Ranges

$27$ Equipment

$28$ ATSV old CoGS $->$ CoGSbase$\%-20\%$ CoGSbase$\%-10\%$ CoGS Base $\%$ CoGSbase$\%+10\%$ CoGSbase$\%+20\%$

$29$ Tax credit CoGS $->$ i$.$e base$\%*0\mathrm{.}8$ i$.$e base$\%*0\mathrm{.}9$ i$.$e base$\%*1\mathrm{.}0$ i$.$e base$\%*1\mathrm{.}1$ i$.$e base$\%*1\mathrm{.}2$

$30$ NWC CoGS $->$

$31$ Cost of Capital $4\%$

$32$ Terminal Non$-$OCF: $6\%$

$33$ ATSV new @ t$=68\%$

$34$ NWC $10\%$

$35=$ Net Cash Flow $$0$ $$0$ $$0$ $$0$ $$0$ $$0$ $$012\%$

$36=$ Cummulative CF $$0$ $$0$ $$0$ $$0$ $$0$ $$0$ $$0$

$37$

$38$ Cost of Capital $8\%$

$39$ NPV

$40$ IRR $=$ NPV Analysis Grid: NPV vs Discount Rate & Year $1$ Sales Ranges

$41$ PBP $=$

$42$ PI $=$ Sales Yr$\mathrm{.}1->$ "Sales Yr$\mathrm{.}1$

$-20\%"$ "Sales Yr$\mathrm{.}1$

$-10\%"$ "Sales Yr$\mathrm{.}1$

Base" "Sales Yr$\mathrm{.}1$

$+10\%"$ "Sales Yr$\mathrm{.}1$

$+20\%"$

$43$ Sales Yr$\mathrm{.}1->$ i$.$e base$\%*0\mathrm{.}8$ i$.$e base$\%*0\mathrm{.}9$ i$.$e base$\%*1\mathrm{.}0$ i$.$e base$\%*1\mathrm{.}1$ i$.$e base$\%*1\mathrm{.}2$

$44$ Table for NPV at rates $0\%-26\%$ for NPV Profile Graph $NPV in Cells:

$45$ "Rates

$(0-26\%$ by $2$ increments$)"$ NPV Cost of Capital $4\%$

$466\%$

$478\%$

$4810\%$

$4912\%$

$50$

$51$

$52$ Notes $/$ Reminders:

$53$ NPV Profile

$54$ Use NPVs from table on left to populate grids for different scenarios

$55$

$56$