One of the popular items in Cozy Eaterys menu is a cake which it buys from Tasty Bakery. The cost depends on the total quantity of weekly order as shown below: Total Quantity of Weekly Order Cost per piece Less than 300 pcs $3.30 300 pcs to 499 pcs $3.00 500 pcs or more $2.70 Tasty also charges a weekly delivery fee of $70, regardless of the quantity ordered and number of delivery trips made in the week For example, if the order quantity is 520 pieces, then the total cost = $70 + 520 pcs * $2.70/pcs. Cozy Eatery is evaluating whether it should make the cake itself. It will have to employ a part time baker who is able to produce the cake with equivalent quality. While the equipment cost is considered sunk cost, the bakers salary and equipment maintenance cost will be $600 per week. The variable cost for each piece of cake will be $1.80. (a) Create a model to help Cozy Eatery to decide whether to make or buy the cake. Refer to the table below, you shall start from 250 pieces of cakes in the first column and increment in steps of 10 pieces per row, up to 650 pieces. Cells in other columns must contain Excel formulae and/or functions without simple linking to another cell You must illustrate conditional formatting to highlight the cell in the Make column with Green background if make is the cheaper option. Do likewise for the cell in the Buy column by highlighting it in Yellow background, if buy is the cheaper option. Total Cost (S$) Weekly Quantity of Cake Make Buy Minimum Best Option 250 Buy The first row for 250 pieces is shown for reference. In addition to the exploratory model, compare the two options by constructing a Scatter Chart with Make and Buy Costs versus Weekly Total Quantity. Interpret the results from the model and the chart to advise Cozy Eaterys owner on whether it should make or buy from quantity between 250 to 650?

Cozy Eaterys owner wants to know accurately the cross over quantity where there is no difference in costs between the Make and Buy options. The model you derived in Part(a) may not provide this information precisely. Explain and demonstrate how you can determine the crossover quantity accurately, You shall highlight any special or advanced features in your models in Q2(a) and Q2(b).