Vacation Request Tracker with somewhat complex rules

I have been asked to create a vacation request spreadsheet that balances the four different vacation types my company offers.

• Holiday - We are off on all federal holidays.

• Floating - All employees receive 4 floating holiday days per year. These must be used by December 31st.

• Carryover - Maximum of 10 days per year can be accrued, if you don't use all your vacation days from the previous year.

• Vacation - 15 days deposited into employees account each New Year. Every 10 years of service nets you a bonus 5 days per year.

When an employee requests a vacation, the request has to pull from the bins in the order shown above (top is highest priority, bottom is lowest). If a bin reaches a balance of 0, the remainder of the vacation request must be debited from the next bin. If this 'next' bin reaches a balance of 0, then the remainder of the vacation request must be debited from the next bin, and so on. If all bins are depleted a red X should appear. You get the idea.

I've seen a lot of spreadsheets but none track the requests in the manner I describe, yet every company I've worked at issues vacations similar to the manner I describe. Why can't I find this type of spreadsheet template? Can Excel masters do this? I assume it would even be similar to balancing between checking and savings accounts or you could borrow ideas from other types of financial templates.