Question

Mark Make-a-Buck sells jars of beach sand in Florida. His price per jar is $2.50. His historical costs have been: Jar Cap Label Sales commission to retailers Excavating machine depreciation Mark's salary (President) $.40 per item $.10 per item $.30 per item $.20 per item $500 per month $1,000 per month 1. a. What is Mark's breakeven point for the month, in terms of both quantity and sales dollars on a pretax basis? b. Mark wants to make a target profit of $2,000 after tax for the month. How many jars of beach sand would he need to sell? Mark's tax rate is 20%. c. Mark generated revenues of $5,000 in September, 2019. What was his margin of safety in both dollars and sales quantity? d. What was Mark's operating leverage in September 2019? 2. Mark is concerned about 2020, as tourism is down due to the pandemic. He feels that he will only be able to sell 500 jars per month. He wants to run some scenarios and figure out what changes he needs to make to remain profitable. Prepare income statements for the following scenarios (one column per scenario): a. Sales of beach sand fall to 500 jars per month with current cost structure; b. Mark uses a cheaper label and jar, reducing those costs 20% each. Sales are at 500 units per month; c. Your choice. Run a scenario detailing what factors you would recommend that Mark look into changing. Assume sales at 500 jars per month. 3. Conclusion. What do you think Mark should do? Should he remain in the business? Be detailed and state your reasons logically. Worksheet Format 1. Set up your Excel workbook with 4 worksheets Input Calculations (requirement 1 above) Soneran bor2 monorairausant bote
!!!PLEASE USE EXCEL SO I CAN LEARN IT!!!