We are an out of school club that runs a breakfast and after school club with a long day on a Friday. We charge different amounts for Monday to Thursdays and Fridays. Breakfast price remains constant.
I need an excel code/formula written to express the monthly fee that a customer has to pay for each school year. For 2015 we used the below for each customer but now need to streamline it. I need someone to write the forumla so I can put in the days required, and a price will come out so I can invoice the client
“ We use a formula to enable a standard monthly fee, rather than differing amounts for each
We take all the days that your child attends throughout the school year, add the fee
to each day and divide it by 11 (the months that the children attend school). Holidays and in
service days are deducted through this calculation.
Example: Child attends breakfast club on Mondays and Thursdays:
Total Mondays plus total Thursdays in 2014/15 school year = 73 days
Fee per breakfast club session = £3
2015/16 year fee (73 × 3) = £219
Monthly fee (219 / 11 months that a child is in school) = £19.91”
I also need to be able to update the number of each day in a year and the price for each day.
Please ask anything if you need to.