Open Sierra Pacific 02.xlsx Start File If The Workbook Opens ✓ Solved

Open Thesierrapacific 02xlsxstart File If The Workbook Opens Inprote

Open the SierraPacific-02.xlsx start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it. Set range names for the workbook.

Select the Student Loan sheet, and select cells B5:C8. Click the Create from Selection button on the Formulas tab, Defined Names group. Verify that the Left column box in the Create Names from Selection dialog box is selected. Deselect the Top row box if it is checked and click OK. Select cells E5:F7.

Repeat the previous step to create range names for this selection. Click the Name Manager button on the Formulas tab, Defined Names group, to view the names in the Name Manager dialog box. Notice that the cell references are absolute. Click Close.

Enter a PMT function. Select cell C8. Click the Financial button on the Formulas tab, Function Library group, and select PMT. Click the Rate box and click cell C7. The range name Rate is substituted and is an absolute reference. Type /12 immediately after Rate to divide by 12 for monthly payments. Click the Nper box and click cell C6. The substituted range name is Loan_Term. Type *12 after Loan_Term. Click the Pv box and type a minus sign (-) to set the argument as a negative amount. Click cell C5 (Loan_Amount) for the pv argument. Leave the Fv and Type boxes empty. Click OK. The payment for a loan at this rate is $186.43, shown as a positive value. Verify or format cell C8 as Accounting Number Format to match cell C5.

Create a total interest formula. Click cell F5 (Total_Interest). This value is calculated by multiplying the monthly payment by the total number of payments to determine total outlay. From this amount, subtract the loan amount. Type =, click cell C8 (the Payment), type , and click cell C6 (Loan_Term). Type 12 to multiply by 12 for monthly payments. Type - immediately after 12 to subtract. Click cell C5 (the Loan_Amount). The formula is Payment Loan_Term * 12 – Loan_Amount. Parentheses are not required because the operations are done from left to right. Press Enter. The result is $1,185.81.

Create the total principal formula and the total loan cost. Select cell F6 (Total_Principal). This value is calculated by multiplying the monthly payment by the total number of payments. From this amount, subtract the total interest. Type =, click cell C8, type , click cell C6, type 12, type - , and click cell F5 (Total_Interest). Press Enter. Total principal is the amount of the loan. Click cell F7, the Total_Cost of the loan. This is the total principal plus the total interest. Type =, click cell F5, type +, click cell F6, and press Enter.

Set order of mathematical operations to build an amortization schedule. Click cell B13. The beginning balance is the loan amount. Type =, click cell C5, and press Enter. Format the value as Accounting Number Format. Select cell C13.

The interest for each payment is calculated by multiplying the balance in column B by the rate divided by 12. Type =, click cell B13, type * ( and click cell C7, then type / 12). Parentheses ensure division is performed first. Press Enter and format the results as Accounting Number Format. Select cell D13. The portion of the payment that is applied to the principal is calculated by subtracting the interest portion from the payment. Type =, click cell C8, type - , click cell C13, and press Enter. The total payment in cell E13 is the sum of interest and principal portions: type =, click cell C13, type + , click cell D13, and press Enter. The ending balance is calculated in F13 by subtracting the principal payment from the beginning balance: type =, click cell B13, type - , click cell D13, and press Enter, verifying the remaining balance.

Fill formulas for subsequent payments. Select cells A13:A14, then drag the fill handle down to row 72 to set 60 payments for a five-year loan. Select cell B14, type =, click cell F13, and press Enter. Double-click the fill handle to copy formulas down to row 72. Once complete, the balance in row 72 should reach zero, indicating the loan is paid off. Scroll through to verify all formulas are correctly filled and that the amortization schedule is complete.

On the Fees & Credit sheet, select cell F7. Enter a formula multiplying credit hours (cell C7) times the number of sections (cell D7) times the fee (cell E7): type =, click cell C7, type , click cell D7, type , click cell E7, and press Enter. Double-click the fill handle to copy the formula down column F. Ensure cells F7:F18 are formatted as Currency. Set a bottom border on cell F18.

Use SUMIF to calculate total fees by department. Select cell C26. On the Formulas tab, choose SUMIF. In the Range box, select B7:B18. Press F4 to make it absolute. In the Criteria box, select cell B26. In the Sum_range box, select F7:F18 and press F4. Click OK. The total fees for the department appear in C26. Copy the formula down to C29, preserving borders. Format C26:C29 as Currency.

