Appendix VII
Base Case Analysis of Webster Street Store
With 30% Loss to Albertson Superstore
WACC 15%
Sales Growth Rate 4%
Cost of Good Sold 75.90%
Salary Costs 12.10%
Employee Benefits 25%
Other Employee Costs 0.60%
Base Rent $79,400
% Rent 0.00%
Advertising 1.00%
Utilities Inflation 6.00%
Other Expenses 2.00%
Base Case Sales 11,130,000
Sales Erosion Factor 0.30
Incremental Sales % 1
MACRS % 20.00% 32.00% 19.20% 11.52% 11.52% 5.76%
Leashold Improvements 0
Equipment Costs 0
Inventory Turnover 11
Existing Inventory 768
Year
12345678910 11 12 13 14 15 16 17
Sales 11,130 11,575 12,038 12,520 13,021 13,541 14,083 14,646 15,232 15,841 16,475 17,134 17,819 18,532 19,274 20,045 20,846
Cost of Goods Sold 8,448 8,786 9,137 9,502 9,883 10,278 10,689 11,117 11,561 12,024 12,505 13,005 13,525 14,066 14,629 15,214 15,822
Gross Profit 2,682 2,790 2,901 3,017 3,138 3,263 3,394 3,530 3,671 3,818 3,971 4,129 4,294 4,466 4,645 4,831 5,024
Salaries 1,347 1,401 1,457 1,515 1,575 1,639 1,704 1,772 1,843 1,917 1,993 2,073 2,156 2,242 2,332 2,425 2,522
Other Employee Costs 67 69 72 75 78 81 84 88 91 95 99 103 107 111 116 120 125
Employee Benefits 337 350 364 379 394 410 426 443 461 479 498 518 539 561 583 606 631
Advertising 111 116 120 125 130 135 141 146 152 158 165 171 178 185 193 200 208
Rent 79 83 86 89 93 97 100 104 109 113 118 122 127 132 137 143 149
Building Costs:
Building Maintenance 21 22 23 24 25 26 27 28 29 30 31 32 34 35 36 38 39
Common Area Maintenance 23 23 24 25 26 27 28 30 31 32 33 35 36 37 39 41 42
Building Insurance 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 4
Real Estate Taxes 27 28 29 30 31 33 34 35 37 38 40 41 43 45 47 48 50
Building Depreciation 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Total Building Costs 72 75 78 81 85 88 91 95 99 103 107 111 116 120 125 130 135
Utilities 116 123 130 138 146 155 165 174 185 196 208 220 233 247 262 278 295
Equipment Costs:
Equipment Depreciation 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Equipment Maintneance 39 40 42 43 45 47 49 51 53 55 57 59 62 64 67 69 72
Other Equipment Costs 17 18 18 19 20 21 22 22 23 24 25 26 27 28 29 31 32
Total Equipment Costs 56 58 60 62 65 68 70 73 76 79 82 85 89 92 96 100 104
Other Expenses 223 232 241 250 260 271 282 293 305 317 330 343 356 371 385 401 417
Profit From Operations 275 284 293 302 311 321 330 340 350 361 371 382 393 404 415 426 438
Taxes 105 108 111 115 118 122 125 129 133 137 141 145 149 153 158 162 166
Net Profit After Taxes 171 176 181 187 193 199 205 211 217 224 230 237 243 250 257 264 271
Depreciation 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Yearly Inventory 768 799 831 864 898 934 972 1,011 1,051 1,093 1,137 1,182 1,230 1,279 1,330 1,383 1,438
Inventory Investment 0 -31 -32 -33 -35 -36 -37 -39 -40 -42 -44 -45 -47 -49 -51 -53 -55
Leasehold Improvements 0
Equipment Costs 0
Residual Value
Free Cash Flow Without Superstore 0 171 145 149 154 158 163 167 172 177 182 186 191 196 201 206 211 216
NPV $1,012
IRR #NUM!
Sales Growth Rate 4%
Cost of Good Sold 75.30%
Salary Costs 11.90%
Employee Benefits 25%
Other Employee Costs 0.55%
Base Rent $97,900
% Rent 0.00%
Advertising 0.90%
Utilities Inflation 4.00%
Other Expenses 2.00%
Year
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Sales 19500000 20280000 21091200 21934848 22812242 23724732 24673721 25660670 26687096 27754580 28864764 30019354 31220128 32468933 33767691 35118398 36523134
Cost of Goods Sold 14683500 15270840 15881674 16516941 17177618 17864723 18579312 19322484 20095384 20899199 21735167 22604574 23508757 24449107 25427071 26444154 27501920
Gross Profit 4816500 5009160 5209526 5417907 5634624 5860009 6094409 6338185 6591713 6855381 7129597 7414780 7711372 8019827 8340620 8674244 9021214
Salaries 2320500 2413320 2509853 2610247 2714657 2823243 2936173 3053620 3175764 3302795 3434907 3572303 3715195 3863803 4018355 4179089 4346253
Other Employee Costs 107250 111540 116002 120642 125467 130486 135705 141134 146779 152650 158756 165106 171711 178579 185722 193151 200877
Employee Benefits 580125 603330 627463 652562 678664 705811 734043 763405 793941 825699 858727 893076 928799 965951 1004589 1044772 1086563
Advertising 175500 182520 189821 197414 205310 213523 222063 230946 240184 249791 259783 270174 280981 292220 303909 316066 328708
Rent 97900 97900 97900 97900 97900 97900 97900 97900 97900 97900 97900 97900 97900 97900 97900 97900 97900
Building Costs:
Building Maintenance 24000 24000 24000 24000 24000 24000 24000 24000 24000 24000 24000 24000 24000 24000 24000 24000 24000
Common Area Maintenance 25000 25000 25000 25000 25000 25000 25000 25000 25000 25000 25000 25000 25000 25000 25000 25000 25000
Building Insurance 2100 2100 2100 2100 2100 2100 2100 2100 2100 2100 2100 2100 2100 2100 2100 2100 2100
Real Estate Taxes 34000 34000 34000 34000 34000 34000 34000 34000 34000 34000 34000 34000 34000 34000 34000 34000 34000
Building Depreciation 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Total Building Costs 85100 85100 85100 85100 85100 85100 85100 85100 85100 85100 85100 85100 85100 85100 85100 85100 85100
Utilities 142500 148200 154128 160293 166705 173373 180308 187520 195021 202822 210935 219372 228147 237273 246764 256634 266900
Equipment Costs:
Equipment Depreciation
Equipment Maintneance 48000 48000 48000 48000 48000 48000 48000 48000 48000 48000 48000 48000 48000 48000 48000 48000 48000
Other Equipment Costs 23000 23000 23000 23000 23000 23000 23000 23000 23000 23000 23000 23000 23000 23000 23000 23000 23000
Total Equipment Costs 71000 71000 71000 71000 71000 71000 71000 71000 71000 71000 71000 71000 71000 71000 71000 71000 71000
Other Expenses 390000 405600 421824 438697 456245 474495 493474 513213 533742 555092 577295 600387 624403 649379 675354 702368 730463
Profit From Operations 846625 890650 936436 984053 1033576 1085079 1138642 1194347 1252281 1312533 1375194 1440362 1508136 1578622 1651926 1728163 1807450
Taxes 321718 338447 355846 373940 392759 412330 432684 453852 475867 498762 522574 547337 573092 599876 627732 656702 686831
Net Profit After Taxes 524908 552203 580590 610113 640817 672749 705958 740495 776414 813770 852620 893024 935044 978745 1024194 1071461 1120619
Appendix VIII
Base Case Analysis of Park Hill Acres (Business as Usual)
WACC 15%
Sales Growth Rate 4%
Cost of Good Sold 75.30%
Salary Costs 11.90%
Employee Benefits 25%
Other Employee Costs 0.55%
Base Rent 97,900
% Rent 0.00%
Advertising 0.90%
Utilities Inflation 6.00%
Other Expenses 2.00%
Base Case Sales 19,500,000
Sales Erosion Factor 0.00
Incremental Sales % 1
MACRS % 20.00% 32.00% 19.20% 11.52% 11.52% 5.76%
Leashold Improvements
Equipment Costs 0
Inventory Turnover 11
Existing Inventory 1,335
Year
12345678910 11 12 13 14 15 16 17
Sales 19,500 20,280 21,091 21,935 22,812 23,725 24,674 25,661 26,687 27,755 28,865 30,019 31,220 32,469 33,768 35,118 36,523
Cost of Goods Sold 14,684 15,271 15,882 16,517 17,178 17,865 18,579 19,322 20,095 20,899 21,735 22,605 23,509 24,449 25,427 26,444 27,502
Gross Profit 4,817 5,009 5,210 5,418 5,635 5,860 6,094 6,338 6,592 6,855 7,130 7,415 7,711 8,020 8,341 8,674 9,021
Salaries 2,321 2,413 2,510 2,610 2,715 2,823 2,936 3,054 3,176 3,303 3,435 3,572 3,715 3,864 4,018 4,179 4,346
Other Employee Costs 107 112 116 121 125 130 136 141 147 153 159 165 172 179 186 193 201
Employee Benefits 580 603 627 653 679 706 734 763 794 826 859 893 929 966 1,005 1,045 1,087
Advertising 176 183 190 197 205 214 222 231 240 250 260 270 281 292 304 316 329
Rent 98 102 106 110 115 119 124 129 134 139 145 151 157 163 170 176 183
Building Costs:
Building Maintenance 24 25 26 27 28 29 30 32 33 34 36 37 38 40 42 43 45
Common Area Maintenance 25 26 27 28 29 30 32 33 34 36 37 38 40 42 43 45 47
Building Insurance 2 2 2 2 2 3 3 3 3 3 3 3 3 3 4 4 4
Real Estate Taxes 34 35 37 38 40 41 43 45 47 48 50 52 54 57 59 61 64
Building Depreciation 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Total Building Costs 85 89 92 96 100 104 108 112 116 121 126 131 136 142 147 153 159
Utilities 143 151 160 170 180 191 202 214 227 241 255 271 287 304 322 342 362
Equipment Costs:
Equipment Depreciation 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Equipment Maintneance 48 50 52 54 56 58 61 63 66 68 71 74 77 80 83 86 90
Other Equipment Costs 23 24 25 26 27 28 29 30 31 33 34 35 37 38 40 41 43
Total Equipment Costs 71 74 77 80 83 86 90 93 97 101 105 109 114 118 123 128 133
Other Expenses 390 406 422 439 456 474 493 513 534 555 577 600 624 649 675 702 730
Profit From Operations 847 878 910 943 977 1,013 1,049 1,087 1,127 1,167 1,209 1,252 1,297 1,343 1,391 1,440 1,491
Taxes 322 334 346 358 371 385 399 413 428 443 459 476 493 510 528 547 566
Net Profit After Taxes 525 544 564 585 606 628 651 674 698 724 750 776 804 833 862 893 924
Depreciation 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Yearly Inventory 1,335 1,388 1,444 1,502 1,562 1,624 1,689 1,757 1,827 1,900 1,976 2,055 2,137 2,223 2,312 2,404 2,500
Inventory Investment 0 -53 –56 -58 -60 -62 -65 -68 -70 -73 -76 -79 -82 -85 -89 -92 -96
Leasehold Improvements 0
Equipment Costs 0
Residual Value
Free Cash Flow W ithout Superstore 0 525 491 508 527 546 565 586 607 628 651 674 697 722 747 773 800 828
NPV $3,492
IRR #NUM!
Appendix IX
Expansion of Park Hill Acres Store
WACC 15%
Sales Growth Rate 4%
Cost of Good Sold 74.60%
Salary Costs 11.80%
Employee Benefits 25%
Other Employee Costs 0.50%
Base Rent $147,000
% Rent 0.00%
Advertising 0.90%
Utilities Inflation 6.00%
Other Expenses 2.00%
Base Case Sales 23,400,000
Sales Erosion Factor 0.00
Incremental Sales % 1
MACRS % 20.00% 32.00% 19.20% 11.52% 11.52% 5.76%
Leashold Improvements 750,000
Equipment Costs 1,000,000
Inventory Turnover 11
Existing Inventory 1,335
Year
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Sales 23,400 24,336 25,309 26,322 27,375 28,470 29,608 30,793 32,025 33,305 34,638 36,023 37,464 38,963 40,521 42,142 43,828
Cost of Goods Sold 17,456 18,155 18,881 19,636 20,422 21,238 22,088 22,971 23,890 24,846 25,840 26,873 27,948 29,066 30,229 31,438 32,696
Gross Profit 5,944 6,181 6,429 6,686 6,953 7,231 7,521 7,821 8,134 8,460 8,798 9,150 9,516 9,897 10,292 10,704 11,132
Salaries 2,761 2,872 2,987 3,106 3,230 3,359 3,494 3,634 3,779 3,930 4,087 4,251 4,421 4,598 4,782 4,973 5,172
Other Employee Costs 117 122 127 132 137 142 148 154 160 167 173 180 187 195 203 211 219
Employee Benefits 690 718 747 776 808 840 873 908 945 983 1,022 1,063 1,105 1,149 1,195 1,243 1,293
Advertising 211 219 228 237 246 256 266 277 288 300 312 324 337 351 365 379 394
Rent 147 153 159 165 172 179 186 193 201 209 218 226 235 245 255 265 275
Building Costs:
Building Maintenance 36 37 39 40 42 44 46 47 49 51 53 55 58 60 62 65 67
Common Area Maintenance 37 38 40 42 43 45 47 49 51 53 55 57 59 62 64 67 69
Building Insurance 3 3 3 3 4 4 4 4 4 4 5 5 5 5 5 6 6
Real Estate Taxes 47 49 51 53 55 57 59 62 64 67 70 72 75 78 81 85 88
Building Depreciation 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44
Total Building Costs 167 172 177 183 188 194 200 206 213 219 226 234 241 249 257 266 275
Utilities 212 225 238 252 268 284 301 319 338 358 380 402 427 452 479 508 539
Equipment Costs:
Equipment Depreciation 200 320 192 115 115 58 0 0 0 0 0 0 0 0 0 0 0
Equipment Maintneance 72 75 78 81 84 88 91 95 99 102 107 111 115 120 125 130 135
Other Equipment Costs 35 36 38 39 41 43 44 46 48 50 52 54 56 58 61 63 66
Total Equipment Costs 307 431 308 236 240 188 135 141 146 152 158 165 171 178 185 193 200
Other Expenses 468 487 506 526 547 569 592 616 640 666 693 720 749 779 810 843 877
Profit From Operations 863 783 953 1,072 1,117 1,220 1,325 1,373 1,424 1,476 1,529 1,585 1,642 1,701 1,761 1,824 1,889
Taxes 328 298 362 407 424 464 503 522 541 561 581 602 624 646 669 693 718
Net Profit After Taxes 535 486 591 665 692 756 821 851 883 915 948 982 1,018 1,054 1,092 1,131 1,171
Depreciation 244 364 236 159 159 102 44 44 44 44 44 44 44 44 44 44 44
Yearly Inventory 1,587 1,650 1,716 1,785 1,857 1,931 2,008 2,088 2,172 2,259 2,349 2,443 2,541 2,642 2,748 2,858 2,972
Inventory Investment -252 -63 -66 –69 -71 -74 –77 -80 -84 -87 -90 -94 -98 -102 -106 -110 -114
Leasehold Improvements 750
Equipment Costs 1000
Residual Value
Free Cash Flow W ith Expansion -1750 527 786 761 756 780 784 788 815 843 872 902 933 964 997 1,030 1,065 1,101
Incremental FCF with Expansion -1750 2 295 252 229 234 218 202 209 215 222 228 235 242 250 257 265 273
NPV ($504)
IRR 9.60%
Appendix X
Base Case Analysis of Webster Street Store (Business as Usual)
WACC 15%
Sales Growth Rate 4%
Cost of Good Sold 75.90%
Salary Costs 12.10%
Employee Benefits 25%
Other Employee Costs 0.60%
Base Rent $79,400
% Rent 0.00%
Advertising 1.00%
Utilities Inflation 6.00%
Other Expenses 2.00%
Base Case Sales 15,900,000
Sales Erosion Factor 0.00
Incremental Sales % 1
MACRS % 20.00% 32.00% 19.20% 11.52% 11.52% 5.76%
Leashold Improvements 0
Equipment Costs 0
Inventory Turnover 11
Existing Inventory 1,097
Year
12345678910 11 12 13 14 15 16 17
Sales 15,900 16,536 17,197 17,885 18,601 19,345 20,119 20,923 21,760 22,631 23,536 24,477 25,456 26,475 27,534 28,635 29,780
Cost of Goods Sold 12,068 12,551 13,053 13,575 14,118 14,683 15,270 15,881 16,516 17,177 17,864 18,578 19,321 20,094 20,898 21,734 22,603
Gross Profit 3,832 3,985 4,145 4,310 4,483 4,662 4,849 5,043 5,244 5,454 5,672 5,899 6,135 6,380 6,636 6,901 7,177
Salaries 1,924 2,001 2,081 2,164 2,251 2,341 2,434 2,532 2,633 2,738 2,848 2,962 3,080 3,203 3,332 3,465 3,603
Other Employee Costs 95 99 103 107 112 116 121 126 131 136 141 147 153 159 165 172 179
Employee Benefits 481 500 520 541 563 585 609 633 658 685 712 740 770 801 833 866 901
Advertising 159 165 172 179 186 193 201 209 218 226 235 245 255 265 275 286 298
Rent 79 83 86 89 93 97 100 104 109 113 118 122 127 132 137 143 149
Building Costs:
Building Maintenance 21 22 23 24 25 26 27 28 29 30 31 32 34 35 36 38 39
Common Area Maintenance 23 23 24 25 26 27 28 30 31 32 33 35 36 37 39 41 42
Building Insurance 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 4
Real Estate Taxes 27 28 29 30 31 33 34 35 37 38 40 41 43 45 47 48 50
Building Depreciation 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Total Building Costs 72 75 78 81 85 88 91 95 99 103 107 111 116 120 125 130 135
Utilities 116 123 130 138 146 155 165 174 185 196 208 220 233 247 262 278 295
Equipment Costs:
Equipment Depreciation 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Equipment Maintneance 39 40 42 43 45 47 49 51 53 55 57 59 62 64 67 69 72
Other Equipment Costs 17 18 18 19 20 21 22 22 23 24 25 26 27 28 29 31 32
Total Equipment Costs 56 58 60 62 65 68 70 73 76 79 82 85 89 92 96 100 104
Other Expenses 318 331 344 358 372 387 402 418 435 453 471 490 509 529 551 573 596
Profit From Operations 531 550 570 590 611 632 655 678 701 726 751 776 803 831 859 888 918
Taxes 202 209 217 224 232 240 249 257 266 276 285 295 305 316 326 337 349
Net Profit After Taxes 329 341 353 366 379 392 406 420 435 450 465 481 498 515 532 551 569
Depreciation 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Yearly Inventory 1,097 1,141 1,187 1,234 1,283 1,335 1,388 1,444 1,501 1,562 1,624 1,689 1,756 1,827 1,900 1,976 2,055
Inventory Investment 0 -44 –46 -47 -49 -51 -53 -56 -58 -60 -62 -65 -68 -70 -73 -76 -79
Leasehold Improvements 0
Equipment Costs 0
Residual Value
Free Cash Flow W ithout Superstore 0 329 297 308 318 329 341 352 365 377 390 403 416 430 445 459 475 490
NPV $2,112
IRR #NUM!
Appendix XI
Expansion of Webster Street Store
WACC 15%
Sales Growth Rate 4%
Cost of Good Sold 75.00%
Salary Costs 12.10%
Employee Benefits 25%
Other Employee Costs 0.60%
Base Rent $129,000
% Rent 0.00%
Advertising 1.00%
Utilities Inflation 6.00%
Other Expenses 2.00%
Base Case Sales 19,080,000
Sales Erosion Factor 0.00
Incremental Sales % 1
MACRS % 20.00% 32.00% 19.20% 11.52% 11.52% 5.76%
Leashold Improvements 750,000
Equipment Costs 1,000,000
Inventory Turnover 11
Existing Inventory 1,097
Year
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Sales 19,080 19,843 20,637 21,462 22,321 23,214 24,142 25,108 26,112 27,157 28,243 29,373 30,548 31,770 33,040 34,362 35,736
Cost of Goods Sold 14,310 14,882 15,478 16,097 16,741 17,410 18,107 18,831 19,584 20,368 21,182 22,030 22,911 23,827 24,780 25,772 26,802
Gross Profit 4,770 4,961 5,159 5,366 5,580 5,803 6,036 6,277 6,528 6,789 7,061 7,343 7,637 7,942 8,260 8,591 8,934
Salaries 2,309 2,401 2,497 2,597 2,701 2,809 2,921 3,038 3,160 3,286 3,417 3,554 3,696 3,844 3,998 4,158 4,324
Other Employee Costs 114 119 124 129 134 139 145 151 157 163 169 176 183 191 198 206 214
Employee Benefits 577 600 624 649 675 702 730 760 790 821 854 889 924 961 999 1,039 1,081
Advertising 191 198 206 215 223 232 241 251 261 272 282 294 305 318 330 344 357
Rent 129 134 140 145 151 157 163 170 177 184 191 199 207 215 223 232 242
Building Costs:
Building Maintenance 33 34 36 37 39 40 42 43 45 47 49 51 53 55 57 59 62
Common Area Maintenance 36 37 39 40 42 44 46 47 49 51 53 55 58 60 62 65 67
Building Insurance 3 3 3 3 4 4 4 4 4 4 4 5 5 5 5 5 6
Real Estate Taxes 44 46 48 49 51 54 56 58 60 63 65 68 70 73 76 79 82
Building Depreciation 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44
Total Building Costs 160 165 170 175 180 185 191 197 203 209 216 223 230 237 245 253 261
Utilities 189 200 212 225 239 253 268 284 301 319 338 359 380 403 427 453 480
Equipment Costs:
Equipment Depreciation 200 320 192 115 115 58 0 0 0 0 0 0 0 0 0 0 0
Equipment Maintneance 63 66 68 71 74 77 80 83 86 90 93 97 101 105 109 113 118
Other Equipment Costs 28 29 30 31 33 34 35 37 38 40 41 43 45 47 48 50 52
Total Equipment Costs 291 415 290 218 222 168 115 120 125 130 135 140 146 152 158 164 170
Other Expenses 382 397 413 429 446 464 483 502 522 543 565 587 611 635 661 687 715
Profit From Operations 428 331 483 584 610 693 778 805 833 862 892 923 954 987 1,020 1,054 1,089
Taxes 163 126 184 222 232 263 295 306 317 328 339 351 363 375 388 401 414
Net Profit After Taxes 265 205 300 362 378 430 482 499 517 535 553 572 592 612 632 653 675
Depreciation 244 364 236 159 159 102 44 44 44 44 44 44 44 44 44 44 44
Yearly Inventory 1,301 1,353 1,407 1,463 1,522 1,583 1,646 1,712 1,780 1,852 1,926 2,003 2,083 2,166 2,253 2,343 2,437
Inventory Investment -204 -52 -54 –56 -59 -61 –63 -66 -68 -71 -74 -77 -80 -83 -87 -90 –94
Leasehold Improvements 750
Equipment Costs 1000
Residual Value
Free Cash Flow W ith Expansion -1750 306 517 482 465 479 471 463 477 492 508 523 539 556 573 590 608 626
Incremental FCF with Expansion -1750 -24 220 174 147 149 130 110 113 115 118 120 123 125 128 130 133 135
NPV ($1,005)
IRR 2.35%
Appendix XII
Lost Profits from Webster Street Store Closure
WACC 15%
Sales Growth Rate 4%
Cost of Good Sold 75.90%
Salary Costs 12.10%
Employee Benefits 25%
Other Employee Costs 0.60%
Base Rent $79,400
% Rent 0.00%
Advertising 1.00%
Utilities Inflation 6.00%
Other Expenses 2.00%
Base Case Sales 11,130,000
Sales Erosion Factor 0.30
Incremental Sales % 1
MACRS % 20.00% 32.00% 19.20% 11.52% 11.52% 5.76%
Leashold Improvements 0
Equipment Costs 0
Inventory Turnover 11
Existing Inventory 1,097
Year
12345678910 11 12 13 14 15 16 17
Sales 11,130 11,575 12,038 12,520 13,021 13,541 14,083 14,646 15,232 15,841 16,475 17,134 17,819 18,532 19,274 20,045 20,846
Cost of Goods Sold 8,448 8,786 9,137 9,502 9,883 10,278 10,689 11,117 11,561 12,024 12,505 13,005 13,525 14,066 14,629 15,214 15,822
Gross Profit 2,682 2,790 2,901 3,017 3,138 3,263 3,394 3,530 3,671 3,818 3,971 4,129 4,294 4,466 4,645 4,831 5,024
Salaries 1,347 1,401 1,457 1,515 1,575 1,639 1,704 1,772 1,843 1,917 1,993 2,073 2,156 2,242 2,332 2,425 2,522
Other Employee Costs 67 69 72 75 78 81 84 88 91 95 99 103 107 111 116 120 125
Employee Benefits 337 350 364 379 394 410 426 443 461 479 498 518 539 561 583 606 631
Advertising 111 116 120 125 130 135 141 146 152 158 165 171 178 185 193 200 208
Rent 79 83 86 89 93 97 100 104 109 113 118 122 127 132 137 143 149
Building Costs:
Building Maintenance 21 22 23 24 25 26 27 28 29 30 31 32 34 35 36 38 39
Common Area Maintenance 23 23 24 25 26 27 28 30 31 32 33 35 36 37 39 41 42
Building Insurance 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 4
Real Estate Taxes 27 28 29 30 31 33 34 35 37 38 40 41 43 45 47 48 50
Building Depreciation 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Total Building Costs 72 75 78 81 85 88 91 95 99 103 107 111 116 120 125 130 135
Utilities 116 123 130 138 146 155 165 174 185 196 208 220 233 247 262 278 295
Equipment Costs:
Equipment Depreciation 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Equipment Maintneance 39 40 42 43 45 47 49 51 53 55 57 59 62 64 67 69 72
Other Equipment Costs 17 18 18 19 20 21 22 22 23 24 25 26 27 28 29 31 32
Total Equipment Costs 56 58 60 62 65 68 70 73 76 79 82 85 89 92 96 100 104
Other Expenses 223 232 241 250 260 271 282 293 305 317 330 343 356 371 385 401 417
Profit From Operations 275 284 293 302 311 321 330 340 350 361 371 382 393 404 415 426 438
Taxes 105 108 111 115 118 122 125 129 133 137 141 145 149 153 158 162 166
Net Profit After Taxes 171 176 181 187 193 199 205 211 217 224 230 237 243 250 257 264 271
Depreciation 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Yearly Inventory 768 799 831 864 898 934 972 1,011 1,051 1,093 1,137 1,182 1,230 1,279 1,330 1,383 1,438
Inventory Investment 329 -31 -32 -33 -35 -36 -37 -39 -40 –42 -44 -45 -47 -49 -51 -53 -55
Leasehold Improvements 0
Equipment Costs 0
Residual Value
Free Cash Flow W ithout Superstore 0 500 145 149 154 158 163 167 172 177 182 186 191 196 201 206 211 216
NPV $1,298
IRR #NUM!
Appendix XIII
Minimum Sales for Park Hill Acres Superstore
WACC 15%
Sales Growth Rate 4%
Cost of Good Sold 74.20%
Salary Costs 11.80%
Employee Benefits 25%
Other Employee Costs 0.50%
Base Rent $234,960
% Rent 0.00%
Advertising 0.90%
Utilities Inflation 6.00%
Other Expenses 2.00%
Base Case Sales 37,000,000
Sales Erosion Factor 0.00
Incremental Sales % 1
MACRS % 20.00% 32.00% 19.20% 11.52% 11.52% 5.76%
Leashold Improvements 3,500,000
Equipment Costs 4,500,000
Inventory Turnover 11
Existing Inventory 1,335
Year
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Sales 37,000 38,480 40,019 41,620 43,285 45,016 46,817 48,689 50,637 52,663 54,769 56,960 59,238 61,608 64,072 66,635 69,300
Cost of Goods Sold 27,454 28,552 29,694 30,882 32,117 33,402 34,738 36,128 37,573 39,076 40,639 42,264 43,955 45,713 47,541 49,443 51,421
Gross Profit 9,546 9,928 10,325 10,738 11,167 11,614 12,079 12,562 13,064 13,587 14,130 14,696 15,283 15,895 16,531 17,192 17,879
Salaries 4,366 4,541 4,722 4,911 5,108 5,312 5,524 5,745 5,975 6,214 6,463 6,721 6,990 7,270 7,560 7,863 8,177
Other Employee Costs 185 192 200 208 216 225 234 243 253 263 274 285 296 308 320 333 347
Employee Benefits 1,092 1,135 1,181 1,228 1,277 1,328 1,381 1,436 1,494 1,554 1,616 1,680 1,748 1,817 1,890 1,966 2,044
Advertising 333 346 360 375 390 405 421 438 456 474 493 513 533 554 577 600 624
Rent 235 244 254 264 275 286 297 309 322 334 348 362 376 391 407 423 440
Building Costs:
Building Maintenance 60 62 65 67 70 73 76 79 82 85 89 92 96 100 104 108 112
Common Area Maintenance 63 65 68 70 73 76 79 82 86 89 93 96 100 104 108 113 117
Building Insurance 7 7 7 7 8 8 8 9 9 9 10 10 10 11 11 12 12
Real Estate Taxes 75 78 81 84 88 91 95 99 103 107 111 115 120 125 130 135 140
Building Depreciation 206 206 206 206 206 206 206 206 206 206 206 206 206 206 206 206 206
Total Building Costs 410 418 427 435 445 454 464 474 485 496 508 520 532 546 559 573 588
Utilities 300 318 337 357 379 401 426 451 478 507 537 569 604 640 678 719 762
Equipment Costs:
Equipment Depreciation 900 1,440 864 518 518 259 0 0 0 0 0 0 0 0 0 0 0
Equipment Maintneance 63 65 68 70 73 76 79 82 86 89 93 96 100 104 108 113 117
Other Equipment Costs 31 32 34 35 36 38 39 41 42 44 46 48 50 52 54 56 58
Total Equipment Costs 994 1,537 965 624 628 373 118 123 128 133 138 144 150 156 162 168 175
Other Expenses 740 770 800 832 866 900 936 974 1,013 1,053 1,095 1,139 1,185 1,232 1,281 1,333 1,386
Profit From Operations 892 426 1,079 1,503 1,585 1,929 2,276 2,367 2,461 2,558 2,659 2,762 2,870 2,981 3,095 3,214 3,336
Taxes 339 162 410 571 602 733 865 899 935 972 1,010 1,050 1,090 1,133 1,176 1,221 1,268
Net Profit After Taxes 553 264 669 932 983 1,196 1,411 1,468 1,526 1,586 1,648 1,713 1,779 1,848 1,919 1,993 2,068
Depreciation 1,106 1,646 1,070 724 724 465 206 206 206 206 206 206 206 206 206 206 206
Yearly Inventory 2,496 2,596 2,699 2,807 2,920 3,037 3,158 3,284 3,416 3,552 3,694 3,842 3,996 4,156 4,322 4,495 4,675
Inventory Investment -1,161 -100 -104 -108 -112 -117 -121 -126 -131 -137 -142 -148 -154 -160 -166 -173 -180
Leasehold Improvements 3500
Equipment Costs 4500
Residual Value
Free Cash Flow With Superstore -8000 498 1,810 1,635 1,548 1,595 1,544 1,496 1,547 1,600 1,655 1,712 1,771 1,831 1,894 1,959 2,026 2,095
Incremental FCF with Superstore -8000 -27 1,319 1,126 1,022 1,049 979 910 941 972 1,005 1,039 1,073 1,110 1,147 1,185 1,225 1,267
NPV ($2,421)
IRR 9.35%