trying to figure taxes per month and stuck

[scott11106]

i have attached a sample of my spreadsheet, i have a rental and i need to pay taxes per month and need to separate the charges per month, sometimes rentals span two seperate months and that is where this gets harry, the rates are set and standard at a rate for weekend and a rate for weekday, however weekend rates are applied for friday, saturday and sundaysample data for taxes.xlsx

[ben_hensel]

I think for your purposes here it shouldn't actually matter whether the rental spans the month end, only when the fee is paid.

Like, if someone is renting for the last three days in January and the first three days in February, and they pay the bill when they check out, then you're recognizing the revenue in the month of February, I don't think you're booking any accrual for January.

So I think you should only be booking the revenue (and the tax) based on when the payment was received, not when the rental was reserved? In that case it's just some kind of boolean-tested SUM operation.

[newdoverman]

If this is just for paying taxes, isn't the total revenue minus tax exempt items what matters? That means that the Taxable amount is what you have to use for each month...correct?

I have summarized the Taxable amounts by Year and Month.

[scott11106]

yes i think you are right and that makes this much easier and the pivot table is a great solution, let me play with it

thanks

[newdoverman]

Thank you for the feedback.

Here is an excellent site with tutorials on various aspects of Excel. This url is for Pivot Tables.
http://www.contextures.com/ExcelVide...als.html#pivot

Debra Dalgleish is very good at giving instructions and her site is well worth making note of.