CBAD $292$ HW$2(30$ Pts$)$

NB: For this question you will need to create a model from scratch as no template has been provided.

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

$0$

$150$

$300$

$450$

$600$

Unit cost

$14\mathrm{.}2512\mathrm{.}5011\mathrm{.}0010\mathrm{.}50$

$8\mathrm{.}75$

The fixed cost for the store is $$120$ and a branding cost of $$2\mathrm{.}75$ for each umbrella. The manager wants to order $500$ umbrellas but is uncertain about the demand which could be between $200800.$ 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.

$1$

Create a model considering an order level of $400$ orders and demand of $500$ units.

a$)$ Color code your model $(2$pts$)$

b$)$ Create range names $(2$pts$)$

c$)$ Use IF Statement to calculate revenue $($regular price, leftover price$)(6$pts$)$

d$)$ Use VLOOKUP to get the unit cost to be charged for the ordered units $(3$pts$)$

e$)$ Calculate total cost and profit $(6$pts$)$

f$)$ How many units will have to be ordered to break even $(2$pts$)$

g$)$ How many units will have to be ordered to get a profit of $$4,500(2$pts$)$

h$)$ How does a change in order units affect profit for order units $2001000$ with increment of $100$ units. Create a one way data table and a line chart. Be sure to edit your chart $(3$pts$)$

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 $2001000$ with increment of $100$ units and demand units is another input ranging from $2001000$ with increment of $100$ units $(2$pts$)$

j$)$ Clearly note your observations $(2$pts$)$