Question

3. Use a spreadsheet for evaluation of the multiple alternatives provided below. Use incremental B/C analysis. These alternatives are relative to the application of nanotechnology and the use of thin-film solar panels applied to houses to reduce the dependency on fossil-fuel generated electrical energy. A community of 400 new all- electric public housing units will utilize the technology as anticipated proof that significant reductions in overall utility costs can be attained over the expected 15-year life of the housing. The table below details the three bids received. Also included are estimated annual electricity usage costs for the community with the panels in use, and the PW of backup systems required in case of panel failures. Use i = 5% per year to select the best bid. Turn in the spreadsheet with calculations and the best alternative highlighted in pink.

Answer 1

Find the below calculation done in excel:

Incremental Benefit Cost Analysis |Information provided in question Total Initial Backup Sys Annual Alternatives Initial Cost Annual Utility Bill and Backup Cost Maintenace Cost $960,000.00 G $2,400,000.00 $650,000.00 $3,050,000.00 $500,000.00 $1,850,000.00 $750,000.00 $2,600,000.00 $650,000.00 $1,000,000.00 н $6,150,000.00 $950,000.00 $7,100,000.00 $450,000.00 $550,000.00 Rate(%)= Period(Years) 15 Sorting the alternative on basis of initial cost+bactup system cost Benefit Cost Total Initial Backup Sys Annual Alternatives Initial Cost and Backup Annual Utility Bill Cost Maintenace Cost $6,150,000.00 $950,000.00 $7,100,000.00 $450,000.00 $550,000.00 C $2,400,000.00 $650,000.00 $3,050,000.00 $500,000.00 $960,000.00 |$750,000.00 $2,600,000.00 $650,000.00 н $1,850,000.00 $1,000,000.00 Present Worth PV(rate,nper,pmt) So rate =5%, nper= 15 years, pmt= payment made/received during the year Present Worth of(using the above excel formula).. Note: PW of initial and backup cost is given in question, so we just taking them as it is by adding both Benefit Cost Total Initial and Annual Utility Annual Alternatives Backup Cost $7,100,000.00 $3,050,000.00 Maintenace Bill $90,000.00 $100,000.00 $110,000.00 $192,000.00 G н $2,600,000.00 $130,000.00 $200,000.00

Incremental Analysis Difference in Difference in BC Ratio Diff in Difference in Initial and Backup Benefits/diff in Annual Interpretation Benefit Cost Maintenance total cost ($10,000.00) (S30,000.00) C vs G $4,050,000.00 ($82,000.00) (S0.0203) BC<1 here so go with alternative G $450,000.00 (S8,000.00) (S0.0190) BC<1 here so go with alternative H finally G vs H