Free Excel Practice File 3
One hundred and twenty students. Some have paid, some have not, and the Principal wants to know where things stand. Your job is to finish the register and find the answer.
This is the third file in the series, and the most substantial. It picks up where the retail sales file left off β SUMIF and COUNTIF, tables and filtering, a PivotTable and two charts. About 60 to 90 minutes at a comfortable pace.
Free, no sign-up. Works in Microsoft Excel, Google Sheets, LibreOffice Calc or Apple Numbers.
Every one of these is used on real data, in a real order, for a reason someone actually asked for. Nothing is practised for its own sake.
You have just started as the office administrator at KΕwhai Valley Intermediate School. There are 120 students across Year 7 and Year 8. Every student pays a term activity fee, and GST is added on top of that fee.
The register is half finished. The fees are recorded and the payments have been entered, but nobody has worked out the GST, the totals, or what is still owing. That part is yours.
By the end you will be able to tell the Principal exactly how much was charged, how much has come in, how many families are overdue, and which year group is further behind. Then you will put it on a chart, because a number in a sentence is easy to argue with and a chart is not.
A note on the setting. This school is in New Zealand, so the GST rate in the workbook is 15 per cent rather than the Australian 10 per cent. That is deliberate, and it does not change a single formula you will write. The rate is stored in one cell and every calculation points at that cell β which is exactly how a spreadsheet should be built. Change the cell and the whole register updates itself. Learning that habit matters far more than learning one country's rate.
Read this first. It explains the scenario and walks through all seven parts in order.
The 120 student records β the register itself. Three columns are blank and you fill them in. Row 6 is completed as a worked example.
Where you build the summary for the Principal. Twenty-three questions across five sections, each with a formula hint beside it. Yellow cells are yours.
The completed columns and every answer, so you can check your work once you have finished. The Answer Key shows the correct figure beside your own result, so you can see exactly where a number went astray rather than just that it did.
Work through them in order. Each one depends on the one before it, so skipping ahead usually creates more work than it saves.
Widen the columns, set the money columns to currency and the due dates to dates, centre the year and room, and freeze the header row so it stays put as you scroll.
Work out the GST, the total fee including GST, and the balance still outstanding β then fill each formula down all 120 rows. Check row 6 before you fill down. A wrong formula copied 120 times is 120 mistakes.
AutoSum, SUM, AVERAGE, MIN, MAX and COUNT. Totals charged, totals collected, the average fee, the largest and smallest, and how many students there are altogether.
Add up only the rows that match a condition, and count only the ones that do. How much has Year 7 been charged? How many families are overdue? This is the part that turns a list into an answer.
Convert the register to a proper Excel table, filter it down to one year group, then to the overdue families, and sort by the largest balance owing to see who to contact first.
Payment status down the side, fees across the values, then year level across the top. Switch from Sum to Average and back, and watch what changes. This is the tool most people are frightened of and most quickly stop being frightened of.
A bar chart of fees by payment status, and a pie chart showing how many students sit in each status, with the percentages labelled. The workbook also explains when a pie chart is the wrong choice β counts add up to a whole, dollars owed do not.
This one assumes you have written a formula before. If that is not you yet, start with file one β it begins with clicking a cell and typing an equals sign, and it explains every single step. There is no prize for starting at the hard end.
One month of household spending. AutoSum, percentages, and your first two charts. Around 30 to 40 minutes.
Start here β Practice File 2A Melbourne shop with four stores and 100 sales. SUMIF, VLOOKUP, INDEX and MATCH. Around 60 to 90 minutes.
Take this one first βDownload the workbook, save your own copy, and open the Instructions sheet. Everything you need is inside it.
Download the workbook (Excel)Practice data only β every student, caregiver and contact detail in the file is fictional.
π Stuck on one of the parts? That is normal, and it is fixable.
Book a free 15-minute chat and we will work out where you got to and what would help.
Book Your Free Discovery CallNot ready to book yet? Send a quick message and Mandeep will get back to you personally. For booking a Discovery Call or 1:1 Coaching Session, use the buttons above to choose a time directly.
For instant booking, use the Discovery Call or 1:1 Coaching buttons instead β this form is for general questions only.