1.Ensure that the Facilities worksheet is active. Use Goal Seek to reduce the monthly payment in cell B6 to the optimal value of $6000. Complete this task by changing the Loan amount in cell E6.

2.Create the following three scenarios using Scenario Manager. The scenarios should change the cells B7, B8, and E6.

Good
B7 = .0325
B8 = 5
E6 = 275000

Most Likely
B7 = .057
B8 = 5
E6 = 312227.32

Bad
B7 = .0700
B8 = 3
E6 = 350000

Create a Scenario Summary Report based on the value in cell B6. Format the new report appropriately.

3.Ensure that the Facilities worksheet is active. Enter a reference to the beginning loan balance in cell B12 and enter a reference to the payment amount in cell C12.

4 Enter a function in cell D12, based on the payment and loan details, that calculates the amount of interest paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references

5.Enter a function in cell E12, based on the payment and loan details, that calculates the amount of principal paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references.

6.Enter a formula in cell F12 to calculate the remaining balance after the current payment. The remaining balance is calculated by subtracting the principal payment from the balance in column B.

7 Enter a function in cell G12, based on the payment and loan details, that calculates the amount of cumulative interest paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references.

8.Enter a function in cell H12, based on the payment and loan details, that calculates the amount of cumulative principal paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references.File Home Insert Page Layout Formulas Data Review & Share É E - View Help Tell me what you want to do Connections Clear Le P

File Home Insert Page Layout Formulas Data Review & Share É E – View Help Tell me what you want to do Connections Clear “Le Properties Refresh Ed Sort All Edit Links Advanced Connections Sort & Filter 1 S a m show Detail E Data Analysis Reapply ? Solver LA L From From ccess Web E Existing Connections L LO From From Other Text Sources Get External Data Show Queries From Table New Query – Le Recent Sources Get & Transform Sort Filter Consolidate Relationships Text to Columns Flash Fill Group Ungroup Subtotal Remove Data Duplicates Validation Data Tools What If Forecast Analysis * Sheet Forecast Outline Analyze 11 : A X for Remaining Balance B C D Beta Manufacturing E F G H I J K L M N O P Q R S T U V W X . r . Facility Amortization Table Payment Details Payment $4,972.00 APR 3.25% Years 5 Pmts per Year 12 Loan Details Loan $275,000.00 Periodic Rate 0.271% # of Payments 60 Payment Number Beginning Payment Balance Amount Principal Interest Paid Repayment Remaining Cumulative Balance Interest Cumulative Principal Insurance Scenario Summary Facilities Sales Inventory



Source link

Leave a Reply

Your email address will not be published. Required fields are marked *