The Barry's Auto Tires company sells radial tires. The frequency information for the number of units sold in the week (i.e. weekly demand = 1, 2, 3, 4 or 5 units) among past 200 weeks is shown in the frequency table below.

Weekly demand (units)		Freq. (no. of weeks among 200 weeks)
1		40
2		55
3		60
4		30
5		15

The company has 2 new units delivered to the store at the beginning of each week. Unmet demand is considered as lost sales (no backorders). The storage space is limited to a maximum of 5 units of tires. If the space is filled, the store manager will have the extra tire(s) (i.e. over capacitated situation) in the front display area for sale at a discount price. For example, if the store has the inventory of 7 units, 2 units will be displayed for a discount price.

Create an Excel template to show your 50-week scenario of simulation (week 1 through week 50) to calculate the rate (or percentage) of over-capacitated weeks (e.g. = 10%, if there are 10 weeks in the over capacitated situation out of 100 weeks), % loss of sales (e.g.= 10% if during these 50 weeks, lost sales is 10 units and total demand is 100 units), and the average number of units in the front display area for the week. These numbers can provide insights into whether to change the delivery/ordering strategy or expand the storage space. We assume that there are no old units on hand at the beginning of the first week (week 1).