Question

Use the following estimated information for Super Shirts, Inc. to perform the analyses: Revenues s215,000 Cost of goods sold-20% of revenues Salaries and wages-20% of revenues Additional costs Operating Costs-$39,000 Depreciation $2,400 Bad debts expense-1% of 70% of revenues Interest expense - $525 REQUIRED: On the basis of the facts as given above, prepare the following schedules using a spreadsheet software package: A. Super Shirts would like to be more profitable. Use Goal Seek in Excel to determine the following changes. Each situation is independent of the others. 1. How much would the cost of goods sold percentage need to be for net income to be $100,000? 2. Assume original percentages, how much would the salaries percentage need to be for net income to be $100,000? B. GO BACK TO THE ORIGINAL DATA. Use Solver in Excel to determine the following assuming these constraints (COGS not more than 30% or less than 10%, Salaries not more than 25% or less than 15%; 1. Assuming both must change, what percentages would the COGS and 2. What is the maximum net income Super Shirts can achieve with the 3. What is the minimum net income Super Shirts can achieve with the the salaries percentages have to be in order to get $100,000 net income? above constraints? above constraints? C. GO BACK TO THE ORIGINAL DATA Use Solver in Excel to determine the following: I. Assuming COGS-20% and Salaries-20%, how much would revenues have to be to earn S100,000 net income? 2. Assuming COGS 22% and Salaries 25%, how much would revenues have to be to earn $100,000 net income? D. GO BACK TO THE ORIGINAL DATA Use Scenarios in Excel. Prepare and print a Scenarios Summary Report for net income with the following options: (Each case is independent.) 1. Best case-Sales twice the original amount. 2. Worst case - Sales are ' as much as original amount. 3. Lower COGs-: 15% instead of 20%. 4. Change sales, COGS, and salaries: Sales increase by 30% of original amount, COGS-25%, higher salaries-25%. (1) For your Scenarios Summary Report, be sure to format your Scenarios Summary (2) Conditional format your net income by highlighting with your choice of color any by putting titles in for the variables. incomes above $100,000. Also format your net income with another color choice for those incomes below the current net income.

Answer 1

Answer A1:

Current profit = 85570 after deducting all expenses

New profit to achieved = 100000

Additional cost reduction required = 100000-85570 = 14430

So Revised COGS = 43000 (20% of 215000) – 14430 = 28570 i.e. 13.29% of Revenue

Answer A2:

Current profit = 85570 after deducting all expenses

New profit to achieved = 100000

Additional cost reduction required = 100000-85570 = 14430

So Revised Salaries and wages expenses = 43000 (20% of 215000) – 14430 = 28570 i.e. 13.29% of Revenue

Answer B1:

Using solver:

COGS should be 18.29% of revenue and salaries will be 15% of revenue or vice versa or both can be 16.67% of revenues.

Answer B2:

Maximum profit could be 117820 in case of minimum cost i.e. 10% COGS and 15% salaries.

Answer B3:

Minimum profit could be 53,320 in case of Maximum cost i.e. 30% COGS and 25% salaries.

Answer C1: you can also use goal seek for this one.

Revenue should be 239333.8954 in order to get profit of 100000.
Answer C2:

Revenue should be 271367.1128 in order to get profit of 100000.

Note: As per policies I should answer 4 subparts, I have answer more than that. Due to time problem can’t solve any more.