Question

Jacket Frenzy is a small clothing manufacturer, specializing in creating custom, one-of-a-kind jackets. They
exclusively sell their product through their online store.
Jacket Frenzy is run by a team of three staff members: April, Beatrice, and Claudia.
April and Beatrice work together to manufacture jackets. Claudia manages orders, sales and delivery.

Jacket Frenzy receive requests through their online store, and Claudia responds with a quote, after
consulting with a member of the manufacturing team. If the customer confirms their order, April and
Beatrice manufacture the ordered products, and Claudia organizes delivery.
Currently, the process for generating a quote is entirely subjective, based on the requested material and
jacket size. Staff members quote an amount based on their personal experience.
For several months, staff have been keeping track of the quote date, material, size, quote amount, and
manufacturing cost for each order in a spreadsheet. As each product is hand-made, the manufacturing
cost may vary between similar orders.
If a quote was not accepted by the customer, then manufacturing cost is not included.

This spreadsheet is available below

Quote date	Material	Size	Quote amount	Manufacturing cost ($)
2018-06-04	Cotton	S	76.3	25.3
2018-06-05	Cotton	L	68	31
2018-06-06	Wool	L	80.3	47.1
2018-06-06	Leather	XL	247.6	71.8
2018-06-06	Wool	L	78	46
2018-06-07	Wool	M	94.8	39.6
2018-06-08	Wool	XS	154.1	30.3
2018-06-08	Wool	L	121	44
2018-06-11	Wool	XS	63.3	31.3
2018-06-11	Tweed	XL	169	82
2018-06-12	Wool	XS	91.3	32.1
2018-06-13	Wool	L	111.6	45.8
2018-06-13	Tweed	XL	238	81
2018-06-13	Wool	XXL	114.8	56.6
2018-06-14	Wool	L	123.1	46.3
2018-06-14	Wool	M	107	39
2018-06-14	Wool	XXL	116.3	56.3
2018-06-14	Cotton	XL	94	31
2018-06-15	Wool	XL	156.3	52.1
2018-06-18	Silk	S	363.6	86.8
2018-06-18	Wool	XS	74	32
2018-06-18	Cotton	M	83.8	26.6
2018-06-19	Cotton	L	70.1	28.3
2018-06-20	Tweed	XXL	198	97
2018-06-20	Cotton	M	71.3	24.3
2018-06-21	Leather	XXL	231	75
2018-06-21	Tweed	XL	222.3	82.1
2018-06-22	Silk	M	236.6	93.8
2018-06-22	Wool	L	166	50
2018-06-22	Wool	M	154.8	42.6
2018-06-25	Wool	XS	100.1	34.3
2018-06-26	Wool	S	93	41
2018-06-26	Cotton	L	91.3	29.3
2018-06-27	Wool	L	100	49
2018-06-27	Wool	L	103.3	49.1
2018-06-27	Cotton	L	79.6	28.8
2018-06-27	Wool	XL	129	54
2018-06-28	Wool	S	155.8	42.6
2018-06-29	Wool	XXL	156.1	64.3
2018-07-02	Cotton	M	64	24
2018-07-02	Wool	XS	106.3	35.3
2018-07-03	Cotton	L	86	29
2018-07-03	Cotton	S	78.3	22.1
2018-07-04	Wool	XS	106.6	35.8
2018-07-04	Cotton	S	64	22
2018-07-04	Wool	L	122.8	52.6
2018-07-05	Tweed	XS	250.1	55.3
2018-07-06	Cotton	XXL	100	34
2018-07-06	Cotton	M	84.3	25.3
2018-07-06	Cotton	L	92	28
2018-07-09	Tweed	XS	173.3	55.1
2018-07-09	Cotton	XS	70.6	19.8
2018-07-09	Leather	XS	160	42
2018-07-09	Cotton	L	89.8	28.6
2018-07-10	Wool	L	115.1	53.3
2018-07-10	Cotton	M	73	24
2018-07-10	Silk	S	384.3	88.3
2018-07-10	Leather	L	206	60
2018-07-11	Wool	M	127.3	46.1
2018-07-11	Tweed	XS	211.6	56.8
2018-07-11	Cotton	XXL	104	33
2018-07-11	Wool	XXL	155.8	64.6
2018-07-12	Wool	S	165.1	42.3
2018-07-12	Wool	XS	125	36
2018-07-13	Tweed	S	276.3	68.3
2018-07-13	Wool	S	144	42
2018-07-13	Wool	XL	134.3	57.1
2018-07-13	Wool	XXL	192.6	63.8
2018-07-16	Wool	M	122	48
2018-07-17	Cotton	XL	75.8	31.6
2018-07-18	Cotton	XS	58.1	18.3
2018-07-18	Tweed	XXL	316	105
2018-07-19	Wool	M	129.3	50.3
2018-07-20	Wool	XXL	173	69
2018-07-23	Leather	XS	182.3	38.1
2018-07-23	Wool	XXL	135.6	70.8
2018-07-23	Wool	S	121	44
2018-07-23	Leather	L	249.8	57.6
2018-07-24	Cotton	S	66.1	22.3
2018-07-24	Cotton	L	81	28
2018-07-24	Cotton	S	70.3	22.3
2018-07-24	Wool	XS	113	39
2018-07-25	Cotton	XS	74.3	18.1
2018-07-25	Tweed	M	230.6	76.8
2018-07-26	Silk	XL	291	130
2018-07-26	Cotton	L	84.8	27.6
2018-07-26	Wool	XS	114.1	38.3
2018-07-26	Leather	S	110	47
2018-07-27	Cotton	XS	53.3	18.3
2018-07-30	Wool	XS	107	38
2018-07-31	Leather	XS	193.3	37.1
2018-07-31	Cotton	XS	73.6	18.8
2018-08-01	Leather	S	222	44
2018-08-01	Silk	XL	532.8	127.6
2018-08-02	Silk	S	170.1	96.3
2018-08-02	Wool	XXL	189	71
2018-08-03	Cotton	XXL	95.3	33.3
2018-08-06	Silk	M	499	103
2018-08-07	Leather	L	213.3	55.1

