Problem 4: Estimates

A company purchases Widgets according to this price schedule:

• For the first 100 units, $9.20 per Unit
• For any of the next 500 units $8.80 per Unit
• For any oil beyond 1000 units, $7.95 per Unit
• The Purchase Estimate spreadsheet calculates the total price of buying Units of Widgets, where a number input to a cell (UnitsOrdered) on the worksheet with values that may fall into any or all of the 3 Tiers of pricing.

1. Re-create the model as per below:
2. The model must be able to handle any order quantity scenarios.
3. Format with differentiating colors as per example below.
4. Hint: Key functions to use:IF