Prior to the creation of government-wide financial statements, the City of Loveland did not report the cost of its infrastructure assets. Now city officials are attempting to determine reported values for major infrastructure assets that were obtained prior to the preparation of these statements. The chief concern is determining a value for the citys hundreds of miles of roads that were built at various times over the past several decades. The city assumes each road will last for 50 years (depreciation is 2 percent per year). As of December 31, 2020, city engineers believed that one mile of new road would cost $2.3 million. For convenience, each road is assumed to have been acquired as of January 1 of the year in which it was put into operation. Officials have done some investigation and believe that the cost of constructing a mile of road has increased by 8 percent each year over the past 30 years.

Required

Build a spreadsheet to determine the value that should now be reported for each mile of road depending on the year it was put into operation. For example, what reported value should be disclosed in the government-wide financial statements for 10 miles of roads put into operation on January 1, 1999?

Please include the spreadsheet created for all years.