I need this Excel worksheet filled out with specific instructions from the Word file.
RidgeWell Language School Analysis of Student Payments Files Needed: Tutorial 7-StudentAccounting.xlsx Ridgewell 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 “Paid” if the value is 0 or less. Note that every cell in the Owing column will contain “Paid” 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 have to match exactly. Colors are in the ‘Blue-Grey’ accent family) 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) =VLOOKUP( Value you want to look up, Range where you want to lookup the value, The column number in the range containing the return value, Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE ) =VLOOKUP( 1 , 2 , 3 , 4) (1) Should be the Program cell in this row (3) 2 (2) The whole table (without headers) of the lookup table on the second tab. Use absolute references!! (4) FALSE 10. In cell J5, compute taxes by multiplying Paid by 0.033%. Do so for the entire column. 11. In cell K5 (Balance), enter the formula that adds the values in the Tuition, Material Costs, and Taxes cells and then subtracts from that total the sum of the Paid and Discount cells. Use Point and Click to enter the cell addresses and don’t forget to include parenthesis where needed. Fill in the rest of the column. 12. 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. 13. Change the Student ID for Chloe Leblanc to 4511 and change the Student ID for Patricia Chow to 4599. 14. Open the Lookup tab. In cell D1, create another lookup table for professors as seen below: (Color and text formatting have to match exactly. Colors are in the ‘Blue-Grey’ accent family) 15. In cell L5 of the Student Records worksheet, use the similar VLOOKUP function used previously to enter the appropriate professor based on the program a student is taking (“English,” “Spanish,” or “French.”). 16. 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 text centering and a centered vertical alignment to make sure text is centered in the cells. The colors used are in the ‘Blue-Grey’ accent family. Use Wrap Text on the header text to make each display inside their cell as seen below. 17. In cell C4, use the COUNTIF function to calculate how many students are enrolled in the Spanish program. Use the Program range name in the formula. You should see 19 in cell C4. 18. Repeat Step 17 to calculate the number of students enrolled in the French Program and the English Program. 19. In cell D4, use the SUMIF function to calculate the total Tuition costs for the students enrolled in the Spanish Program, then use the SUMIF function to calculate the required totals for cells F4, G4, and I4. Remember for E, include an underscore for Material_Costs to match the range name. 20. Repeat Step 19 to perform the same calculations for the students in the French Program and the English Program. 21. In Cell H4, use COUNTIF to calculate how many students have outstanding balances for the Spanish Program. 22. Repeat Step 21 to perform the same calculations for the students in the French Program and the English Program. 23. In Cell J4, use the AVERAGEIF function to calculate the average balance owing by the students in the Spanish Program. 24. Repeat Step 23 to calculate the average balance owing by the students in the French Program and the English Program. 25. Format all values in the range D4:G6 and I4:J4 in Accounting, widen columns as needed. 26. Change the theme to ‘Integral’ from the Page Layout tab. 27. Save the file and submit it to your instructor. Grading Rubric Grade Item: Points Main student table completed correctly 40 pts Added summation table at the top completed correctly 35 pts Look up tables 10 pts Colors and formatting are as expected 10 pts File named correctly Incorrect examples including but not limited to: Tutorial 7_Last Name_StudentAccounting (1) or Tutorial 7_Last Name_StudentAccounting.xlsx.xlsx 5 pts Common Additional Deductions: Submitting identical files as someone else in the class -100pts and a formal report filed. Submitting identical file from another semester -100pts and a formal report filed. Taking liberties and making changes you want to make and not ones that are in the instructions. Ie formatting etc. -10~100pts depending upon infraction Using your own excel document instead of the one provided -100pts Student Records Mountainview Language School Student Payment Records Student IDFirst NameLast NameProgramLevelTuitionMaterial CostsPaidDiscountTaxesBalanceProfessor 4522SerafinaLeflerSpanish1$ 2,000.00 4589AliceKimEnglish2$ 1,200.00 4512FernandeHunzikerEnglish1$ 2,300.00 4589ChloeLeblancEnglish1$ 1,800.00 4536GraceParkEnglish4$ 3,500.00 4588FionaHearstSpanish4$ 800.00 4597MilardStallingsFrench1$ 3,350.00 4598DixieO'BrienFrench2$ 3,500.00 4508JorgeGonzalesEnglish1$ 1,000.00 4534DerekKimEnglish2$ 3,500.00 4597PatriciaChowEnglish3$ 3,500.00 4566WendelLeeEnglish4$ 3,000.00 4560HansKitmuellerEnglish2$ 2,000.00 4591DorianMinelliEnglish2$ 3,500.00 4572LisaSmithSpanish2$ 1,800.00 4500LindenWagnerSpanish1$ 2,350.00 4532KaylaAbersSpanish1$ 500.00 4567RyanLouieEnglish1$ 2,300.00 4577MokotaYanamotoEnglish1$ 2,300.00 4578KaylaTaketaEnglish4$ 800.00 4568RashidSinghEnglish4$ 1,200.00 4590ParminderSinghEnglish3$ 2,500.00 4509JudsonMinichEnglish3$ 3,500.00 4582CathyBrownSpanish4$ 3,350.00 4570ElinorPetrovichEnglish4$ 200.00 4576WendyShufeltEnglish3$ 1,000.00 4529ClementineSleetFrench2$ 1,000.00 4537ClotildeHoffmannEnglish2$ 2,000.00 4569EmiliaLeeSpanish2$ 1,500.00 Lookup