DUE: Oct 19 (REVISED FOR IAN) Objective: - Increase understanding of cost-volume-prot analysis and relevant costing - Practice using Excel - Formulate well-written conclusion using proper writing techniques. Mark Make-a-Buck sells jars of beach sand in Florida. His price per jar is $2.50. His historical costs have been: Jar $.40 per item Cap $.10 per item Label $.30 per item Sales commission to retailers $.20 per item Excavating machine depreciation $500 per month Mark's salary (President) $1,000 per month 1. a. What is Mark's breakeven point for the year, in terms of both quantity and sales dollars on a pretax basis? b. Mark wants to make a target prot of $20,000 after tax for the year. How many jars of beach sand would he need to sell? Mark's tax rate is 20%. c. Mark generated revenues of $50,000 in 2021. What was his margin of safety in both dollars and sales quantity? d. What was Mark's operating leverage in 2021'? 2. Use the Excel Goal seek function to prove that you calculated the correct numbers in 1 a. and 1b. above. 3. Mark is concerned about 2022, as global warming may reduce his ability to harvest beach sand. He feels that he will only be able to sell 750 jars per month. He wants to run some scenarios and figure out what changes he needs to make to become protable. Prepare total year 2022 income statements for the following scenarios (one column per scenario): a. Sales of beach sand are 750 jars per month with current cost structure. This column should be labelled \"Base case\"; b. Mark uses a cheaper label and jar, reducing those costs 20% each. Sales are at 750 units per month. Call this column (scenario) \"lower costs\". c. Mark takes no salary. Call this scenario \"no salary\". d. Mark wants to run an optimistic scenario showing what profits will be if he increases sales to 1,250 per month. Call this scenario \"higher sales\". Use the \"scenario manager\" function in Excel to track these scenarios. This is located under \"Data\" \"Goal seek" \"Scenario Manager". Create your 4 scenarios and run the summary. The summary page should be called \"Scenario summary\". 4. Mark is considering outsourcing the production of the jars of sand. He has a quote from an outside supplier for a price of $1.75 per jar. Mark would still incur the commission to retailers and excavating machine depreciation. a. If Mark would reduce his salary to $500 per month, would this outsourcing be feasible. Use one column to calculate an income statement for this scenario; this is your \"outsource scenario\". b. that price per jar would Mark need from the supplier to be indifferent to producing the sand himself and accepting their offer? Use the \"Goal seek\" lnction to calculate this amount. Set up your Goal seek calculations in a separate column; all numbers should be the same as your Outsource scenario except the supplier's cost per jar. 5. Conclusion. What do you think Mark should do? Should he remain in the business? Be detailed and state your reasons logically. Also include your opinion as to whether you felt using the \"Goal seek\" and \"Scenario Manager\" were efcient and if you would use them with this type of analysis in the real world. Worksheet Format Set up your Excel workbook with 6 worksheets 1. Input Calculations (requirements 1 and 2 above) Scenarios there are 4 scenarios (requirement 3 above) Scenario summary This will be the result of using the \"Scenario Manager\" in Excel Outsourcing requirement 4 above Conclusion requirement 5 above For the input area, include your prices, variable costs, fixed costs, sales volumes and scenario assumptions