Help with formula and math involved with totaling data for invoicing
I’m working on a spreadsheet to help track the hundreds of small orders and their revisions that we will be getting over the next few months. The purpose of the spreadsheet is to help determine what to invoice by the end of each order. I attached a copy of what I am working on but I need help with a couple of the formulas and some of the math involved to get to the next level.
Long story short, the pricing agreement established by my customer is as follows:
1. $300 startup base cost for all orders up to 3 revisions (see column K). $50 for each revision after 3.
2. 1 free WB (white body) per revsion (see column E). Additional WB’s are $25 each.
3. All digital orders (see column F) are $3.50 per square foot (see column N).
I need help with figuring out how to figure revisions past the 3rd one into what to invoice. I also need some help fine tuning my math and maybe making them formulas. For example, what if they order 0 WB’s? I need a better equation/formula. Any help would be much appreciated.
|