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