Question

Nielsen’s Fine Clothing Store

Please do this problem in excel.

You are the owner of a general retail-clothing store. Below is the income statement for last year which is not expected to change at all during the upcoming fiscal year. You have one full time manager, two three quarter time assistant managers and 3 part time clerks. You have had the manager and assistant managers for the past five years. Five years ago sales were only 350,000 dollars. As sales have increased you have added the part time clerks. You anticipate adding more clerks as sales increase in the future. You pay you credit card vendor 1 percent of sales. Bad debt expense and shrinkage are also estimated as a percentage of sales. The Mall common area payment must be paid regardless of how many sales you make

1. Please calculate the pretax breakeven point in sales dollars from the formula in the online lecture on breakeven analysis. Use the formula for breakeven sales dollars. Do not use the formula for breakeven in units. As part of this analysis list each expense item and identify whether it is a fixed or a variable expense. (20 points)

2. What happens to your breakeven point if your primary supplier of clothing raises prices 5%? You buy 45% of your clothing from this company. (10 points)

3. Your manager has received an offer of employment from a competitor. She would make 25% more than you are paying her now. If you match this offer, what is the impact upon your breakeven point (what will the new breakeven point be)? (10 points)

Please show the calculations with your answers.

Data for analysis.

First step: Determine which expense items are fixed and which are variable. I do not expect you to make the same assumptions I would make. You are graded on the application of the breakeven formula to the data as you have analyzed it.

Second Step: Calculate the breakeven sales using the formula in the online lecture.

Third Step: Do a “what if” analysis by changing the inputs (in problem 2 and 3) and recalculating the breakeven sales.

Revenues                                         $650,000

Expenses:                           

Managers’s Salaries & Benefits          25,000

Asst. Managers’ Wages                       35,000

Clerical Wages                                     29,000

Owner’s Salary                                    55,000

Rent (4% of gross revenue)                 13,000

Mall Common Area Payment             25,000

Phone Bill                                              5,000

Property Insurance                                7,000

Property Taxes                                      5,000

Office Supplies                                     2,000

Postage                                                     500

Equipment Expenses                             5,000

Shrinkage                                               4,000

Bad Debt Expense                               15,000

Credit Card Expense                              6.500

Wholesale Clothing Purchases           380,000

                                                         ---------------

Total Expenses                                    612,000

Net Operating Income                           38,000               

Answer 1

1. Analysis of Costs:

Simply defined variable costs are the costs that vary with number of units and fixed costs are constant, do not vary with number of units.

Fixed costs	Variable costs
Manager's salary	Clerks' wages
Asst manager' wages	Rent expense
Owner's salary	Bad debt expense
Mall common area payment	Shrinkage expense
Phone bill	Credit card expense
Property insurance and taxes	Wholesale clothing purchase
Equipment expenses
Postage and office supplies

Therefore, from the above table, total variable costs = $ 29,000 + 13,000 + 15,000 + 6,500 + 4,000 + 380,000 = $ 447,500

and, total fixed costs = $ 25,000 + 35,000 + 55,000 + 25,000 + 5,000 + 7,000 + 5,000 + 2,000 + 500 + 5,000 = $ 167,500

Contribution margin = (Sales - Variable costs)/sales x 100 = $ (650,000 - 447,500)/650,000 x 100 = 31%

Break even sales in dollars = Fixed costs / contribution margin = $ 167,500 / 31% = $540,323 (rounded off)

2. Revised break even point: (supplier of 45% of clothing has increased price by 5%)

45% of the existing clothing purchase expense = $380,000 x 45% = $171,000

5% increase in the 45% expense = $171,000 x 1.05 = $179,550

Remaining 55% expense shall remain same i.e. $380,000 - $171,000 = $209,000

Revised clothing purchase expense = $179,550 + $209,000 = $ 388,550

Revised variable costs = $447,500 - $380,000 + $388,550 = $456,050

Revised contribution margin = $ (650,000 - 456,050) / 650,000 x 100 = 29.84%

Revised break even sales in dollars = Fixed costs / contribution margin = $ 167,500 / 29.84% = $ 561,327 (rounded off)

As a result of 5% increase in supplier clothing cost of 45% , the break even point has increased by approximately 3.8%.

3. Revised break even point: (Increase in manager's salary by 25%)

existing salary of the manager = $25,000

Increase in the manager's salary = $ 25,000 x 25% = $6,250

Therefore, fixed costs will increase by $6,250

Revised fixed costs = $167,500 + $6,250 = $173,750

Revised break even sales in dollars = Fixed costs / contribution margin = $ 173,750 / 31% = $ 560,484 (rounded off)

As a result of 5% increase in the manager's salary, the break even point has increased by approximately 3.7%