data analytics question and need the explanation and answer to help me learn.
look question carefully
Requirements: 1
All questions in this assignment pertain to the short case “Pricing an Online Payroll Calculator”. Please analyze consumer preferences using conjoint analysis and determine the market’s price-demand relationship to answer questions pertaining to a variety of possible pricing designs contingent on a given product line and market structure.
Although preliminary analysis of customer preference data suggested a highly profitable transition from free service to a paid subscription, withdrawing free online service entails the risk of new entrants poaching small business customers with free services. Brand X’s free online services was also instrumental in maintaining a strong brand visibility for several years despite modest profits. As a result, management is concerned about a possible backlash to withdrawal of the free calculator from loyal customers especially those that are insensitive to ads. The marketing division is therefore tasked with identifying a pricing design for a product line that includes both free and paid services.
Please help Brand X analyze pricing designs involving the following scenarios.
Status Quo Scenario: Brand X offers a free online calculator with digital display ads and Brand C offers a subscription-based payroll calculator with unlimited automated calculations for a high price of $79.99.
Scenario I: Brand X retains its free service and introduces a subscription-based service providing up to 300 automated calculations to take advantage of possible price discrimination through versioning.
Scenario II: Brand X retains its free service and introduces two additional subscription-based services: one providing up to 300 automated calculations and the other providing an ad free environment for manual calculations targeted at ad sensitive customers.
Under Scenario I, if Brand X sets the price of the paid version at $24.99/annum (i.e. profile #5), which of the following statements is true based on your demand-based pricing analysis?
A. The paid version will capture more than 20% of the total market.
C. Brand X likely reclaims more than 10% of the total market from the competing brand.
D. None of the above
Under Scenario I, if Brand X sets the price of the paid version at $24.99/annum (i.e. profile #5), which of the following statements is true based on your demand-based pricing analysis? [select all that apply]
B. Brand X dominates the market in terms of both total market share and revenues compared to status quo.
C. Brand X’s revenue decreases compared to status quo scenario.
D. None of the above
Under Scenario I, if Brand X sets the price of the paid version at $24.99/annum (i.e. profile #5), which of the following statements is true based on your demand-based pricing analysis? [select all that apply]
A. The paid version is likely to draw a small proportion of the market share from the 2nd segment of the market that used the free services (cannibalization).
B. Competing brand C is still the dominant player in terms of market share with respect to the 1st and 3rd segment of the market.
C. Competing brand C is now the dominant player in terms of market share only with respect to the 1st segment of the market.
D. None of the above
Under Scenario I, which of the following statements is true concerning the price-demand relationship (i.e., demand as a function of price) for the paid version of Brand X’s payroll calculator?
A. The demand curve is characterized by a downward slope that is steep initially followed by a gradual tapering.
C. The demand curve is characterized by a downward slope that is approximately linear.
D. None of the above
Under Scenario I, which of the following statements is true concerning the price-demand relationship for the paid version of Brand X’s payroll calculator?
A. Maximum possible revenue from the paid version lies beyond a price point of $20.
B. Maximum possible revenue from the paid version lies below the price point of $20.
D. None of the above.
Under Scenario I, what is the optimal price to maximize Brand X’s total revenue (Please use Excel solver to do revenue maximization assuming zero marginal cost)?
A. [$19, $20]
B. [$23, $24]
D. None of the above.
Under Scenario I, which of the following statements is true concerning the optimal price for the paid version of the product?
A. Total revenue of Brand X under optimal price is [$955900, $956000]
B. Total revenue of Brand X under optimal price is [$570000, $580000]
C. Total revenue of Brand X under optimal price is [$382100, $382200]
Under Scenario I, what is the optimal price subject to the constraint that Brand X’s paid version has at least 20 percent of the total market? (Please use excel solver to maximize total revenue with added inequality constraint).
B. [$19, $20]
C. [$18, $19]
D. None of the above.
Under Scenario II, what are the optimal prices to maximize total revenue? (Please use excel solver to optimize two prices simultaneously based on revenue maximization).
A. [$17, $18] for ad-free manual calculator, [$23, $24] for up to 300 automated calculators.
B. [$24, $25] for ad-free manual calculator, [$17, $18] for up to 300 automated calculators.
C. [$17, $18] for ad-free manual calculator, [$24, $25] for up to 300 automated calculators.
Under Scenario II, which of the following statements is true at optimal prices?
A. The free service of Brand X continues to retain the majority of the market share.
B. Brand X’s total revenue is less than what it was under a 2-product product-line (i.e. without an ad free manual calculator).
D. None of the above.
Under Scenario II, what is Brand X’s total revenue at optimal prices?
A. [$951200, $951300]
B. [$1010600, $1010700]
D. None of the above.
Under Scenario II, what is true concerning Brand C at optimal prices for Brand X? [select all that apply]
A. Brand C loses more than 30% of its market share under status quo.
B. Brand C’s revenue is more than twice that of Brand X
C. Brand C gains more than 30% of its market share under status quo.
D. None of the above.
Under Scenario II, what will be the consequence of a 50% drop in ad-revenue per customer for Brand X?
A. Brand X’s total revenue drops by more than 25%.
C. Brand X’s total revenue drops by a mere 1%.
D. None of the above.
Under Scenario II, what are the optimal prices subject to the additional constraint that the free version’s market share remains less than 25% of the total market? (Please use excel solver to maximize total revenue optimizing two prices simultaneously with an added inequality constraint)
A. [$16, $17] for ad-free manual calculator, [$4, $5] for up to 300 automated calculations.
B. [$4, $5] for ad-free manual calculator, [$16, $17] for up to 300 automated calculations.
D. None of the above.
Under Scenario II, which of the following statements is true concerning optimal prices?
B. Applying a maximum market share constraint of 25% for the free version (as in Q16) will ensure that a greater proportion of the revenue is generated by the paid versions.
C. Applying a maximum market share constraint of 25% for the free version (as in Q16) increases both the total market share and revenue of competing Brand C.
D. None of the above.
16 [Extra Credit] Under Scenario II, assume that segment 2 is predominantly comprised of small businesses with less than 10 employees and therefore can be priced differently using ‘size of business’ as justification for discounts. What is true concerning price discrimination at the segment level? (Please assume that Segment 1 and Segment 3 are both comprised of medium to large businesses and are indistinguishable for pricing purposes)
A. Under optimal prices, segment-level price discrimination allows total revenue for Brand X to exceed $1.3 million.
B. Optimal prices are possibly infeasible owing to consumer fairness concerns as the price for manual ad-free calculators vastly exceeds the price for calculators with up to 300 calculations.
D. None of the above.
20) Please upload your Excel worksheet here. Please include your name and id for ease of evaluation. Thank you!
1 Short Case (B2B): Pricing an Online Payroll-Calculator A software development firm (Brand X; privately held) specializes in building custom software for tax and payroll calculations for the US business market. Although their main source of revenue is from selling pre-packaged software and support services to large business services and human resources consulting firms, they had been enjoying a steady source of revenue from their free online payroll calculators catering to small businesses. They generate revenue through display ads totaling approximately $600,000 in the most recent fiscal year. They had around 60,000 unique businesses use their services in the past year, a steep drop in numbers from previous years. The clientele included businesses from all 50 states. Ad revenue has been dwindling over the years and the future of the product is in jeopardy given the cost of annual updates to the website necessary to keep the calculator current with respect to changing rules, regulations, and laws. A recent meeting convened by top management to discuss the loss of market share and a possible future course of action has been inconclusive. There were conflicting opinions on what ails the product. Although a new competitor (Brand C) that has entered the market recently is the most likely culprit, they charge a high price of $79.99 for annual subscriptions compared to Brand X’s free service. The only difference between Brand X and Brand C is that the latter is able to handle automated calculations for a large number of employees using their proprietary tool (they offer unlimited calculations for $79.99). In contrast, Brand X supports only manual calculations. However, the product manager claims that the maximum number of calculations ever performed by a customer was less than 100. The marketing head opined that perhaps the digital ads annoyed customers prompting the switch to a paid service but was skeptical that the willingness to pay was as high. A key impediment to an evidence-based change in marketing strategy is the lack of research on customer preferences for payroll calculator features and the distribution of their maximum willingness to pay. Although the current service is still profitable given the almost zero marginal cost, the dwindling ad revenue and, more importantly, the possibility of a high-priced competitor poaching extant customer base have created a pricing conundrum. Was the firm missing untapped consumer surplus all along? Can the brand move to a paid service without losing market share? Is there a scope for multiple versions of the same product to be priced differently? Although it is hard to match the new entrant’s features, the product team is confident of enhancing the product to cater up to 300 automated and customized calculations. However, is there a market for such an enhancement?
2 Data from a preliminary conjoint study conducted by the marketing manager during a recent trade show is available below. Customers that showed interest in Brand X’s payroll calculators were requested to rank order 12 different product profiles each varying in the number of calculations (manual, 300 (automated), and unlimited), presence of digital advertising (Yes or No), and annual subscription charges for paid services ($24.99 and $59.99). Three types of preference ranking covered almost the entire dataset (a small percentage of unique ranks is not reported) each representing 30%, 60% and 10% of the sample respectively. For your analysis, please assume an annual ad revenue per user as $10 and the total market size as 100000 businesses. Also, assume that the sample is representative of the market i.e. it is a simple random sample of adequate size. Product Profiles Customer Preferences* # #calculations Display ads Price+ seg_1 (30%) seg_2 (60%) seg_3 (10%) 1 manual No 24.99 4 12 8 2 manual Yes 24.99 3 11 2 3 manual No 59.99 2 2 7 4 manual Yes 59.99 1 1 1 5 automated(300) No 24.99 8 10 10 6 automated(300) Yes 24.99 7 9 4 7 automated(300) No 59.99 6 4 9 8 automated(300) Yes 59.99 5 3 3 9 unlimited No 24.99 12 8 12 10 unlimited Yes 24.99 11 7 6 11 unlimited No 59.99 10 6 11 12 unlimited Yes 59.99 9 5 5 + Price levels are chosen based on qualitative interviews with current customers and users of competitor website. Current customers were asked about their willingness to pay for continuation of service owing to unsustainable ad revenues. Users of competitor products were asked about the price at which they are likely to switch. Random bids were used to ascertain the lower limit of prices the customers deemed unreasonable. * Ranks are reverse coded. Ranks 12 and 1 indicate most and least preferred respectively.
