PLEASE SHOW HOW TO DO AT LEAST THE FORMULAS FOR EACH BOX
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) Create a 'baseline' scenario by setting both D112 and D132 to 2%. Real Estate Additions - Real Estate Stock previous year multiplied by Real Estate Growth Rate current year Previous 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. Construction Equipment Additions Replacements Sales Stock Sales Growth Real Estate Growth Additions Rate 2% Stock 1,000 Change in Additions 1.0 1.0 0 0% 0.9 0.9 0.8 0.8 0.7 0.7 0.6 0.6 0.5 0.5 0.4 0.4 0.3 Year 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2012 2013 we 2014 2015 2016 2017 2018 2019 2020 2021E 2022E 2023E 2024E 2025E 2026E 2027E 2028E 2029E 2030E 2031E 2029 2032E 2033E 2004 2034E 2035E 2036E 2037E 2038E 2039E 2040E 2041E 0.3 0% 0% 0% 0% 0% 0% 0% 0% ned 0% 0% 0% 0% - 0% 0% 098 0% 0% 0% 0% - 2% 0% 0% 0% 0% 0% % 0% % 0% 09 0.2 0.2 0.1 0.0 091 % 0% 0% 0% nor 0% 0% 0% 02: % 0% 0% 0% 0% In the baseline scenario prowth remains flat at 20 annually. 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) Create a 'baseline' scenario by setting both D112 and D132 to 2%. Real Estate Additions - Real Estate Stock previous year multiplied by Real Estate Growth Rate current year Previous 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. Construction Equipment Additions Replacements Sales Stock Sales Growth Real Estate Growth Additions Rate 2% Stock 1,000 Change in Additions 1.0 1.0 0 0% 0.9 0.9 0.8 0.8 0.7 0.7 0.6 0.6 0.5 0.5 0.4 0.4 0.3 Year 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2012 2013 we 2014 2015 2016 2017 2018 2019 2020 2021E 2022E 2023E 2024E 2025E 2026E 2027E 2028E 2029E 2030E 2031E 2029 2032E 2033E 2004 2034E 2035E 2036E 2037E 2038E 2039E 2040E 2041E 0.3 0% 0% 0% 0% 0% 0% 0% 0% ned 0% 0% 0% 0% - 0% 0% 098 0% 0% 0% 0% - 2% 0% 0% 0% 0% 0% % 0% % 0% 09 0.2 0.2 0.1 0.0 091 % 0% 0% 0% nor 0% 0% 0% 02: % 0% 0% 0% 0% In the baseline scenario prowth remains flat at 20 annually
