Develop Excel spreadsheets to solve the following two problems:
The "basic problem" is worth twenty points; you may also do any or all of the "advanced features", each of which is worth five points. No one "advanced feature" is more difficult than the others, and you can add them in any order you like.
You're sharing a house with four other students. You've agreed to share expenses for groceries, rent, utilities, household supplies, etc. but in fact everything is paid for by one person or another. At the end of the month, each person tells you the total amount (s)he spent on household expenses, and your spreadsheet's job is to figure out who owes whom how much? Include a chart showing who paid how much of the bills.
Be sure to design your spreadsheet so that next month, you can just type in the new expenditure numbers and the rest of the spreadsheet will automatically reflect the changes. Also design it so that if there were 50 students, or 500, in the house, you could modify the spreadsheet to handle them without typing in any new formulas: you should just type in the names of the additional students, and copy-and-paste existing formulas.
Some residents earn a lot more than others, and the residents have agreed to split expenses not equally, but in proportion to income: if I earn 30% of the total income for the house, I'll pay 30% of the expenses. Modify your spreadsheet so that you can type in each person's income, and it'll compute each person's share of the total expenses, and how much each person owes whom at the end of the month.
Keep records of expenses paid over several months, and add a chart to show who paid how much of the bills from month to month. (If you also do Advanced Feature 1, assume that people's income doesn't change from month to month.)
Keep records of expenses paid in several categories (e.g. rent, groceries, utilities, etc.), and add a chart to show how much of the expenses went into which category. (If you also do Advanced Feature 2, this chart should show clearly how the breakdown of expenses changes from month to month.)
You're in a club that is putting on a festival in the park. You have to pay a fixed amount (say, $600) to rent the park for the day, plus a certain amount (say, $6/person) for security, food, porta-johns, etc. in proportion to the number of people who come to the festival. You're charging admission at the entrance: say, $10 per adult and $5 per child.
The basic question is "How much money will we make or lose?" Obviously, this depends on how many people come to the festival, and how many of them are adults vs. children. Unfortunately, you don't know these numbers exactly until the day of the festival, so you need to look at several possible scenarios with different numbers of adults and children.
Develop a spreadsheet that allows you to predict profit/loss for the day for several such scenarios. Design it in such a way that if the park rental fee, or the cost of security/food/porta-johns, or the ticket prices you charge at the door, are changed, you can respond by typing the new number in one place, and the rest of the spreadsheet will instantly reflect the change.
This problem is worth twenty points, the same as the "basic" version of the previous problem.