Bob the Builder is a very clever backyard engineer who has just invented a new bicycle brake pad that will greatly improve the ability of pushbikes to stop safely in wet conditions. Bob wants to produce his new brake pads (which are to be sold in sets of two pads), however he is concerned about the market conditions (i.e. sale price) that will prevail and also about his costs of production. Bob actually has no fixed costs of production, but he will certainly have variable costs - the magnitude of which is unknown at this time. Bob thinks the variable cost could be around $58.75 per set of brake pads, but that is only a guess, so he would really like to know what level of variable cost will always result in a positive profit for the business. Bob's friend Fred is going to help finance the brake pad business. As an economist, Fred has conducted some research to determine that the demand equation for the new bicycle brake pads is given by P=140-0.02Q where P is the price of a brake pad set in dollars and Q is the total quantity of brake pad sets demanded by the market per month. It is also known that the quantity of brake pad sets demanded by the market is the summation of demand from two main groups of buyers – namely road bike riders and mountain bike riders. Hence when the total level of demand is known, the associated market price can be determined from the demand equation. Fred also believes strongly that the demand from road bike riders can be accurately estimated by a normal distribution with a mean of 1350 brake pad sets per month and a standard deviation of 250 sets per month. However, for mountain bike riders Fred considers that there is just a 21% chance that they will have a high demand for the new brake pads. If such high demand eventuates then the mountain bike riders are expected to buy 1600 brake pad sets per month. However, if the demand from this group is only medium (which has a probability of 47%) then their demand will fall to 1100 brake pad sets per month. That leaves a 32% chance that mountain bike riders won't particularly take up the new brake pads, and in that case their demand would be quite low at only 800 brake pad sets per month. Noting the above, both Bob and Fred now want a simulation model that determines the maximum variable cost under which their proposed brake pad business will be profitable. YOU ARE REQUIRED TO: Design and build a comprehensive, robust, accurate and necessarily flexible model (i.e. an MS Excel spreadsheet) to fully analyse the stated business problem. As a minimum you will need to determine the level of variable cost that Bob and Fred can endure to maintain the brake pad business as profitable. Write a supporting report (1000 words). This is to be a detailed document that will enable the instructor to clearly understand: how your model works (i.e. its inputs, assumptions, methods, calculations, etc) and why you designed it that way; the core outputs and results from both your model and, importantly, any useful sensitivity/scenario analysis; and the conclusions drawn from the analysis, and the recommendations made to solve the stated business problem. =======================================