Please help with the calculations part!!! The question has already been answered and answer is provided below, but Chegg expert didn't provide any solutions for the part that mentions:

"Develop formulas for each of the other columns (B to H) to calculate the values based on the input data from the "Inputs and Results" sheet. This will require financial calculations, such as present value, future value, and annuity calculations, considering factors like the client's gender and desired withdrawal."

Please let me know what formulas to use for each part and how to calculate it showing excel formulas. Please provide help with the rest of the assignment where solutions are unclear. use CY22 figure of 4.5%. Thanks

Salwid-Fin, a financial planner, has hired you to develop a spreadsheet they will use with their clients. Theyve noticed that many of their clients ask about the consequences of withdrawing money from their super funds well before retirement. They want a spreadsheet that demonstrates these consequences to their clients. Broadly, theyd like to show their clients a graph with seven curves on it: one, the clients current super balance, two, the amount of super the client wants to withdraw, three, the fall in clients annual retirement income stream (henceforth, RIS) in retirement, four, the target RIS the client is aiming for, five, the target capital the client requires at retirement (the lump sum required to fund the RIS), six, the annual contributions required to hit the target RIS/capital if the super withdrawal goes ahead, and seven, the annual contribution to hit the target RIS/capital if no super is taken out now. To achieve these objectives, you have been asked to develop the following. Develop a spreadsheet with at least two sheets. One sheet, titled Inputs and Result consists of the following.

1. The Salwid-Fin logo

2. The clients current age (an integer, from 18 to 64). Assume that it is the clients birthday on the date he or she sees you.

3. The clients gender (M or F)

4. The clients current super balance (in dollars)

5. The clients desired withdrawal (in dollars). Assume the withdrawal will be made today, on the clients birthday.

6. Desired annual income between ages 65 to 85, inclusive, for males, assumed to be paid at the end of every year (so the first payment is at age 66; the last payment is at age 85). This is the clients desired, or target, RIS. For females, work with a payment period from age 65 to 87 (inclusive).

7. CY22, the Australian 10-year government bond yield for calendar year 2022 (i.e., the 10-year government bond yield on 31 December 2022, a j2 rate). We will adjust this rate, by adding 170 basis points to it, and assume this is the (constant) level of interest rates from the current time into the foreseeable future.

To create the desired spreadsheet for Salwid-Fin, follow these steps in your preferred spreadsheet software (e.g., Microsoft Excel, Google Sheets, or LibreOffice Calc):

Create a new spreadsheet file and name it "Salwid-Fin Super Fund Withdrawal Consequences".

Set up two sheets within the file. Name the first sheet "Inputs and Results" and the second sheet "Calculations and Graphs".

On the "Inputs and Results" sheet, arrange the required input fields as follows:

