Lorenzo, the owner of a local poster shop, cornes to you for help. While his shop has been breaking even for the past two years, it has not been able to generate a profit. For him to keep the shop open, he needs to earn at least $12,000 in operatine income next year. You agree to help Lorenzo and ask him for some current information about his products' selling price and costs You tell him you will work through some possible scenarios that might involve changing his sales price to generate the number of units sold needed to reach his target profit. Lorenzo shares the following information with you as vou ponder different scenarios to help your client. Using torenzo's date and proper Excel formulas, first, plan to get an understanding of torenzo's financial situation based on breakeven. Then look at the following five pricing scenarios: 1. Lower the selling price by 10% to increase sales volume by 5%. 2. Advertise on radio and social media for a combined cost of $1,000 to increase volume by 10$ 3. Use a more affordable paper on which to print the posters (available for $0.60 per unit), in combination with a less-expensive film to coat the surface of the poster favallable for $0.40 per unit). 4. Instead of paying the salespeople a fixed salary, move to a commission-based compensation plan (save $20,000 in salary, incur $1.50 per unit sold commission), which should increase sales volume by 20% 5. Advertise on radio and social media for a combined cost of $1,000 and, instead of paying salespeople a foxed salary, move to a commission-based compensation plan (save $20,000 in salary, incur $1.50 per unit sold commission), which could increase sales volume by 25%. 1. Prepare an accurate contribution margin income statement based on breakeven using correct Excel formulas. 2. Perform a cost volume profit analysis for all scenarios using correct Excel formulas. There are five scenarios. Include the following details in your response: A. Calculate the effects on costs and sales volume for each scenario. B. Prepare an accurate contribution margin income statement for each scenario. C. Calculate the after-tax effect of each proposed scenario. 3. Determine the effect of the price increase based on the market for all scenarios using correct Excel formulas. Include the following details in your response: A. Calculate the after-tax effect on sales for each scenario. B. Prepare a contribution margin income statement for each scenario. 4. Evaluate a special-order option using correct Excel formulas. Include the following detall in your response: A. Calculate accurately the after-tax profitability of the special order. B. Prepare an accurate contribution margin income statement for the company if the special order is accepted. Stakeholder Presentation 1. Explain the key points to communicate to the stakeholders. Include the following details in your response: A. Summarize the company's request. B. Explain the benefits and advantages to the company for each scenario. C. Explain the disadvantages to the company for each scenario. 2. Present the best possible recommendation(s) for the company moving forward based on cost data analysis. Include the following details in your response: A. Determine which scenario is the best for Lorenzo's company. B. Determine if Lorenzo should accept the special order. C. Provide your rationale. 3. Explain the potential impact of decisions on business and workforce relationships. Include the following details in your response: A. Expiain the potential effects of going from a salary to commission based salesforce. B. Explain the potential effects of downgrading the quality of supplies for customers' orders in the short and long tern. C. Explain how a potential price increase might affect customers short and long term. 4. Utilize effective basic visualizations that support your tationale. 5. Create a simple, neat, and clearly articulated presentation with narration for the needs of the stakeholders
