The Prime National Properties Group is considering selling some of its educational facilities. The facilities have been tracked in an Access database. Shelly Williams, the President of the company, has asked you to prepare a report. Import the Access data in the file Facilities_Data as a table into Excel. Using the PMT function, calculate the monthly payments the company could expect. The company would charge an 8% rate over 10 years. The result should be a positive number. Mr. Williams also asked you to calculate an extra processing fee for monthly loan payments using the IF function. If a monthly loan payment is less than $6000, the company would charge a processing fee of $150; otherwise, there would be no processing fee. Filter the rows to display only those facilities where there is a $0 processing fee. Hide all of the address fields and the processing fee column. Save the Excel file as Lastname_Firstname_Educational_Facilities For this assignment, you will need the following files: You will save your files as: Lastname_Firstname_Educational_Facilities Lastname_Firstname_Educational_Report

Introduction

The Prime National Properties Group is considering selling some of its educational facilities and has asked for a report on the data of these facilities. The data is currently stored in an Access database and needs to be imported into Excel for further analysis. In this report, we will import the Access data into Excel, calculate the monthly payments using the PMT function, and determine if an extra processing fee needs to be charged using the IF function. We will filter the data to display only the facilities with no processing fee and save the Excel file with the appropriate name.

Importing Access Data into Excel

To import the Access data into Excel, we will use the Power Query feature. Power Query allows us to connect to various data sources, including an Access database. By importing the data into Excel, we will be able to perform calculations and analysis more easily.

Calculating Monthly Payments

The monthly payments for the facilities will be calculated using the PMT function in Excel. The PMT function calculates the monthly payment for a loan based on the interest rate, number of periods, and loan amount. In this case, the interest rate is 8%, the number of periods is 10 years, and the loan amount is the total cost of the facility.

To calculate the monthly payments, we will use the following formula:

=PMT(interest_rate/12, number_of_periods*12, loan_amount)

The result will be a positive number, representing the monthly payment the company can expect.

Determining the Extra Processing Fee

The company wants to charge an extra processing fee for monthly loan payments if the payment is less than $6000. To determine if an extra processing fee is required, we will use the IF function in Excel. The IF function allows us to perform a logical test and return different values based on the result of that test.

In this case, we will use the following formula to determine if a processing fee should be charged:

=IF(monthly_payment < 6000, 150, 0) If the monthly payment is less than $6000, a processing fee of $150 will be charged. Otherwise, there will be no processing fee. Filtering the Data and Hiding Columns To display only the facilities with no processing fee, we will filter the data to include only the rows where the processing fee is $0. This can be done by applying a filter to the processing fee column and selecting only the rows where the value is equal to 0. Additionally, we are instructed to hide all of the address fields and the processing fee column. Hiding these columns will make the report more streamlined and focused on the relevant information. Saving the Excel File Finally, we are instructed to save the Excel file with the file name format Lastname_Firstname_Educational_Facilities. By following this naming convention, the file can be easily identified and organized within the file storage system. Conclusion In this report, we discussed the process of importing Access data into Excel, calculating monthly payments using the PMT function, determining an extra processing fee using the IF function, filtering the data, hiding columns, and saving the Excel file. By following these steps, we can create a report that provides valuable information on the educational facilities being considered for sale by the Prime National Properties Group.

Need your ASSIGNMENT done? Use our paper writing service to score better and meet your deadline.


Click Here to Make an Order Click Here to Hire a Writer