A1: Insert the Salwid-Fin logo (you can insert an image or use text as a placeholder if you don't have the actual logo).

A3: "Client's current age:" B3: [enter client's age]

A4: "Client's gender:" B4: [enter 'M' or 'F']

A5: "Current super balance:" B5: [enter client's current super balance]

A6: "Desired withdrawal:" B6: [enter client's desired withdrawal]

A7: "Desired annual income (RIS):" B7: [enter client's target RIS]

A8: "CY22 Australian 10-year government bond yield:" B8: [enter the bond yield]

A9: "Adjusted interest rate:" B9: =B8+0.017 (Add 170 basis points to the bond yield)

On the "Calculations and Graphs" sheet, create columns for each of the seven curve variables:

A1: "Age" B1: "Current Super Balance" C1: "Amount of Super Withdrawal" D1: "Fall in RIS" E1: "Target RIS" F1: "Target Capital" G1: "Annual Contribution with Withdrawal" H1: "Annual Contribution without Withdrawal"

Populate the "Age" column (column A) with ages from the client's current age to 64.

Develop formulas for each of the other columns (B to H) to calculate the values based on the input data from the "Inputs and Results" sheet. This will require financial calculations, such as present value, future value, and annuity calculations, considering factors like the client's gender and desired withdrawal.

Create a line graph using the data from columns A to H, with age on the x-axis and dollar values on the y-axis. This graph will display the seven curves as requested by Salwid-Fin.

Format the graph for easy readability, including labels for the curves and axis titles.

Once completed, the spreadsheet will allow Salwid-Fin to input their client's information and generate a graph showing the consequences of withdrawing from their super fund before retirement.

Step 2/2

I'll provide a more detailed, step-by-step explanation of how to create the spreadsheet.

Step 1: Create a new spreadsheet

Open your preferred spreadsheet software (e.g., Microsoft Excel, Google Sheets, or LibreOffice Calc).

Create a new spreadsheet file.

Name the file "Salwid-Fin Super Fund Withdrawal Consequences".

Step 2: Set up two sheets

Name the first sheet "Inputs and Results".

Name the second sheet "Calculations and Graphs".

Step 3: Set up input fields on the "Inputs and Results" sheet

Add the following fields and labels: A1: Insert the Salwid-Fin logo (use text or an image placeholder if you don't have the actual logo). A3: "Client's current age:" B3: (leave this cell empty for user input) A4: "Client's gender:" B4: (leave this cell empty for user input) A5: "Current super balance:" B5: (leave this cell empty for user input) A6: "Desired withdrawal:" B6: (leave this cell empty for user input) A7: "Desired annual income (RIS):" B7: (leave this cell empty for user input) A8: "CY22 Australian 10-year government bond yield:" B8: (leave this cell empty for user input) A9: "Adjusted interest rate:" B9: =B8+0.017 (formula to add 170 basis points to the bond yield)

Step 4: Set up columns on the "Calculations and Graphs" sheet

Create column headers as follows: A1: "Age" B1: "Current Super Balance" C1: "Amount of Super Withdrawal" D1: "Fall in RIS" E1: "Target RIS" F1: "Target Capital" G1: "Annual Contribution with Withdrawal" H1: "Annual Contribution without Withdrawal"

Step 5: Populate the "Age" column

Starting at A2, list ages from the client's current age to 64.

Step 6: Develop formulas for the other columns

Calculate values in columns B to H based on input data from the "Inputs and Results" sheet. This requires financial calculations like present value, future value, and annuity calculations, factoring in gender and desired withdrawal. You may need to research specific formulas for these calculations, as they can be quite complex and vary depending on client input.

Step 7: Create a line graph

Select the data in columns A to H.

Insert a line graph to display the seven curves.

Ensure age is on the x-axis, and dollar values are on the y-axis.

Step 8: Format the graph

Improve the graph's readability by adding labels for each curve and axis titles.

The completed spreadsheet will allow Salwid-Fin to input client data and generate a graph demonstrating the consequences of withdrawing from their super fund before retirement.

Explanation:

To create a spreadsheet for Salwid-Fin that demonstrates the consequences of withdrawing from a super fund before retirement, follow these steps:

Create a new spreadsheet with two sheets: "Inputs and Results" and "Calculations and Graphs".

On the "Inputs and Results" sheet, set up input fields for the client's age, gender, current super balance, desired withdrawal, desired annual income (RIS), and the Australian 10-year government bond yield. Calculate an adjusted interest rate by adding 170 basis points to the bond yield.

On the "Calculations and Graphs" sheet, create columns for age, current super balance, amount of super withdrawal, fall in RIS, target RIS, target capital, annual contribution with withdrawal, and annual contribution without withdrawal.

Populate the "Age" column with ages from the client's current age to 64.

Develop formulas for the other columns (B to H) based on the input data from the "Inputs and Results" sheet, using financial calculations like present value, future value, and annuity calculations.

Create a line graph using the data from columns A to H, displaying the seven curves as requested by Salwid-Fin.

Format the graph for readability, including labels for the curves and axis titles.

The completed spreadsheet will help Salwid-Fin clients visualize the impact of withdrawing from their super fund before retirement.

Final answer

To create the Salwid-Fin Super Fund Withdrawal Consequences spreadsheet:

Set up input fields for age, gender, super balance, desired withdrawal, RIS, and bond yield, and adjust interest rate.

Create columns for age, super balance, withdrawal, RIS, target RIS, target capital, annual contributions with and without withdrawal.

Use formulas to calculate column values, create a line graph to display the data, and format the graph for readability.