!!!PLEASE USE EXCEL SO I CAN LEARN IT!!!
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 Worksheet Format 1. Set up your Excel workbook with 4 worksheets Input Calculations (requirement 1 above) Scenarios - there are 3 scenarios (requirement 2 above) Summary and conclusion 2. For the input area, include your prices, variable costs, fixed costs, sales volumes and scenario assumptions. 3. The calculations and scenarios tab must reference back (link) to the input tab. 4. The summary must look like this: Question 1: Quantity XX Revenues Other XX Breakeven Point Target profit $2,000 XX NA Margin of Safety XX XX Operating leverage NA NA XXX Question 2: Operating Income Scenario 1 XX Scenario 2 XX Scenario 3 xx Conclusion: Link all your numbers to your calculation tabs. GRADING 1. Excel set up properly with 4 separate worksheets within the workbook. Uses linking 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 Worksheet Format 1. Set up your Excel workbook with 4 worksheets Input Calculations (requirement 1 above) Scenarios - there are 3 scenarios (requirement 2 above) Summary and conclusion 2. For the input area, include your prices, variable costs, fixed costs, sales volumes and scenario assumptions. 3. The calculations and scenarios tab must reference back (link) to the input tab. 4. The summary must look like this: Question 1: Quantity XX Revenues Other XX Breakeven Point Target profit $2,000 XX NA Margin of Safety XX XX Operating leverage NA NA XXX Question 2: Operating Income Scenario 1 XX Scenario 2 XX Scenario 3 xx Conclusion: Link all your numbers to your calculation tabs. GRADING 1. Excel set up properly with 4 separate worksheets within the workbook. Uses linking
