Mark Make-a-Buck sells jars of beach sand in Florida. His price per jar is $2.50. His historical costs have been: What is Mark's breakeven point for the year, in terms of both quantity and sales dollars on a pretax basis? Mark wants to make a target profit 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%. Mark generated revenues of $50,000 in 2021 . What was his margin of safety in both dollars and sales quantity? What was Mark's operating leverage in 2021? Use the Excel Goal seek function to prove that you calculated the correct numbers in 1a. and 1b. above. 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 profitable. Prepare total year 2022 income statements for the following scenarios (one column per scenario): Sales of beach sand are 750 jars per month with current cost structure. This column should he lahelled "Race cace". Mark is concerned about 2022, as global warming may reduce his ability to arvest 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 ecome profitable. Prepare total year 2022 income statements for the following cenarios (one column per scenario): Sales of beach sand are 750 jars per month with current cost structure. This column should be labelled "Base case"; 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". Mark takes no salary. Call this scenario "no salary". 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". 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. 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". What 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" function 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. 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 efficient and if you would use them with this type of analysis in the real world. Mark Make-a-Buck sells jars of beach sand in Florida. His price per jar is $2.50. His historical costs have been: What is Mark's breakeven point for the year, in terms of both quantity and sales dollars on a pretax basis? Mark wants to make a target profit 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%. Mark generated revenues of $50,000 in 2021 . What was his margin of safety in both dollars and sales quantity? What was Mark's operating leverage in 2021? Use the Excel Goal seek function to prove that you calculated the correct numbers in 1a. and 1b. above. 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 profitable. Prepare total year 2022 income statements for the following scenarios (one column per scenario): Sales of beach sand are 750 jars per month with current cost structure. This column should he lahelled "Race cace". Mark is concerned about 2022, as global warming may reduce his ability to arvest 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 ecome profitable. Prepare total year 2022 income statements for the following cenarios (one column per scenario): Sales of beach sand are 750 jars per month with current cost structure. This column should be labelled "Base case"; 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". Mark takes no salary. Call this scenario "no salary". 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". 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. 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". What 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" function 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. 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 efficient and if you would use them with this type of analysis in the real world