DUE: Oct 12

Objective:

- Increase understanding of cost-volume-profit analysis

- Practice creating a pivot table

- Practice using Excel

- Formulate well-written conclusion using proper writing techniques.

Gladys wants to start a merchandising company selling beach shirts online. She plans to purchase the shirts, add custom iron-on patterns, and sell online via retailers. Gladys plans to do all the work herself, so wont need to hire anyone. Her sales price per shirt is $22.50 and estimated costs are:

Shirt$10.00 per item

Custom iron on$1.00 per item

Shipping labeland postage$2.50 per item

Sales commission to online retailers20% of selling price per item

Iron press rent$500 per month

Gladyss salary (President)$1,000 per month

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

b. Gladys wants to make a target profit of $25,000 after tax for the year. How many shirts would he need to sell? Gladyss tax rate is 20%.

c. If Gladys can generate revenues of $108,000, what is her margin of safety in both dollars and sales quantity?

d. If Gladys generates revenues $108,000, what is heroperating leverage?

2. 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. You will have two goal seek calculations showing on your calculations worksheet, as well as the calculations in 1 above.

3. Gladys is concerned about a potential market slowdown. She wants to run some scenarios and figure out what changes she may need to make to becomeprofitable. Prepare total year 2023 income statements for the following scenarios (one column per scenario):

a. Sales of shirts are 400 units per month with current cost structure. This column should be labelled Base case;

b. Gladys uses a cheaper label and shipping method, reducing those costs 20%. Sales are at 400 units per month. Call the column (scenario) lower costs.

c. Gladys takes no salary. Call the scenario no salary. Sales remain at 400 shirts per month.

d. Gladys wants to run an optimistic scenario showing what profits will be if sales increase to 1,000 shirts per month. Call the 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.

4. Gladys is concerned about the cost of shirts rising in this inflationary environment. She has budgeted $10 per shirt but is unsure where to purchase them from. She has obtained a database showing purchase information from 2021 for a similar shirt from various vendors located throughout the world. This database is called DataSetfor Gladys shirts.

Using this data, construct a pivot table. Your rows should be the vendor location. Your columns should be the number of shirts purchased and the total costs.

Calculate the cost per unit for each vendor using your pivot table. This calculation should be outside of your pivot table (separate cells).

Highlight the lowest cost vendor with yellow. Highlight the highest cost vendor using orange.

5. Conclusion. Should Gladys open the business? What other factors should Gladys consider? Be detailed and state your reasons logically.

what are my inputs for data need help

need help resolving