answer those question please

Developing a Model of Real Estate and Construction Equipment Enter formulas into the green boxes below to create a model. Real Estate (Real Estate typically has a 40+ year useful life so that we will ignore replacement) Real Estate Stock- Real Estate Stock previous year plus Real Estate Additions current year (set to index of 1,000 in 1999) Real Estate Growth Rate - Annual Growth Rate of Real Estate (approximated by GNP growth) Real Estate Additions - Real Estate Stock previous year multiplied by Real Estate Growth Rate current year Change in Real Estate Additions - Real Estate Additions current year minus maximum of Real Estate Additions previous 20 years Construction Equipment (measured in equivalent units of Real Estate Additions) Construction Equipment Stock - Construction Equipment Stock previous year plus Construction Equipment Additions current year Construction Equipment Additions - maximum of Real Estate Change in Additions current year and zero (do not reduce stock if underutilized) Construction Equipment Replacement - As construction equipment has a 20 year useful life, Construction Equipment Replacement is equal to Construction Equipment Sales of 20 years ago Construction Equipment Sales - the sum of Construction Equipment Additions plus Construction Equipment Replacements The cells will turn red if the formula you enter in the cell is incorrect. Real Estate Construction Equipment Year Stock Growth Additions Change in Stock Additions Replacements Sales Sales Growth 1999 1,000 Rate Additions 2000 1.0 2001 1.0 2002 0.9 2003 0.9 2004 0.8 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020E 2021E 2022E 2023E 2024E 2025E 2026E 2027E 2028E 2029E 2030E 2031E 2032E 2033E 2034E 2035E 2036E 2037E 2038E 2039E 2040E In the baseline scenario in which growth remains flat at 2% annually The Stock of Construction Equipment increases 'monotonically'. Amonotonically increasing function is one that increases as x does for all realx In other words, the function increases steadily without ever declining asx increases. DATA ANALYSIS Seasonality DATA Seasonality CASE Seasonality EXCEL Statistical Functions EXCEL Workspace A CASE Capital EquipYou are going to forecast the impact of different potential Amazon strategies on USPS' competitive business. The USPS does not disclose the details of its agreement with Amazon. Financial analysts often have to work with limited information. Below is a 'baseline' model assuming that: Revenues of the competitive business are currently $6.8 billion and grow at 11% annually Variable Costs related to the competitive business is $1.6 billion and grow at 11% annually. The competitive business currently loses $.8 billion on the competitive business based on the fact that USPS loses 12% on its overall revenues. The following values are calculated based on accounting relationships: Allocated Fixed Costs = Fully Absorbed Costs - Variable Costs Fully Absorbed Costs = Revenues - Profit Profit = Revenues - Fully Absorbed Cost Marginal Contribution = Revenues - Variable Costs The Marginal Contribution represents how much the companies profits would decline were the company to lose the business (assuming the fixed costs cannot be eliminated). 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 Revenues 6.8 7.5 8.4 9.3 10.3 11.5 12.7 14.1 15.7 17.2 Variable Costs 1.6 1.8 2.0 2.2 2.4 2.7 3.0 3.3 3.7 4.1 Allocated Fixed Costs 5.0 6.7 7.4 8.2 9.1 10.1 11.3 12.5 13.9 15.4 Fully Absorbed Costs 7.6 8.5 9.4 10.4 11.6 12.8 14.2 15.8 17.6 19.5 Profit 0.8 -0.9 -1.0 -1.1 -1.2 -1.4 -1.5 -1.7 -1.9 -2.2 Marginal Contribution 5.2 5.8 6.4 7.1 7.9 8.8 9.7 10.8 12.0 13.1 As you can see, the 'baseline' scenario results in the competitive business of USPS remaining unprofitable on a fully absorbed cost basis but generating significant marginal contribution. Assuming, $1.6 billion Variable Costs in 2019, enter the value (not a formula) of Marginal Contribution in 2028. While we know that the legally required minimum revenue of the competitive business is $1.6 billion, we do not know what Variable Costs are. Change the Variable Costs in the green cell above to $4 billion to perform a sensitivity analysis of this unknown value. Assuming, $4 billion Variable Costs in 2019, enter the value (not a formula) of Marginal Contribution in 2028 The economics of USPS is very sensitive to the assumption on Variable Costs. Return the 2019 Variable Costs above to $1.6 billion. We will expand the above model to isolate and forecast the size and profitability of USPS's services to Amazon. Let's 'guesstimate' that: DATA ANALYSIS Seasonality DATA Seasonality CASE Seasonality EXCEL Statistical Functions EXCEL Workspace CASE Capital Equip CASE USPS) 1 We will expand.the above model to isolate and forecast the size and profitability of USPS's services to Amazon. Let's 'guesstimate' that: ; Amazon currently represents 30% of USPS competitive revenues. Amazon is currently receiving a 25% discount resulting in USPS's variable cost per dollar of revenue being 33% greater (1/(1-25%)=1.33) than for other customers. USPS' fixed costs per dollar of revenues for Amazon are inflated by 33% assuming fixed costs are allocated based on volume not revenues. 7 Let's assume that the impact of USPS pricing strategy to Amazon will occur as a step change at the beginning of the next calendar year. Amazon 4 Revenues 1.7 1.9 21 23 26 29 3.2 35 39 43 ) Variable Costs 05 0.6 0.7 0.7 08 09 10 11 12 14 Allocated Fixed Costs 15 17 19 21 23 25 28 31 35 3.8 Fully Absorbed Costs 20 23 25 28 31 34 38 42 47 4 Profit (0.3) (0.4) (0.4) (0.5) (0.5) (0.6) (0.6) (0.7) (0.8) (0.9) Marginal Contribution 12 13 14 16 18 20 22 24 27 7 The results of the model are driven by entering values for the following two factors: The percent of the 30% of USPS's competitive business revenues that Amazon currently represents that USPS would lose if USPS retains its current pricing. B Percent of Revenues from Amazon Lost The additional discount that USPS would need to offer Amazon to retain the current share of Amazon's deliveries that USPS represents. | Additional Discount Provided to Amazon : Forecast the following two scenarios (Harvest and Defend) by applying the above model Enter the appropriate values in the two green cells immediately above. Harvest Strategy 4 In a'Harvest' strategy a company retains its pricing and sacrifices sales. ; Assume USPS will not increase its discount to Amazon but will ultimately lose 50% of it current share. Percent of Revenues from Amazon Lost =50%, Additional Discount Provided to Amazon = 0%. k In the green cells below enter the values (not a formula) of the Profit and Marginal Contribution based on this hypothesized scenario. 4 Profit Marginal Contribution Defend Strategy Y In a'Defend' strategy a company reduces its pricing and retains sales. Assume an incremental 30% discount will avoid any additional loss of deliveries to Amazon internal operations. Harvest Strategy In a 'Harvest' strategy a company retains its pricing and sacrifices sales. Assume USPS will not increase its discount to Amazon but will ultimately lose 50% of it current share. Percent of Revenues from Amazon Lost = 50%, Additional Discount Provided to Amazon = 0%. In the green cells below enter the values (not a formula) of the Profit and Marginal Contribution based on this hypothesized scenario. Profit Marginal Contribution Defend Strategy In a 'Defend' strategy a company reduces its pricing and retains sales. Assume an incremental 30% discount will avoid any additional loss of deliveries to Amazon internal operations. Percent of Revenues from Amazon Lost = 0%, Additional Discount Provided to Amazon = 30%. In the green cells below enter the values (not a formula) of the Profit and Marginal Contribution based on this hypothesized scenario. Profit Marginal Contribution If you were the Postmaster General which strategy would you select