Brit Traders is planning on buying some RAINCOATS to sell during the Spring season. Due to prevailing market prices and the estimated costs, it plans on selling each umbrella at $25. The store manager has been in some communication with an online vendor who is selling the RAINCOATS at discounted prices depending on the units bought. Below is the quantity discount structure offered by the vendor. Quantity Discount Structure At least Unit cost 0 14.25 150 12.50 300 11.00 450 10.50 600 8.75 The fixed cost for the store is $120 and a branding cost of $2.75 for each umbrella. The manager wants to order 500 umbrellas but is uncertain about the demand which could be between 200 – 800. He has also decided that in case all the umbrellas will not be sold before the end of the season, any leftovers will be sold at a clearance price of $9 for each umbrella. The store manager has approached you to help him build a spreadsheet model to help him in making a decision on how many pieces to buy Create a model considering an order level of 400 orders and demand of 500 units. a) Color code your model (2pts) b) Create range names (2pts) c) Use IF Statement to calculate revenue (regular price, leftover price) (6pts) d) Use VLOOKUP to get the unit cost to be charged for the ordered units (3pts) e) Calculate total cost and profit (6pts) f) How many units will have to be ordered to break even (2pts) g) How many units will have to be ordered to get a profit of $4,500 (2pts) h) How does a change in order units affect profit for order units 200 – 1000 with increment of 100 units. Create a one way data table and a line chart. Be sure to edit your chart (3pts) i) How does a change in order units and demand units jointly affect the profit. Set up a two– way data table with profit as the output and order as the input with order quantities ranging from 200 – 1000 with increment of 100 units and demand units is another input ranging from 200 – 1000 with increment of 100 units (2pts) j) Clearly note your observations (2pts)
Brit Traders is planning on buying some RAINCOATS to sell during the Spring season. Due to prevailing market prices and the estimated costs, it plans on selling each umbrella at $25. The store manager has been in some communication with an online vendor who is selling the RAINCOATS at discounted prices depending on the units bought. Below is the quantity discount structure offered by the vendor. Quantity Discount Structure At least Unit cost 0 14.25 150 12.50 300 11.00 450 10.50 600 8.75
The fixed cost for the store is $120 and a branding cost of $2.75 for each umbrella. The manager wants to order 500 umbrellas but is uncertain about the demand which could be between 200 – 800. He has also decided that in case all the umbrellas will not be sold before the end of the season, any leftovers will be sold at a clearance price of $9 for each umbrella. The store manager has approached you to help him build a spreadsheet model to help him in making a decision on how many pieces to buy
Create a model considering an order level of 400 orders and demand of 500 units. a) Color code your model (2pts) b) Create range names (2pts) c) Use IF Statement to calculate revenue (regular price, leftover price) (6pts) d) Use VLOOKUP to get the unit cost to be charged for the ordered units (3pts) e) Calculate total cost and profit (6pts) f) How many units will have to be ordered to break even (2pts) g) How many units will have to be ordered to get a profit of $4,500 (2pts) h) How does a change in order units affect profit for order units 200 – 1000 with increment of 100 units. Create a one way data table and a line chart. Be sure to edit your chart (3pts) i) How does a change in order units and demand units jointly affect the profit. Set up a two– way data table with profit as the output and order as the input with order quantities ranging from 200 – 1000 with increment of 100 units and demand units is another input ranging from 200 – 1000 with increment of 100 units (2pts) j) Clearly note your observations (2pts)
AI-generated content may present inaccurate or offensive content that does not represent bartleby’s views.
Unlock instant AI solutions
Tap the button
to generate a solution