c. Vary the discount rate between 5 and 15% and the peak share for market penetration between 0 and 10%. Describe business insights about the device that you learn from the results. d. Vary all parameters in the model except the dates by +15%. List below the three parameters that have the strongest impact on the NPV. Part B: The model as currently built has a number of bugs. Your task in this part is to find these bugs. The only types of bugs you are to look for include: wrong input: a numerical input does not correspond to the problem description hard-coded input: the numerical value of an input appears in a formula (this excludes the value zero in a cell) reference: a formula uses an incorrect cell reference (i.e., points to the wrong cell) logic: a formula is used incorrectly. You should assume the accounting logic of the model is correct unless it explicitly contradicts the problem statement. Thus, for example, negative taxes are allowable in row 81. List the cell address or addresses of the bugs you find below and give a brief explanation of why it is an error and how you would fix it. Cell Address(es) Explanation and Fix Etc… _____________________________________________________________________________ 3. Finding bugs in the Material X model Open the workbook MaterialBugs.xlsx. This model was built to estimate the market potential for a consumer product that uses Raw Material X as a key ingredient. Unfortunately, this model has bugs.
4. Coordinating advertising and production The Hawley Lighting Company manufactures four families of household lighting at its factory. The product families are table lamps, floor lamps, ceiling fixtures, and pendant lamps. The table below shows the average material costs for each of the products. Product Table Floor Ceiling Pendant Material cost $66 85 50 80 Each product is made in one of two production processes. Table lamps and floor lamps go through the process in Department 1, while ceiling fixtures and pendants go through the process in Department 2. Variable production costs and capacities (measured in units of product) are shown in the table below. Note that there are regular and overtime possibilities for each department. Regular time Overtime Capacity Capacity Process Unit cost in units Unit cost in units Department 1 $16 100,000 $18 25,000 Department 2 12 90,000 15 24,000 Average selling prices for the four products are known, and estimates have been made of the market demand for each product at these prices. These figures are shown in the following table. Product Floor Ceiling Pendant Selling price $120 150 100 160 Potential 60,000 20,000 100,000 35,000 a. What is the optimal production plan for the company? (Give the optimal profit and how much of each product to produce on regular time and on overtime.) Hint: use four decision variables for production of each product in regular time, and four decision variables for production of each product in overtime. b. Advertising raises an additional complication because sales levels can be affected by advertising expenditures. Each $1,000 spent on advertising for a particular product raises demand above the base level by the percentage given below. For example, an expenditure of $5,000 on advertising for table lamps would raise demand by 6% (5x1.2%), or 3,600 units (60,000x6%). (Expenditures of fractions of $1,000 have proportional effects.) However, there is a budget limit of $18,000 on the total amount to be spent on advertising among all four products. Product Table Floor Ceiling Pendant Advertising Effect 1.2% 1.0% 0.8% 1.5% Amend your model to include the advertising decisions and use it to determine the optimal production and advertising plan for the company. (Give the optimal profit, how much of each