Chapter 103 Bloopers analysts have come up with the followin

Chapter 10.3

Blooper’s analysts have come up with the following revised estimates for its magnoosium mine:

Range

Conduct a sensitivity analysis for each variable and range and compute the NPV for each. Use Spreadsheet 10.1 and accompanying data as a starting point for the analysis. (Do not round intermediate calculations. Negative amounts should be indicated by a minus sign. Enter your answers in thousands rounded to the nearest whole dollar.)

	Range
	Pessimistic		Optimistic
Initial investment	+	40	%		–	15	%
Revenues	–	15	%		+	20	%
Variable costs	+	25	%		–	20	%
Fixed cost	+	40	%		–	20	%
Working capital	+	45	%		–	25	%

Solution

Sensitivity Analysis of Initial Investment Pessimistic Optimistic 0 1 2 3 4 5 6 0 1 2 3 4 5 6 Revenue 15,000 15,750 16,538 17,364 18,233 15,000 15,750 16,538 17,364 18,233 Less: Variable expenses 6,000 6,300 6,615 6,946 7,293 6,000 6,300 6,615 6,946 7,293 Less: Fixed Expenses 4,000 4,200 4,410 4,631 4,862 4,000 4,200 4,410 4,631 4,862 Less: Depreciation 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 Pre tax Profit 3,000 3,250 3,513 3,787 4,078 3,000 3,250 3,513 3,787 4,078 Tax 1,050 1,138 1,229 1,326 1,427 1,050 1,138 1,229 1,326 1,427 Profit after tax 1,950 2,113 2,283 2,462 2,651 1,950 2,113 2,283 2,462 2,651 Operating cash flow 3,950 4,113 4,283 4,462 4,651 3,950 4,113 4,283 4,462 4,651 Working Capital Working capital 1,500 4,075 4,279 4,493 4,717 3,039 0 1,500 4,075 4,279 4,493 4,717 3,039 0 Change in working capital 1,500 2,575 204 214 225 -1,679 -3,039 1,500 2,575 204 214 225 -1,679 -3,039 Cash flow from investment in working capital -1,500 -2,575 -204 -214 -225 1,679 3,039 -1,500 -2,575 -204 -214 -225 1,679 3,039 Initial Investment in Fixed assets -14000 -7500 Sale of fixed assets 1300 1300 Total Project cash flow -15,500 1,375 3,909 4,069 4,237 6,330 4,339 -9,000 1,375 3,909 4,069 4,237 6,330 4,339 Discounting factor @ 6.67% 1 0.9374707 0.878851 0.823897 0.77238 0.724083 0.678807 1 0.937471 0.878851 0.823897 0.77238 0.724083 0.678807 Present value of cashflow -15,500 1,289 3,435 3,353 3,272 4,583 2,945 -9,000 1,289 3,435 3,353 3,272 4,583 2,945 NPV 3,378 9,878 Sensitivity Analysis of Sales Pessimistic Optimistic 0 1 2 3 4 5 6 0 1 2 3 4 5 6 Revenue 12,750 13,388 14,057 14,759 15,498 18,000 18,900 19,846 20,837 21,880 Less: Variable expenses 5,100 5,355 5,623 5,904 6,199 7,200 7,560 7,938 8,335 8,752 Less: Fixed Expenses 4,000 4,200 4,410 4,631 4,862 4,000 4,200 4,410 4,631 4,862 Less: Depreciation 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 Pre tax Profit 1,650 1,833 2,024 2,225 2,437 4,800 5,140 5,497 5,871 6,266 Tax 578 641 709 779 853 1,680 1,799 1,924 2,055 2,193 Profit after tax 1,073 1,191 1,316 1,446 1,584 3,120 3,341 3,573 3,816 4,073 Operating cash flow 3,073 3,191 3,316 3,446 3,584 5,120 5,341 5,573 5,816 6,073 Working Capital Working capital 1,365 3,563 3,741 3,928 4,124 2,588 0 1,680 4,770 5,009 5,259 5,522 3,654 0 Change in working capital 1,365 2,198 178 187 196 -1,536 -2,588 1,680 3,090 239 251 263 -1,868 -3,039 Cash flow from investment in working capital -1,365 -2,198 -178 -187 -196 1,536 2,588 -1,680 -2,575 -204 -214 -225 1,679 3,039 Initial Investment in Fixed assets -10000 -10000 Sale of fixed assets 1300 1300 Total Project cash flow -11,365 875 3,013 3,129 3,250 5,120 3,888 -11,680 2,545 5,137 5,359 5,591 7,752 4,339 Discounting factor @ 6.67% 1 0.9374707 0.878851 0.823897 0.77238 0.724083 0.678807 1 0.937471 0.878851 0.823897 0.77238 0.724083 0.678807 Present value of cashflow -11,365 820 2,648 2,578 2,510 3,707 2,639 -11,680 2,386 4,515 4,415 4,319 5,613 2,945 NPV 3,538 12,513 Sensitivity Analysis of Variable costs Pessimistic Optimistic 0 1 2 3 4 5 6 0 1 2 3 4 5 6 Revenue 15,000 15,750 16,538 17,364 18,233 15,000 15,750 16,538 17,364 18,233 Less: Variable expenses 7,500 7,875 8,269 8,682 9,117 4,800 5,040 5,292 5,556 5,835 Less: Fixed Expenses 4,000 4,200 4,410 4,631 4,862 4,000 4,200 4,410 4,631 4,862 Less: Depreciation 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 Pre tax Profit 1,500 1,675 1,859 2,051 2,255 4,200 4,510 4,836 5,177 5,536 Tax 525 586 651 718 789 1,470 1,579 1,693 1,812 1,938 Profit after tax 975 1,089 1,208 1,333 1,465 2,730 2,932 3,143 3,365 3,599 Operating cash flow 2,975 3,089 3,208 3,333 3,465 4,730 4,932 5,143 5,365 5,599 Working Capital Working capital 1,725 4,316 4,532 4,759 4,997 3,045 0 1,320 3,891 4,086 4,290 4,504 3,045 0 Change in working capital 1,725 2,591 216 227 238 -1,952 -3,045 1,320 2,571 195 204 214 -1,459 -3,039 Cash flow from investment in working capital -1,725 -2,591 -216 -227 -238 1,952 3,045 -1,320 -2,575 -204 -214 -225 1,679 3,039 Initial Investment in Fixed assets -10000 -10000 Sale of fixed assets 1300 1300 Total Project cash flow -11,725 384 2,873 2,982 3,095 5,417 4,345 -11,320 2,155 4,728 4,929 5,140 7,278 4,339 Discounting factor @ 6.67% 1 0.9374707 0.878851 0.823897 0.77238 0.724083 0.678807 1 0.937471 0.878851 0.823897 0.77238 0.724083 0.678807 Present value of cashflow -11,725 360 2,525 2,457 2,391 3,922 2,949 -11,320 2,020 4,155 4,061 3,970 5,270 2,945 NPV 2,879 11,101 Sensitivity Analysis of Fixed cost Pessimistic Optimistic 0 1 2 3 4 5 6 0 1 2 3 4 5 6 Revenue 15,000 15,750 16,538 17,364 18,233 15,000 15,750 16,538 17,364 18,233 Less: Variable expenses 6,000 6,300 6,615 6,946 7,293 6,000 6,300 6,615 6,946 7,293 Less: Fixed Expenses 5,600 5,880 6,174 6,483 6,807 3,200 3,360 3,528 3,705 3,890 Less: Depreciation 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 Pre tax Profit 1,400 1,570 1,749 1,935 2,133 3,800 4,090 4,395 4,714 5,050 Tax 490 550 612 677 747 1,330 1,432 1,538 1,650 1,768 Profit after tax 910 1,021 1,137 1,258 1,386 2,470 2,659 2,857 3,064 3,283 Operating cash flow 2,910 3,021 3,137 3,258 3,386 4,470 4,659 4,857 5,064 5,283 Working Capital Working capital 1,740 4,332 4,549 4,776 5,015 3,045 0 1,380 3,954 4,152 4,359 4,577 3,045 0 Change in working capital 1,740 2,592 217 228 239 -1,970 -3,045 1,380 2,574 198 208 218 -1,532 -3,045 Cash flow from investment in working capital -1,740 -2,592 -217 -228 -239 1,970 3,045 -1,380 -2,574 -198 -208 -218 1,532 3,045 Initial Investment in Fixed assets -10000 -10000 Sale of fixed assets 1300 1300 Total Project cash flow -11,740 318 2,804 2,909 3,019 5,356 4,345 -11,380 1,896 4,461 4,649 4,846 6,815 4,345 Discounting factor @ 6.67% 1 0.9374707 0.878851 0.823897 0.77238 0.724083 0.678807 1 0.937471 0.878851 0.823897 0.77238 0.724083 0.678807 Present value of cashflow -11,740 298 2,464 2,397 2,332 3,878 2,949 -11,380 1,777 3,920 3,830 3,743 4,935 2,949 NPV 2,579 9,775 Sensitivity Analysis of Working capital Pessimistic Optimistic 0 1 2 3 4 5 6 0 1 2 3 4 5 6 Revenue 15,000 15,750 16,538 17,364 18,233 15,000 15,750 16,538 17,364 18,233 Less: Variable expenses 6,000 6,300 6,615 6,946 7,293 6,000 6,300 6,615 6,946 7,293 Less: Fixed Expenses 4,000 4,200 4,410 4,631 4,862 4,000 4,200 4,410 4,631 4,862 Less: Depreciation 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 Pre tax Profit 3,000 3,250 3,513 3,787 4,078 3,000 3,250 3,513 3,787 4,078 Tax 1,050 1,138 1,229 1,326 1,427 1,050 1,138 1,229 1,326 1,427 Profit after tax 1,950 2,113 2,283 2,462 2,651 1,950 2,113 2,283 2,462 2,651 Operating cash flow 3,950 4,113 4,283 4,462 4,651 3,950 4,113 4,283 4,462 4,651 Working Capital Working capital 2,175 3,734 296 310 326 -2,435 0 1,125 1,931 153 161 169 -1,259 0 Change in working capital 2,175 1,559 -3,438 15 16 -2,761 2,435 1,125 806 -1,778 8 8 -1,428 1,259 Cash flow from investment in working capital -2,175 -1,559 3,438 -15 -16 2,761 -2,435 -1,125 -806 1,778 -8 -8 1,428 -1,259 Initial Investment in Fixed assets -10000 -10000 Sale of fixed assets 1300 1300 Total Project cash flow -12,175 2,391 7,550 4,269 4,446 7,411 -1,135 -11,125 3,144 5,891 4,276 4,454 6,079 41 Discounting factor @ 6.67% 1 0.9374707 0.878851 0.823897 0.77238 0.724083 0.678807 1 0.937471 0.878851 0.823897 0.77238 0.724083 0.678807 Present value of cashflow -12,175 2,242 6,636 3,517 3,434 5,366 -770 -11,125 2,947 5,177 3,523 3,440 4,401 28 NPV 8,250 8,391 Sensitivity Analysis Pessimistic Optimistic NPV Change in Initial Investment 3378 9878 NPV Change in revenue 3538 12513 NPV Change in Variable cost 2879 11101 NPV Change in Fixed cost 2579 9775 NPV Change in working capital 8250 8391