APA, One Page Paper and Two P&L's (Not Including The Title, Spreadsheets, And Reference Pages). The Description Is Below:ONE-
C1000
sample3-yearprojection1.xlsxLikely Case Scenario
| ABC Firm Financial Data | |||||||||||||||||
| Consolidated Statements of Operations (USD in millions, except per share amounts) | |||||||||||||||||
| Fiscal years ending September 28, 2016, September 27, 2017, and September 25, 2018 | |||||||||||||||||
| Forecast of Unique Factors | |||||||||||||||||
| 2016 Actual | 2017 Actual | 2018 Actual | 2016 to 2017 % Change | 2017 to 2018 % Change | Avg. % Change | 2019 Forecast | 2020 Forecast | 2021 Forecast | 2016 Actual | 2017 Actual | 2018 Actual | 2019 Forecast | 2020 Forecast | 2021 Forecast | |||
| Sales | $ 14,194 | $ 15,389 | $ 15,724 | 8.42% | 2.18% | 5.30% | $ 16,667 | $ 17,667 | $ 18,551 | Domestic Sales, as % of Total Sales | 96.7% | 96.9% | 97.1% | 97.3% | 97.5% | 97.7% | |
| Cost of goods sold and occupancy costs | 9,150 | 9,973 | 10,313 | 8.99% | 3.41% | 6.20% | 10,952 | 11,631 | 12,353 | International Sales, as % of Total Sales | 3.3% | 3.1% | 2.9% | 2.7% | 2.5% | 2.3% | |
| Gross profit | 5,044 | 5,416 | 5,411 | 7.38% | -0.09% | 3.64% | 5,715 | 6,036 | 6,198 | Total Sales | 100.0% | 100.0% | 100.0% | 100.0% | 100.0% | 100.0% | |
| Selling, general, and adminisrative expenses | 4,032 | 4,472 | 4,477 | 10.91% | 0.11% | 5.51% | 4,432 | 4,521 | 4,611 | ||||||||
| Pre-opening expenses | 67 | 67 | 64 | 0.00% | -4.48% | -2.24% | 64 | 65 | 65 | Prepared Foods & Bakery | 19.2% | 19.0% | 18.9% | 19.5% | 20.1% | 20.7% | |
| Relocation, store closure, and lease termination costs | 11 | 16 | 13 | 45.45% | -18.75% | 13.35% | 11 | 9 | 7 | Other Perishables | 47.6% | 47.5% | 47.6% | 46.8% | 46.0% | 45.2% | |
| Operating income | 934 | 861 | 857 | -7.82% | -0.46% | -4.14% | 1,208 | 1,441 | 1,515 | Total Perishable Sales | 66.8% | 66.5% | 66.5% | 66.3% | 66.1% | 65.9% | |
| Interest expense | - | - | (41) | (41) | (41) | (41) | Non-perishable Sales | 33.2% | 33.5% | 33.5% | 33.7% | 33.9% | 34.1% | ||||
| Investment and other income | 12 | 17 | 11 | 41.67% | -35.29% | 3.19% | 11.3 | 11.7 | 12.0 | Total Sales | 100.0% | 100.0% | 100.0% | 100.0% | 100.0% | 100.0% | |
| Income before income taxes | 946 | 878 | 827 | -7.19% | -5.81% | -6.50% | 1,178 | 1,412 | 1,486 | ||||||||
| Provision for income taxes | 367 | 342 | 320 | -6.81% | -6.43% | -6.62% | 301 | 283 | 266 | Advertising Expenses (USD in millions) | $ 63 | $ 89 | $ 96 | $ 100.8 | $ 105.8 | $ 111.1 | |
| Net income | $ 579 | $ 536 | $ 507 | -7.43% | -5.41% | -6.42% | $ 878 | $ 1,129 | $ 1,220 | ||||||||
| Basic earnings per share | $ 1.57 | $ 1.49 | $ 1.55 | -5.10% | 4.03% | -0.53% | $ 1.61 | $ 1.68 | $ 1.74 | Average square footage per store | - | 37,628 | 37,628 | 37,628 | 37,628 | 37,628 | |
| Weighted average shares outstanding | 367.8 | 358.5 | 326.1 | -2.53% | -9.04% | -5.78% | 306.5 | 288.1 | 270.9 | Average employees per store | - | 190 | 190 | 190 | 190 | 190 | |
| Diluted earnings per share | $ 1.56 | $ 1.48 | $ 1.55 | -5.13% | 4.73% | -0.20% | $ 1.62 | $ 1.70 | $ 1.78 | Average revenue per square foot | $ 990.00 | $ 970.00 | $ 915.00 | $ 915.00 | $ 915.00 | $ 915.00 | |
| Weighted average shares outstanding, diluted basis | 370.5 | 360.8 | 326.9 | -2.62% | -9.40% | -6.01% | 307.3 | 288.8 | 271.5 | Number of Stores | 399 | 431 | 456 | 487 | 521 | 557 | |
| Divideds declared per common share | $ 0.48 | $ 0.52 | $ 0.54 | 8.33% | 3.85% | 6.09% | $ 0.57 | $ 0.61 | $ 0.64 | ||||||||
| Assumptions: | |||||||||||||||||
| Revenue increases will be relative to increase in number of stores. Between 2014-2016 the number of stores increased by an average of 6.9% due to the company's plan to open more stores under the '365' banner. We will assume that the number of stores will continue to increase at that pace for the next 3 years. | |||||||||||||||||
| As of 2016, ABC Firm was producing $915 of revenue per gross square foot of store space. Knowing that each store averages 37,628 square feet of space, I will apply the same revenue rate for additional new stores, keeping in mind that the historical data means the sales are expected to grow 4.5% in 2017 and 5% in 2018 and 2019, due to the addition of new stores. | |||||||||||||||||
| Assume a 0.11% decrease in operating expenses in 2017, per the company's continued plan to reduce operating expenses by $300 million by the end of 2017. Assume a 3% increase in operating expenses for the remaining two forecasted years, as new stores will add to the costs. | |||||||||||||||||
| ABC Firm employed 87,000 employees as of 09/25/2016. Due to the addition of new stores, I am going to assume a 6.9% increase in staff in 2017, 2018 & 2019. | |||||||||||||||||
| Assume a 5% increase in advertising cost YOY, as management has expressed advertising would remain a focus in the coming years. | |||||||||||||||||
| In 2015, ABC Firm announced a new capital allocation strategy, which included a 5-year $500 million revolving credit facility. Therefore, the interest expense is assumed to have the same run rate for 2017-2019. | |||||||||||||||||
| At the end of 2016, ABC Firm announced that they created 5 new positions within the upper management leadership team, including a global vice president in Culinary & Hospitality services. Therefore, there is an assumed increase of 3% in the revenue generated from the prepared foods and bakery segments of the stores. | |||||||||||||||||
| Assume relocation, store closures and lease termination costs continue to drop since the sub-leasing of unused property has been actively pursued by management. | |||||||||||||||||
Best Case Scenario
| ABC Firm Financial Data - Best Case Scenario | |||||||||||||||||
| Consolidated Statements of Operations (USD in millions, except per share amounts) | |||||||||||||||||
| Fiscal years ending September 28, 2016, September 27, 2017, and September 25, 2018 | |||||||||||||||||
| Forecast of Unique Factors | |||||||||||||||||
| 2016 Actual | 2017 Actual | 2018 Actual | 2016 to 2017 % Change | 2017 to 2018 % Change | Avg. % Change | 2019 Forecast | 2020 Forecast | 2021 Forecast | 2016 Actual | 2017 Actual | 2018 Actual | 2019 Forecast | 2020 Forecast | 2021 Forecast | |||
| Sales | $ 14,194 | $ 15,389 | $ 15,724 | 8.42% | 2.18% | 5.30% | $ 17,139 | $ 18,682 | $ 20,363 | Domestic Sales, as % of Total Sales | 96.7% | 96.9% | 97.1% | 97.3% | 97.5% | 97.7% | |
| Cost of goods sold and occupancy costs | 9,150 | 9,973 | 10,313 | 8.99% | 3.41% | 6.20% | 10,952 | 11,631 | 12,353 | International Sales, as % of Total Sales | 3.3% | 3.1% | 2.9% | 2.7% | 2.5% | 2.3% | |
| Gross profit | 5,044 | 5,416 | 5,411 | 7.38% | -0.09% | 3.64% | 5,831 | 6,309 | 6,826 | Total Sales | 100.0% | 100.0% | 100.0% | 100.0% | 100.0% | 100.0% | |
| Selling, general, and adminisrative expenses | 4,032 | 4,472 | 4,477 | 10.91% | 0.11% | 5.51% | 4,432 | 4,654 | 4,887 | ||||||||
| Pre-opening expenses | 67 | 67 | 64 | 0.00% | -4.48% | -2.24% | 68 | 73 | 78 | Prepared Foods & Bakery | 19.2% | 19.0% | 18.9% | 19.8% | 20.8% | 21.9% | |
| Relocation, store closure, and lease termination costs | 11 | 16 | 13 | 45.45% | -18.75% | 13.35% | 11 | 9 | 7 | Other Perishables | 47.6% | 47.5% | 47.6% | 46.8% | 46.0% | 45.2% | |
| Operating income | 934 | 861 | 857 | -7.82% | -0.46% | -4.14% | 1,320 | 1,573 | 1,854 | Total Perishable Sales | 66.8% | 66.5% | 66.5% | 66.6% | 66.8% | 67.1% | |
| Interest expense | - | - | (41) | (41) | (41) | (41) | Non-perishable Sales | 33.2% | 33.5% | 33.5% | 33.4% | 33.2% | 32.9% | ||||
| Investment and other income | 12 | 17 | 11 | 41.67% | -35.29% | 3.19% | 11.3 | 11.7 | 12.0 | Total Sales | 100.0% | 100.0% | 100.0% | 100.0% | 100.0% | 100.0% | |
| Income before income taxes | 946 | 878 | 827 | -7.19% | -5.81% | -6.50% | 1,290 | 1,544 | 1,825 | ||||||||
| Provision for income taxes | 367 | 342 | 320 | -6.81% | -6.43% | -6.62% | 301 | 283 | 266 | Advertising Expenses (USD in millions) | $ 63 | $ 89 | $ 96 | $ 96.0 | $ 96.0 | $ 96.0 | |
| Net income | $ 579 | $ 536 | $ 507 | -7.43% | -5.41% | -6.42% | $ 989 | $ 1,261 | $ 1,559 | ||||||||
| Basic earnings per share | $ 1.57 | $ 1.49 | $ 1.55 | -5.10% | 4.03% | -0.53% | $ 1.61 | $ 1.68 | $ 1.74 | Average square footage per store | - | 37,628 | 37,628 | 37,628 | 37,628 | 37,628 | |
| Weighted average shares outstanding | 367.8 | 358.5 | 326.1 | -2.53% | -9.04% | -5.78% | 306.5 | 288.1 | 270.9 | Average employees per store | - | 190 | 190 | 190 | 190 | 190 | |
| Diluted earnings per share | $ 1.56 | $ 1.48 | $ 1.55 | -5.13% | 4.73% | -0.20% | $ 1.62 | $ 1.70 | $ 1.78 | Average revenue per square foot | $ 990.00 | $ 970.00 | $ 915.00 | $ 942.45 | $ 970.72 | $ 999.85 | |
| Weighted average shares outstanding, diluted basis | 370.5 | 360.8 | 326.9 | -2.62% | -9.40% | -6.01% | 307.3 | 288.8 | 271.5 | Number of Stores | 399 | 431 | 456 | 487 | 521 | 557 | |
| Divideds declared per common share | $ 0.48 | $ 0.52 | $ 0.54 | 8.33% | 3.85% | 6.09% | $ 0.57 | $ 0.61 | $ 0.64 | ||||||||
| Assumptions: Best Case Scenario | |||||||||||||||||
| Revenue increases will be relative to increase in number of stores. Between 2014-2016 the number of stores increased by an average of 6.9% due to the company's plan to open more stores under the '365' banner. We will assume that the number of stores will continue to increase at that pace for the next 3 years - Best case scenario: The rate of the stores will increase as expected, but the revenue per square foot would have a more consistent impact. | |||||||||||||||||
| As of 2016, ABC Firm was producing $915 of revenue per gross square foot of store space. Knowing that each store averages 37,628 square feet of space, the best case scenario would see the revenue rate increase by 3% per square foot per store, including all new stores. | |||||||||||||||||
| Assume a 0.11% decrease in operating expenses in 2017, per the company's continued plan to reduce operating expenses by $300 million by the end of 2017. Best case scenario - the operating expenses increase by only 3% for the remaining two forecasted years. | |||||||||||||||||
| ABC Firm employed 87,000 employees as of 09/25/2016. Due to the addition of new stores, I am going to assume a 6.7% increase in staff in 2017, 2018 & 2019. | |||||||||||||||||
| Assume a 5% increase in advertising cost YOY, as management has expressed advertising would remain a focus in the coming years. Best case scenario - the advertising cost remains constant for 2017-2019. | |||||||||||||||||
| In 2015, ABC Firm announced a new capital allocation strategy, which included a 5-year $500 million revolving credit facility. Therefore, the interest expense is assumed to have the same run rate for 2017-2019. | |||||||||||||||||
| At the end of 2016, ABC Firm announced that they created 5 new positions within the upper management leadership team, including a global vice president in Culinary & Hospitality services. Therefore, there is an assumed increase of 5% in the revenue generated from the prepared foods and bakery segments of the stores. | |||||||||||||||||
| Assume relocation, store closures and lease termination costs continue to drop since the sub-leasing of unused property has been actively pursued by management. | |||||||||||||||||
Worst Case Scenario
| ABC Firm Financial Data - Worst Case Scenario | |||||||||||||||||
| Consolidated Statements of Operations (USD in millions, except per share amounts) | |||||||||||||||||
| Fiscal years ending September 28, 2016, September 27, 2017, and September 25, 2018 | |||||||||||||||||
| Forecast of Unique Factors | |||||||||||||||||
| 2016 Actual | 2017 Actual | 2018 Actual | 2016 to 2017 % Change | 2017 to 2018 % Change | Avg. % Change | 2019 Forecast | 2020 Forecast | 2021 Forecast | 2016 Actual | 2017 Actual | 2018 Actual | 2019 Forecast | 2020 Forecast | 2021 Forecast | |||
| Sales | $ 14,194 | $ 15,389 | $ 15,724 | 8.42% | 2.18% | 5.30% | $ 15,881 | $ 16,040 | $ 16,200 | Domestic Sales, as % of Total Sales | 96.7% | 96.9% | 97.1% | 97.3% | 97.5% | 97.7% | |
| Cost of goods sold and occupancy costs | 9,150 | 9,973 | 10,313 | 8.99% | 3.41% | 6.20% | 10,829 | 11,370 | 11,939 | International Sales, as % of Total Sales | 3.3% | 3.1% | 2.9% | 2.7% | 2.5% | 2.3% | |
| Gross profit | 5,044 | 5,416 | 5,411 | 7.38% | -0.09% | 3.64% | 5,053 | 4,670 | 4,262 | Total Sales | 100.0% | 100.0% | 100.0% | 100.0% | 100.0% | 100.0% | |
| Selling, general, and adminisrative expenses | 4,032 | 4,472 | 4,477 | 10.91% | 0.11% | 5.51% | 4,432 | 4,565 | 4,702 | ||||||||
| Pre-opening expenses | 67 | 67 | 64 | 0.00% | -4.48% | -2.24% | 66 | 66 | 66 | Prepared Foods & Bakery | 19.2% | 19.0% | 18.9% | 18.9% | 18.9% | 18.9% | |
| Relocation, store closure, and lease termination costs | 11 | 16 | 13 | 45.45% | -18.75% | 13.35% | 11 | 9 | 7 | Other Perishables | 47.6% | 47.5% | 47.6% | 47.6% | 47.6% | 47.6% | |
| Operating income | 934 | 861 | 857 | -7.82% | -0.46% | -4.14% | 543 | 30 | (514) | Total Perishable Sales | 66.8% | 66.5% | 66.5% | 66.5% | 66.5% | 66.5% | |
| Interest expense | - | - | (41) | (42) | (43) | (45) | Non-perishable Sales | 33.2% | 33.5% | 33.5% | 33.5% | 33.5% | 33.5% | ||||
| Investment and other income | 12 | 17 | 11 | 41.67% | -35.29% | 3.19% | 11.3 | 11.7 | 12.0 | Total Sales | 100.0% | 100.0% | 100.0% | 100.0% | 100.0% | 100.0% | |
| Income before income taxes | 946 | 878 | 827 | -7.19% | -5.81% | -6.50% | 513 | (2) | (546) | ||||||||
| Provision for income taxes | 367 | 342 | 320 | -6.81% | -6.43% | -6.62% | 301 | 283 | 266 | Advertising Expenses (USD in millions) | $ 63 | $ 89 | $ 96 | $ 105.6 | $ 116.2 | $ 127.8 | |
| Net income | $ 579 | $ 536 | $ 507 | -7.43% | -5.41% | -6.42% | $ 212 | $ (285) | $ (812) | ||||||||
| Basic earnings per share | $ 1.57 | $ 1.49 | $ 1.55 | -5.10% | 4.03% | -0.53% | $ 1.49 | $ 1.43 | $ 1.37 | Average square footage per store | - | 37,628 | 37,628 | 37,628 | 37,628 | 37,628 | |
| Weighted average shares outstanding | 367.8 | 358.5 | 326.1 | -2.53% | -9.04% | -5.78% | 306.5 | 288.1 | 270.9 | Average employees per store | - | 190 | 190 | 190 | 190 | 190 | |
| Diluted earnings per share | $ 1.56 | $ 1.48 | $ 1.55 | -5.13% | 4.73% | -0.20% | $ 1.49 | $ 1.43 | $ 1.37 | Average revenue per square foot | $ 990.00 | $ 970.00 | $ 915.00 | $ 878.40 | $ 843.26 | $ 809.53 | |
| Weighted average shares outstanding, diluted basis | 370.5 | 360.8 | 326.9 | -2.62% | -9.40% | -6.01% | 307.3 | 288.8 | 271.5 | Number of Stores | 399 | 431 | 456 | 472 | 488 | 506 | |
| Divideds declared per common share | $ 0.48 | $ 0.52 | $ 0.54 | 8.33% | 3.85% | 6.09% | $ 0.56 | $ 0.58 | $ 0.60 | ||||||||
| Assumptions: Worst Case Scenario | |||||||||||||||||
| Revenue increases will be relative to increase in number of stores. Between 2014-2016 the number of stores increased by an average of 6.9% due to the company's plan to open more stores under the '365' banner. We will assume that the number of stores will continue to increase at that pace for the next 3 years - Worst case scenario: The rate of the stores will not increase as expected, averaging only 3.5% instead. | |||||||||||||||||
| As of 2016, ABC Firm was producing $915 of revenue per gross square foot of store space. Knowing that each store averages 37,628 square feet of space, the worst case scenario would see the revenue rate decrease to less than $900 per square foot per store. | |||||||||||||||||
| Assume a 0.11% decrease in operating expenses in 2017, per the company's continued plan to reduce operating expenses by a run rate of $300 million by the end of 2017. Worst case scenario - the operating expenses would not continue to decrease beyond 2017 and would instead increase by an average 3% due to the addition of new stores. | |||||||||||||||||
| ABC Firm employed 87,000 employees as of 09/25/2016. Due to the addition of new stores, I am going to assume a 3.5% increase in staff in 2017, 2018 & 2019, since in the worst cast scenario new stores only opened at a rate of 3.5%. | |||||||||||||||||
| Assume a 5% increase in advertising cost YOY, as management has expressed advertising would remain a focus in the coming years. Worst case scenario - the advertising costs increases 10% instead of the anticipated 5% over the next three years. | |||||||||||||||||
| In 2015, ABC Firm announced a new capital allocation strategy, which included a 5-year $500 million revolving credit facility. Therefore, the interest expense is assumed to have the same run rate for 2017-2019. Worst case scenario - the interest rate increases in 2017, 2018 and 2019 by 3%. | |||||||||||||||||
| At the end of 2016, ABC Firm announced that they created 5 new positions within the upper management leadership team, including a global vice president in Culinary & Hospitality services. Therefore, in the worst case scenario, there is no increase in the revenue generated from the prepared foods and bakery segments of the stores, vs. the total sales. | |||||||||||||||||
| Assume relocation, store closures and lease termination costs continue to drop since the sub-leasing of unused property has been actively pursued by management. | |||||||||||||||||
