Question

Perform the comparison calculations and work in MS Excel in order to determine the “optimal” choices. The “optimal” choices will be based on a death between 62 years and 1 month and 100 years. Note, your spreadsheets should be created in such a way that you could change only a few cells and the analysis could be recalculated. This will be helpful for future deliverables. Deliverables: Excel Spreadsheet with Cash Flow Table and breakeven analysis for each of the three alternatives for each of the analysis types.

Answer 1

I will guide you step-by-step in creating an Excel spreadsheet to perform the comparison calculations and conduct the breakeven analysis for each of the three alternatives.

Step-by-step explanation:

Step 1: Open a new Excel spreadsheet.

Step 2: Set up the Cash Flow Table:

a. Create the following column headers: Age Cash Inflow Cash Outflow Net Cash Flow.

b. In the "Age" column enter the ages from 62 years and 1 month to 100 years.

c. In the "Cash Inflow" column enter the cash inflow generated by each alternative.

d. In the "Cash Outflow" column enter the cash outflow associated with each alternative.

e. In the "Net Cash Flow" column subtract the cash outflow from the cash inflow for each alternative.

Step 3: Calculate the Cumulative Net Cash Flow:

a. In the next column create a header for "Cumulative Net Cash Flow."

b. In the first row under this column enter the formula "=SUM($D$2:D2)" and press Enter.

c. Drag the formula down to calculate the cumulative net cash flow for each age.

Step 4: Perform the Breakeven Analysis:

a. In the next column create a header for "Breakeven Analysis."

b. In the first row under this column enter the formula "=ABS(E2)" to calculate the absolute value of the net cash flow.

c. Drag the formula down to calculate the breakeven analysis for each age.

Step 5: Format the spreadsheet as desired adding titles adjusting column widths and applying the necessary formatting.

Step 6: Repeat steps 2-5 for each of the three alternatives.

Step 7: Save the Excel spreadsheet.