Task - Price consistency
Using Excel, analyse the historical data you have available, and create a spreadsheet that allows a member
of the Jacket Frenzy team to enter the jacket material and size, and gives a quote amount.
Ensure that you include enough text and formatting to make your spreadsheet usable by a member of the
Jacket Frenzy team (or by a University lecturer).
You may base your quote amount either on historical averages, or to ensure that Jacket Frenzy makes a
profit.
Validate your quoting approach by applying your formula to historical data.
Justify your approach in word file (approximately 150 words), and attach your Excel file

Answer 1

Helper	Material	Size	Avg Quote
CottonL	Cotton	L	82.51		Material	Silk
CottonM	Cotton	M	75.28		Size	M
CottonS	Cotton	S	71.00		Quote	367.80	VLOOKUP(G2&G3,A1:D26,4,0)
CottonXL	Cotton	XL	84.90
CottonXS	Cotton	XS	65.98
CottonXXL	Cotton	XXL	99.77
LeatherL	Leather	L	223.03
LeatherS	Leather	S	166.00
LeatherXL	Leather	XL	247.60
LeatherXS	Leather	XS	178.53
LeatherXXL	Leather	XXL	231.00
SilkM	Silk	M	367.80
SilkS	Silk	S	306.00
SilkXL	Silk	XL	411.90
TweedM	Tweed	M	230.60
TweedS	Tweed	S	276.30
TweedXL	Tweed	XL	209.77
TweedXS	Tweed	XS	211.67
TweedXXL	Tweed	XXL	257.00
WoolL	Wool	L	112.12
WoolM	Wool	M	122.53
WoolS	Wool	S	135.78
WoolXL	Wool	XL	139.87
WoolXS	Wool	XS	104.98
WoolXXL	Wool	XXL	154.15

Explanation to prepare Excel:

The given above data file is analysed and a PIVOT table can be prepared with Rows containing Material and Size and the Values containing average of Quotes.

Next we can paste special the Pivot Table, use a concatenate formula for column material and size, to prepare a helper column and use the above Vlookup formula in a cell to get the quote, the user needs to enter the material and size and will the formula cell will return the Quote.

In the above excel the material and size needs to be entered or inputed and the Quote will be returned by the spreasheet.