Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Mark is very interested in beach sand in Florida. He wants to start a business selling jars of beach sand to tourists. His sales price

Mark is very interested in beach sand in Florida. He wants to start a business selling jars of beach sand to tourists. His sales price per jar is $2.50 and estimated costs are:

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

Marks salary (President) $1,000 per month

  1. a. What is Marks breakeven point for the year, in terms of both quantity and sales dollars on a pretax basis?

b. Mark wants to make a target profit of $25,000 after tax for the year. How many jars of beach sand would he need to sell? Marks tax rate is 20%.

c. If Mark can generate revenues of $50,000, what is his margin of safety in both dollars and sales quantity?

d. If Mark generates revenues of 50,000 what is his operating leverage?

  1. Use the Excel Goal seek function to prove that you calculated the correct numbers in 1 a. and 1b. above. Copy and paste the results to the calculations worksheet.

  1. Mark is concerned about a potential slowdown in tourism. He wants to run some scenarios and figure out what changes he may need to make to become profitable. Prepare total year 2023 income statements for the following scenarios (one column per scenario):

  1. Sales of beach sand are 750 jars per month with current cost structure. This column should be labelled Base case;
  2. 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.
  3. Mark takes no salary. Call this scenario no salary. Sales remain at 750 jars per month.
  4. Mark wants to run an optimistic scenario showing what profits will be if sales increase to 1,250 per month. Call this scenario higher sales. Keep the same cost structure as the base case.

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.

  1. Mark is concerned about erosion on the beach due to increasingly severe storms. He is working with the City of Saint Pete Beach to estimate costs for the year due to potential beach erosion. The city has provided Mark data regarding storms for 2022; this is included in the excel worksheet Storm Data.
  • Using this data, construct a pivot table to estimate total labor costs to restore beach sand. Your rows should be the storm ID. Your columns should be Cubic miles eroded, Minutes to restore sand and Driver rate.
  • Calculate the following using results from your pivot table. Note that these will be separate calculations based on outputs from your pivot table.

- Total labor cost to restore beach sand

- Labor cost per cubic mile restored

  1. Conclusion. Should Mark open this 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.
  2. Summarize your results in a table as follows:

Worksheet Format

  1. Set up your Excel workbook with 5 worksheets
  • Input and conclusion (requirement 5)
  • 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
  • Data, pivot table (you are given the data and must create the pivot table) and cost estimates for City of St. Pete

  1. For the input area, include your prices, variable costs, fixed costs, sales volumes and scenario assumptions.

GRADING

1. Excel set up properly with 5 separate worksheets within the workbook. Workpapers easy to follow (show your work). Uses linking appropriately 5

2. Requirement #1 6

3. Requirement #2 2

4. Requirement #3 4

5. Requirement #4 4

5. Well worded conclusion, correct spelling and grammar and logical reasoning. 4

STORM ID Cubic miles eroded Driver ID Minutes to restore Driver rate per hour
1 7 1 339 19.5
3 14 3 656 22
7 17 2 797 21.5
4 8 1 374 19.5
1 11 1 527 19.5
6 18 2 844 21.5
5 9 1 421 19.5
5 12 3 562 22
9 15 2 703 21.5
12 10 3 468 22
9 13 3 609 22
9 16 2 750 21.5
1 7 1 339 19.5
12 14 1 656 19.5
3 17 3 797 22
9 8 2 374 21.5
5 11 1 515 19.5
6 18 1 844 19.5
12 9 2 421 21.5
8 12 1 562 19.5
9 15 3 703 22
3 10 2 468 21.5
11 13 3 609 22
12 16 3 750 22
6 7 2 327 21.5
2 14 1 656 19.5
3 17 1 797 19.5
9 8 3 374 22
5 11 2 515 21.5
6 18 1 844 19.5
12 9 1 421 19.5
8 12 2 562 21.5
9 15 1 703 19.5
3 10 3 468 22
11 13 2 609 21.5
5 16 3 750 22
1 7 3 339 22
2 14 2 656 21.5
8 17 1 797 19.5
4 8 1 374 19.5
5 11 3 515 22
11 18 2 844 21.5
7 9 1 421 19.5
1 12 1 574 19.5
9 15 2 703 21.5
10 10 1 468 19.5
4 13 3 609 22
12 16 2 750 21.5
1 16 3 762 22
7 14 3 656 22
3 17 2 797 21.5
4 8 1 374 19.5
10 11 1 515 19.5
6 18 3 844 22
7 9 2 421 21.5
1 12 1 574 19.5
9 15 1 703 19.5
3 10 2 468 21.5
11 13 1 609 19.5
12 16 3 750 22
6 7 2 327 21.5
2 14 3 656 22
3 17 3 797 22
9 8 2 374 21.5
5 11 1 515 19.5
11 18 1 844 19.5
7 9 3 421 22
8 12 2 562 21.5
2 15 1 723 19.5
10 10 1 468 19.5
4 13 2 609 21.5
12 16 1 750 19.5
1 15 3 730 22
2 18 2 844 21.5
8 9 3 421 22
4 16 3 750 22
5 8 2 374 21.5
11 10 1 468 19.5
7 13 1 609 19.5
1 9 3 433 22
9 12 2 562 21.5
10 14 1 656 19.5
4 17 1 797 19.5
12 13 2 609 21.5
6 16 1 750 19.5
2 18 3 844 22
3 18 2 844 21.5
4 9 3 421 22
10 12 3 562 22
6 7 2 327 21.5
7 10 1 468 19.5
2 13 1 609 19.5
9 16 3 750 22
4 11 2 535 21.5
11 14 1 656 19.5
12 17 1 797 19.5
7 20 2 938 21.5
3 15 1 703 19.5
9 18 3 844 22
5 16 2 750 21.5
6 8 3 394 22
7 11 3 515 22
1 14 2 668 21.5
9 9 1 421 19.5
10 32 2 1502 21.5
4 15 2 703 21.5
12 27 1 1267 19.5
6 13 1 609 19.5
2 11 1 535 19.5
3 9 3 434 22
9 12 2 562 21.5
5 17 1 797 19.5
11 18 1 844 19.5
7 11 2 515 21.5

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Fundamental Accounting Principles Volume 2

Authors: Kermit Larson, Heidi Dieckmann

15th Canadian Edition

1259087360, 9781259087363

More Books

Students also viewed these Accounting questions