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.

Part A: Open Language School.xlsx and save it with a new name that includes your name.

 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 lookup table to calculate data related to student payments, and then you use the COUNTIF, SUMIF, and AVERAGEIF functions to analyze the payment data.

1.      Create an Excel table and give it an appropriate name.

2.      In cell K4, add a new column called Owing.

3.      Freeze the rows from the column headings up to Row 1.

4.      In cell K5, enter an IF function that enters Owing in cell K5 if the Balance is greater than 0 and Up to Date if the value is 0 or less.

5.      Students who are enrolled in a Level 1 course pay $2,100 in tuition, and students enrolled in Levels 2, 3, and 4 courses pay $3,300 in tuition. In cell F5 (Tuition), enter a function to enter the correct tuition fee.

6.      A 15% discount is offered to students who are taking Level 1 English. In cell I5, enter a function to calculate the discount amount. If neither criterion is met, a 0 should show.

7.      In the Lookup worksheet, create a Lookup table to reflect the material costs for each program. Include clear labels. The material costs are as follows: $540 for English,

$392 for Spanish and $415 for French.

8.      In cell G5 of the Student Records worksheet, use VLOOKUP or HLOOKUP to enter the appropriate material costs based on the program a student is taking.

9.      In cell J5 (Balance), enter the formula that calculates the balance based on the values in the Tuition, Material Costs, Paid and Discount columns.

10. Add a total row and choose functions which would be meaningful tallies.

11. 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 five new rows above the table, clear any formatting, and then enter and format text following the sample below. You may choose your own colours, but ensure that there is sufficient contrast.

14. In D5:I7, use COUNTIF, SUMIF and AVERAGEIF functions to fill in the table. Format all values.

15. Create a Pivot Table to summarize the number of students enrolled in each program. Then prepare a Pivot Chart to show the percentage of students enrolled in each program. Give appropriate titles. Go to the Payments table and add yourself as a student; make up the rest of the data. Return to the worksheet showing your Pivot Table and ensure that it is updated to reflect your registration.

16. Create a Pivot Table to summarize the tuition received by program and level. Provide an appropriate title and format the table. Then complete this extra step as a bonus: Choose a tuition total for one of the programs and drill down to create a new worksheet showing all of the supporting data. Name the worksheet with the program and the word “Details.” Again, ensure consistent formatting.

17. Double check that all tables are formatted well and have consistent or complementary styles.

 Part B: This question is a follow-up to Assignments 1 and 2 in which you designed a grade book for a course.

·   On your Grades worksheet, add two columns to the right of the final numeric grade.

o   In the first new column, show the final grade after the attendance requirement has been considered for the course. On the course outline, the professor has indicated that 1 point will be deducted for every class missed beyond the 3- class grace period.

o  In the second new column, show the final grade as a letter grade. Use a VLOOKUP or HLOOKUP. Consult the Mount’s undergraduate calendar.