Use SUMPRODUCT to trace and correct errors. Select D26, click the Formulas tab, select SUMPRODUCT. In the Array1 box, select C7:C9 (credit hours), and in the Array2 box, select D7:D9 (sections). Confirm with OK. The total credit hours are displayed. Use Trace Error in D26 to evaluate the formula, ignore the error, then copy the corrected formula down to D29.

Insert the current date via a function. Select cell F20, type =, and press Tab to accept the default function, then press Enter. Return to the top of the sheet with Ctrl+Home.

On a new sheet, name the sheet “Range Names”. Paste range names from the Name dialog. Use F3 to open the Paste Name dialog box; click the Paste List button. Auto-fit columns A and B. Save and close the workbook.

Sample Paper For Above instruction

Introduction

Creating a well-structured Excel workbook for financial analysis, including setting range names, using formulas like PMT and SUMIF, and building amortization schedules, is essential for accurate financial management and reporting. This process leverages Excel's powerful functions and tools to streamline calculations and enhance data organization, demonstrating advanced competence in spreadsheet modeling.

Step 1: Preparing the Workbook and Naming Ranges

Begin by opening the SierraPacific-02.xlsx file and enabling editing if it opens in Protected View. Save the file with your name included in the filename. Navigate to the Student Loan sheet and select the range B5:C8.

Using the Create from Selection feature under the Formulas tab, define names for this range, ensuring only the Left column is selected to establish meaningful range names. Repeat this process for the range E5:F7, creating additional range names for later calculations. Access the Name Manager to verify that the references are absolute, which prevents them from changing when copying formulas.

Step 2: Calculating Monthly Loan Payments with PMT

To calculate the monthly payment, insert the PMT function in cell C8. Select cell C8, then choose the PMT function from the Financial library. Reference the rate (cell C7), divide by 12 for monthly payments, and multiply the loan term (cell C6) by 12 to get total payments. Enter the loan amount (cell C5) as a negative value to reflect cash outflow. The computed monthly payment appears as $186.43.

Step 3: Calculating Total Interest, Principal, and Loan Cost

In cell F5, compute total interest paid over the life of the loan: multiply the monthly payment by total payments, subtract the original loan amount. In cell F6, calculate total principal paid, being the total of all payments minus total interest. Sum these values in F7 to determine the total cost of the loan.

Step 4: Building the Amortization Schedule

Start with the beginning balance in row 13 equal to the loan amount. For each subsequent row, calculate the interest by multiplying the previous balance by the monthly interest rate. Deduct this interest from the monthly payment to find the principal portion. Update the ending balance by subtracting the principal payment from the previous balance.

Fill down these formulas to complete the schedule for 60 payments (assuming a 5-year loan). Ensure the balance reaches zero at the end of the schedule to verify accuracy.

Step 5: Additional Calculations on Fees and Departmental Data

On the Fees & Credit sheet, compute total fees per course by multiplying credit hours, sections, and fee rate. Use SUMIF to aggregate total fees by department, referencing department codes and fee totals. Use SUMPRODUCT to calculate the total credit hours offered by specific departments, ensuring the formulas are correctly referencing ranges and handling errors appropriately.

Step 6: Final Touches and Exporting Data

Insert the current date into the workbook dynamically using the TODAY or NOW function. Copy all defined range names to a new sheet named "Range Names" for documentation. Auto-fit columns for better readability then save and close the workbook.

Conclusion

This process demonstrates proficiency in Excel functions—such as creating named ranges, using financial formulas, building amortization schedules, and employing aggregate functions like SUMIF and SUMPRODUCT—and highlights the importance of precise cell referencing and formula validation. Mastery of these techniques enhances the accuracy and efficiency of financial analysis within spreadsheets, essential skills for financial professionals.

References

• Excel University. (2020). Mastering Excel Financial Functions. Excel University.
• Walkenbach, J. (2018). Excel Bible, 3rd Edition. Wiley.
• Microsoft Support. (2023). Financial functions in Excel. Microsoft Support.
• Chandoo.org. (2020). Building amortization schedules in Excel. Chandoo.org blog.
• Gallo, A. (2019). Financial Modeling Using Excel and VBA. Wiley.
• Hyndman, R.J., & Athanasopoulos, G. (2018). Forecasting: Principles and Practice. OTexts.
• Hoffman, A. (2021). Data Analysis with Excel. Packt Publishing.
• Friedrichs, M. (2017). Advanced Excel Tips & Tricks. Packt Publishing.
• Rubin, S. (2019). Visual Basic for Applications in Excel. McGraw-Hill Education.
• Banker, R. D., & Kauffman, R. J. (2017). An Introduction to Financial Analytics. MIT Press.