| Drug Company planning with net present value
(NPV) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Inputs |
|
|
|
| Year 1 demand |
10000 |
|
|
| Annual demand growth |
|
|
| Years 2 to 6 |
15% |
|
|
| Years 7 to 20 |
5% |
|
|
| Unit cost of production capacity[1] |
$6 |
|
|
| Depreciation rate[2] |
20% |
|
|
| Year 1 monetary values |
|
|
| Unit price |
$8 |
|
|
| Unit variable cost |
$5 |
|
|
| Unit capacity maintenance
cost |
$1 |
|
|
| Inflation rate[3] |
5% |
|
|
| Tax rate |
40% |
|
|
| Discount rate |
10% |
|
|
|
|
| Decision variable |
|
|
| Capacity level (trial value) |
15000 |
|
|
|
| Model
of net present value (NPV) |
|
| Year |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
16 |
17 |
18 |
19 |
20 |
| Building cost |
$90,000 |
|
|
| Depreciation |
$18,000 |
$18,000 |
$18,000 |
$18,000 |
$18,000 |
|
|
| Demand |
10000 |
11500 |
13225 |
15209 |
17490 |
20114 |
21119 |
22175 |
23284 |
24448 |
25671 |
26954 |
28302 |
29717 |
31203 |
32763 |
34401 |
36121 |
37927 |
39823 |
| Units sold |
10000 |
11500 |
13225 |
15000 |
15000 |
15000 |
15000 |
15000 |
15000 |
15000 |
15000 |
15000 |
15000 |
15000 |
15000 |
15000 |
15000 |
15000 |
15000 |
15000 |
|
| Unit price |
$8.00 |
$8.40 |
$8.82 |
$9.26 |
$9.72 |
$10.21 |
$10.72 |
$11.26 |
$11.82 |
$12.41 |
$13.03 |
$13.68 |
$14.37 |
$15.09 |
$15.84 |
$16.63 |
$17.46 |
$18.34 |
$19.25 |
$20.22 |
| Unit variable cost |
$5.00 |
$5.25 |
$5.51 |
$5.79 |
$6.08 |
$6.38 |
$6.70 |
$7.04 |
$7.39 |
$7.76 |
$8.14 |
$8.55 |
$8.98 |
$9.43 |
$9.90 |
$10.39 |
$10.91 |
$11.46 |
$12.03 |
$12.63 |
| Unit maintence cost |
$1.00 |
$1.05 |
$1.10 |
$1.16 |
$1.22 |
$1.28 |
$1.34 |
$1.41 |
$1.48 |
$1.55 |
$1.63 |
$1.71 |
$1.80 |
$1.89 |
$1.98 |
$2.08 |
$2.18 |
$2.29 |
$2.41 |
$2.53 |
|
| Revenue |
$80,000 |
$96,600 |
$116,645 |
$138,915 |
$145,861 |
$153,154 |
$160,811 |
$168,852 |
$177,295 |
$186,159 |
$195,467 |
$205,241 |
$215,503 |
$226,278 |
$237,592 |
$249,471 |
$261,945 |
$275,042 |
$288,794 |
$303,234 |
| Variable cost |
$50,000 |
$60,375 |
$72,903 |
$86,822 |
$91,163 |
$95,721 |
$100,507 |
$105,533 |
$110,809 |
$116,350 |
$122,167 |
$128,275 |
$134,689 |
$141,424 |
$148,495 |
$155,920 |
$163,716 |
$171,901 |
$180,496 |
$189,521 |
| Maintenance cost |
$15,000 |
$15,750 |
$16,538 |
$17,364 |
$18,233 |
$19,144 |
$20,101 |
$21,107 |
$22,162 |
$23,270 |
$24,433 |
$25,655 |
$26,938 |
$28,285 |
$29,699 |
$31,184 |
$32,743 |
$34,380 |
$36,099 |
$37,904 |
|
| Pretax profit |
-$3,000 |
$2,475 |
$9,204 |
$16,729 |
$18,465 |
$38,288 |
$40,203 |
$42,213 |
$44,324 |
$46,540 |
$48,867 |
$51,310 |
$53,876 |
$56,569 |
$59,398 |
$62,368 |
$65,486 |
$68,761 |
$72,199 |
$75,809 |
| After tax profit |
-$3,000 |
$1,485 |
$5,523 |
$10,037 |
$11,079 |
$22,973 |
$24,122 |
$25,328 |
$26,594 |
$27,924 |
$29,320 |
$30,786 |
$32,325 |
$33,942 |
$35,639 |
$37,421 |
$39,292 |
$41,256 |
$43,319 |
$45,485 |
| Free cash flow |
-$75,000 |
$19,485 |
$23,523 |
$28,037 |
$29,079 |
$22,973 |
$24,122 |
$25,328 |
$26,594 |
$27,924 |
$29,320 |
$30,786 |
$32,325 |
$33,942 |
$35,639 |
$37,421 |
$39,292 |
$41,256 |
$43,319 |
$45,485 |
|
| NPV |
$146,107 |
|
|
|
|
| Summary
measures from data table below |
|
| Maximum NPV |
$159,378 |
|
| Corresponding capacity |
21000 |
|
|
| Data
table for NPV as a function of capacity |
|
| Capacity |
NPV |
|
|
$146,107 |
|
|
| 10000 |
$108,995 |
|
| 11000 |
$118,259 |
|
| 12000 |
$126,741 |
|
| 13000 |
$134,442 |
|
| 14000 |
$140,697 |
|
| 15000 |
$146,107 |
|
| 16000 |
$150,391 |
|
| 17000 |
$153,708 |
|
| 18000 |
$156,259 |
|
| 19000 |
$158,142 |
|
| 20000 |
$159,335 |
|
| 21000 |
$159,378 |
|
| 22000 |
$158,183 |
|
| 23000 |
$155,483 |
|
| 24000 |
$151,507 |
|
| 25000 |
$146,618 |
|
| 26000 |
$140,907 |
|
| 27000 |
$134,105 |
|
| 28000 |
$126,124 |
|
| 29000 |
$117,490 |
|
| 30000 |
$108,320 |
|
| 31000 |
$98,509 |
|
| 32000 |
$88,019 |
|
| 33000 |
$77,162 |
|
| 34000 |
$65,683 |
|
| 35000 |
$53,740 |
|
| 36000 |
$41,484 |
|
| 37000 |
$28,576 |
|
| 38000 |
$15,527 |
|
| 39000 |
$1,821 |
|
| 40000 |
-$12,004 |
|
|
|
|
| Summary
measures from data table below |
|
| Discount rate |
5% |
6% |
7% |
8% |
9% |
10% |
11% |
12% |
13% |
14% |
15% |
16% |
17% |
18% |
19% |
20% |
|
| Maximum NPV |
$334,863 |
$287,519 |
$247,388 |
$213,504 |
$184,323 |
$159,378 |
$138,243 |
$120,100 |
$104,802 |
$91,910 |
$80,945 |
$71,349 |
$63,138 |
$55,839 |
$49,572 |
$43,995 |
|
| Corresponding capacity |
24000 |
23000 |
23000 |
22000 |
21000 |
21000 |
20000 |
19000 |
18000 |
17000 |
16000 |
15000 |
15000 |
14000 |
14000 |
13000 |
|
|
| Data
table for NPV as a function of capacity (along side) and discount rate (along
top) |
|
| $146,107 |
5% |
6% |
7% |
8% |
9% |
10% |
11% |
12% |
13% |
14% |
15% |
16% |
17% |
18% |
19% |
20% |
|
| 10000 |
$192,210 |
$170,843 |
$152,209 |
$135,905 |
$121,595 |
$108,995 |
$97,866 |
$88,007 |
$79,246 |
$71,439 |
$64,462 |
$58,210 |
$52,591 |
$47,530 |
$42,958 |
$38,818 |
|
| 11000 |
$209,717 |
$186,229 |
$165,747 |
$147,829 |
$132,104 |
$118,259 |
$106,032 |
$95,201 |
$85,578 |
$77,004 |
$69,343 |
$62,479 |
$56,312 |
$50,757 |
$45,741 |
$41,200 |
|
| 12000 |
$226,366 |
$200,774 |
$178,460 |
$158,943 |
$141,816 |
$126,741 |
$113,430 |
$101,641 |
$91,170 |
$81,842 |
$73,510 |
$66,046 |
$59,342 |
$53,306 |
$47,857 |
$42,926 |
|
| 13000 |
$242,159 |
$214,478 |
$190,348 |
$169,246 |
$150,734 |
$134,442 |
$120,061 |
$107,328 |
$96,021 |
$85,953 |
$76,962 |
$68,911 |
$61,683 |
$55,176 |
$49,305 |
$43,995 |
|
| 14000 |
$256,318 |
$226,589 |
$200,682 |
$178,033 |
$158,170 |
$140,697 |
$125,279 |
$111,634 |
$99,524 |
$88,745 |
$79,124 |
$70,514 |
$62,789 |
$55,839 |
$49,572 |
$43,908 |
|
| 15000 |
$269,558 |
$237,796 |
$210,127 |
$185,946 |
$164,748 |
$146,107 |
$129,666 |
$115,123 |
$102,221 |
$90,744 |
$80,506 |
$71,349 |
$63,138 |
$55,756 |
$49,104 |
$43,095 |
|
| 16000 |
$281,442 |
$247,698 |
$218,315 |
$192,647 |
$170,157 |
$150,391 |
$132,967 |
$117,564 |
$103,908 |
$91,767 |
$80,945 |
$71,273 |
$62,607 |
$54,822 |
$47,813 |
$41,488 |
|
| 17000 |
$292,233 |
$256,534 |
$225,463 |
$198,335 |
$174,577 |
$153,708 |
$135,324 |
$119,082 |
$104,693 |
$91,910 |
$80,523 |
$70,355 |
$61,252 |
$53,082 |
$45,733 |
$39,108 |
|
| 18000 |
$302,068 |
$264,457 |
$231,737 |
$203,186 |
$178,196 |
$156,259 |
$136,946 |
$119,896 |
$104,802 |
$91,403 |
$79,478 |
$68,839 |
$59,322 |
$50,790 |
$43,122 |
$36,217 |
|
| 19000 |
$311,061 |
$271,576 |
$237,245 |
$207,305 |
$181,116 |
$158,142 |
$137,930 |
$120,100 |
$104,327 |
$90,338 |
$77,899 |
$66,811 |
$56,902 |
$48,028 |
$40,061 |
$32,894 |
|
| 20000 |
$319,261 |
$277,924 |
$242,003 |
$210,695 |
$183,327 |
$159,335 |
$138,243 |
$119,650 |
$103,216 |
$88,653 |
$75,716 |
$64,194 |
$53,909 |
$44,706 |
$36,454 |
$29,039 |
|
| 21000 |
$325,941 |
$282,836 |
$245,404 |
$212,801 |
$184,323 |
$159,378 |
$137,466 |
$118,168 |
$101,127 |
$86,041 |
$72,652 |
$60,741 |
$50,121 |
$40,630 |
$32,131 |
$24,503 |
|
| 22000 |
$330,916 |
$286,152 |
$247,308 |
$213,504 |
$184,002 |
$158,183 |
$135,527 |
$115,593 |
$98,009 |
$82,460 |
$68,677 |
$56,430 |
$45,524 |
$35,791 |
$27,086 |
$19,286 |
|
| 23000 |
$333,810 |
$287,519 |
$247,388 |
$212,496 |
$182,076 |
$155,483 |
$132,172 |
$111,686 |
$93,638 |
$77,699 |
$63,588 |
$51,069 |
$39,936 |
$30,015 |
$21,157 |
$13,232 |
|
| 24000 |
$334,863 |
$287,180 |
$245,883 |
$210,017 |
$178,782 |
$151,507 |
$127,630 |
$106,672 |
$88,233 |
$71,971 |
$57,597 |
$44,861 |
$33,555 |
$23,496 |
$14,529 |
$6,522 |
|
| 25000 |
$334,484 |
$285,534 |
$243,185 |
$206,446 |
$174,489 |
$146,618 |
$122,249 |
$100,890 |
$82,123 |
$65,596 |
$51,009 |
$38,106 |
$26,669 |
$16,512 |
$7,474 |
-$584 |
|
| 26000 |
$332,771 |
$282,680 |
$239,391 |
$201,881 |
$169,293 |
$140,907 |
$116,122 |
$94,427 |
$75,392 |
$58,655 |
$43,905 |
$30,879 |
$19,352 |
$9,133 |
$55 |
-$8,021 |
|
| 27000 |
$329,379 |
$278,289 |
$234,190 |
$196,025 |
$162,910 |
$134,105 |
$108,988 |
$87,035 |
$67,804 |
$50,920 |
$36,065 |
$22,969 |
$11,401 |
$1,164 |
-$7,912 |
-$15,970 |
|
| 28000 |
$324,046 |
$272,147 |
$227,407 |
$188,737 |
$155,229 |
$126,124 |
$100,783 |
$78,668 |
$59,326 |
$42,372 |
$27,482 |
$14,378 |
$2,825 |
-$7,379 |
-$16,407 |
-$24,406 |
|
| 29000 |
$317,516 |
$264,948 |
$219,687 |
$180,619 |
$146,813 |
$117,490 |
$91,997 |
$69,784 |
$50,387 |
$33,414 |
$18,532 |
$5,460 |
-$6,044 |
-$16,184 |
-$25,137 |
-$33,053 |
|
| 30000 |
$309,983 |
$256,865 |
$211,189 |
$171,815 |
$137,790 |
$108,320 |
$82,737 |
$60,480 |
$41,075 |
$24,124 |
$9,288 |
-$3,720 |
-$15,146 |
-$25,197 |
-$34,053 |
-$41,865 |
|
| 31000 |
$301,221 |
$247,707 |
$201,748 |
$162,182 |
$128,040 |
$98,509 |
$72,912 |
$50,678 |
$31,324 |
$14,446 |
-$300 |
-$13,205 |
-$24,518 |
-$34,450 |
-$43,182 |
-$50,867 |
|
| 32000 |
$291,092 |
$237,362 |
$191,277 |
$151,654 |
$117,509 |
$88,019 |
$62,494 |
$40,356 |
$21,119 |
$4,370 |
-$10,237 |
-$22,997 |
-$34,160 |
-$43,940 |
-$52,520 |
-$60,054 |
|
| 33000 |
$280,210 |
$226,368 |
$180,244 |
$140,639 |
$106,556 |
$77,162 |
$51,757 |
$29,758 |
$10,672 |
-$5,916 |
-$20,357 |
-$32,949 |
-$43,942 |
-$53,554 |
-$61,966 |
-$69,335 |
|
| 34000 |
$268,019 |
$214,249 |
$168,243 |
$128,791 |
$94,885 |
$65,683 |
$40,483 |
$18,695 |
-$178 |
-$16,553 |
-$30,783 |
-$43,166 |
-$53,956 |
-$63,369 |
-$71,588 |
-$78,771 |
|
| 35000 |
$254,802 |
$201,256 |
$155,498 |
$116,307 |
$82,670 |
$53,740 |
$28,810 |
$7,288 |
-$11,324 |
-$27,444 |
-$41,427 |
-$53,573 |
-$64,134 |
-$73,326 |
-$81,333 |
-$88,313 |
|
| 36000 |
$240,897 |
$187,678 |
$142,253 |
$103,397 |
$70,090 |
$41,484 |
$16,869 |
-$4,348 |
-$22,666 |
-$38,505 |
-$52,218 |
-$64,105 |
-$74,420 |
-$83,377 |
-$91,160 |
-$97,926 |
|
| 37000 |
$225,486 |
$172,830 |
$127,936 |
$89,579 |
$56,742 |
$28,576 |
$4,375 |
-$16,455 |
-$34,410 |
-$49,909 |
-$63,302 |
-$74,889 |
-$84,921 |
-$93,612 |
-$101,145 |
-$107,675 |
|
| 38000 |
$209,742 |
$157,702 |
$113,384 |
$75,564 |
$43,227 |
$15,527 |
-$8,240 |
-$28,664 |
-$46,241 |
-$61,386 |
-$74,449 |
-$85,726 |
-$95,468 |
-$103,887 |
-$111,164 |
-$117,452 |
|
| 39000 |
$192,408 |
$141,247 |
$97,721 |
$60,618 |
$28,930 |
$1,821 |
-$21,408 |
-$41,340 |
-$58,466 |
-$73,196 |
-$85,877 |
-$96,802 |
-$106,218 |
-$114,334 |
-$121,330 |
-$127,356 |
|
| 40000 |
$174,772 |
$124,542 |
$81,851 |
$45,499 |
$14,490 |
-$12,004 |
-$34,675 |
-$54,099 |
-$70,760 |
-$85,064 |
-$97,355 |
-$107,920 |
-$117,003 |
-$124,811 |
-$131,520 |
-$137,280 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
