Mountainview Language School - Students Payment Analysis Excel Project Download Now

Analysis of Student Payments

Mountainview Language School in Denver, CO, is a small school that provides language training in English, Spanish, and French to students from all over the world. The school keeps track of its student records in Excel. You’ve been asked to work with the current spreadsheet and improve it so that information and issues can be analyzed and tracked. You use advanced IF functions and a VLOOKUP table to calculate data related to student payments, and then you use the COUNTIF, SUMIF, and AVERAGEIF functions to analyze the payment data.

1. Start Excel and open Tutorial 7_StudentAccounting.xlsx.
2. Save the spreadsheet as Tutorial 7_Last Name_StudentAccounting.xlsx. (Replace “Last Name” with your last name.)
3. In cell K4, add a new column called Owing. Note that the new column is formatted the same way as the rest of the table because the data is contained in an Excel table.
4. In cell K5, enter an IF function that enters “Owing” in cell K5 if the value in cell J5 (Balance) is greater than “0” and “Up to Date” if the value is 0 or less. Note that every cell in the Owing column will contain “Up to Date” because you have not yet completed all the formulas for the table.
5. Widen the Owing column to fit the contents.
6. In cell F5 (Tuition), enter an “OR” IF function that enters 2000 if the value in the Level range is 1 and 3000 if the value in the Level range is not. Students who are enrolled in a Level 1 course pay $2,000 in tuition and students enrolled in Levels 2, 3, and 4 courses pay $3,000 in tuition. You should see “$2,000” in cell F5 and $3,000” in cell F6. Make sure both values are formatted as Accounting (Currency). If the values appear as labels, revise your IF function.
7. In cell I5, enter an “AND” IF function that enters F5*.10 if the value in the Program range is English AND the value in the Level range is 1. If neither of these criteria are met, enter a 0. The purpose of this IF function is to offer a 10% discount to students who are taking Level 1 English. You should see 0 in cell I5 and $200 in cell I7.
8. In the Lookup worksheet, create a Lookup table that appears as shown below: (color and text formatting do not have to match exactly)
9. In cell G5 of the Student Records worksheet, use the VLOOKUP function to enter the appropriate material costs based on the program a student is taking (“English,” “Spanish,” or “French.”). When entering the table range in the VLOOKUP make sure to press the F4 button to make that range an absolute reference. Make sure to drag G5 down for the rest of the column to have the correct material costs. (select table range, click F4)
10. In cell J5 (Balance), enter the formula that adds the values in the Tuition and Material Costs cells and then subtracts them from the sum of the Paid and Discount cells. You should see $350 in cell J5. Use Point and Click to enter the cell addresses and don’t forget to include parenthesis where needed.
11. Select the Student_ID range, then use conditional formatting to highlight any duplicate values in the Student ID column. For the formatting, select black text and a light red fill color.
12. Change the Student ID for Chloe Leblanc to 4511 and change the Student ID for Patricia Chow to 4599.
13. Insert four new rows above the table, clear any formatting, and then enter and format text as shown below: (color and formatting must match, enlarge columns as needed.) Use Wrap Text on the header text to make each display inside their cell as seen below.
14. In cell D4, use the COUNTIF function to calculate how many students are enrolled in the English program. Use the Program range name in the formula. You should see 19 in cell D4.
15. Repeat Step 14 to calculate the number of students enrolled in the French Program and the Spanish Program.
16. In cell E4, use the SUMIF function to calculate the total Tuition costs for the students enrolled in the English Program, then use the SUMIF function to calculate the required totals for cells F4, G4, and H4. Remember to include an underscore for Material_Costs to match the range name.
17. Repeat Step 16 to perform the same calculations for the students in the French Program and the Spanish Program.
18. In Cell I4, use the AVERAGEIF function to calculate the average balance owing by the students in the English Program.
19. Repeat Step 18 to calculate the average balance owing by the students in the French Program and the Spanish Program.
20. Format all values in the range E4:I6 in Accounting, widen columns as needed.
21. Save the file and submit it to your instructor.

Product Images

Download Now

Buy now