Happy Gift Company designed a new gift box set for the upcoming Christmas holiday. The fixed cost to produce new gift box sets is $150,000. The variable cost is $35 per new gift box set. During the Christmas holiday sale, the company will be selling new gift box sets for $45 each. If the company overproduces new gift box sets, the excess new gift box sets will be sold in January through a distributor who has agreed to pay the company $15 per new gift box set. The company has tentatively decided to produce 70,000 units and assumed the demand is also 70,000 units (at the initial stage), but it wants to conduct an analysis regarding this production quantity before finalizing the decision. (2a) Create a model in an Excel workbook with formulas that relate the values of quantity produced, demand, quantity sold, sales revenue, surplus quantity, surplus revenue (revenue from sales of surplus), total cost, and net profit in an excel workbook. (2b) What is the profit when the demand is 85,000 units? (2c) Modeling demand as a normal random variable with a mean of 70,000 and a standard deviation of 10,000, simulate the sales of the new gift box sets using a quantity produced of 70,000 units. Run 1000 trials with the estimated profit. What is the estimate of the average profit associated with the quantity produced of 70,000 new gift box sets? (2d) Before making a final decision on the production quantity (quantity produced), management wants an analysis of a more aggressive 90,000-unit production quantity (quantity produced) and a more conservative 55,000-unit production quantity (quantity produced). Run the simulation with these two quantities. What is the average profit associated with each? Interpret the findings. Question 3 (See Lecture 8 slides 12-17) Speedway is a global management consulting firm considering expanding and needed a model to analyse the decision to open a new store. Your task is to develop a model that calculates the net present value of profitability over a five-year period using a discount rate of 4% based on the data given. - The firm's target markets are communities with a population of 55,068. - The price for each service (Unit Price) is $120 in the first year. - The price increases (Price Growth Rate) each year at a rate that is normally distributed with a mean of 4% and a standard deviation of 1%. - The variable cost is $45 per customer in the first year - The variable cost increases (Variable Cost Growth Rate) increase annually at a rate that is normally distributed with a mean of 5.5% and a standard deviation of 6.5%. - Fixed costs are estimated to be approximately $85,000 for the first year and grow annually at a rate between 1.8% and 5.6% (Fixed Cost Growth Rate). - The first-year demand (Demand) is approximately 8% of households are anticipated to use the service. - Demand grows (Demand Growth Rate) fairly aggressively in the second and third year and is assumed to have a triangular distribution with a minimum value of 19%, most likely value of 29%, and a maximum value of 39%. After year 3, demand growth is between 6% and 16%, with a most likely value of 7%. • Note: "Round up" the demand to the nearest integer. Based on 1000 simulation trials, compute summary statistics for the average net present value of the profitability over a five-year period and interpret the findings. Note: - Create a spreadsheet (A2_Q3) showing the calculations of your model. - Please include the results in the report along with a screenshot of the relevant information (e.g., data, model, distributions table, partial data tables, or output) in your A2_Q3.
