Guillermo’s Furniture Store Scenario

fin571_-_wk4_furniture_solution_template_3.xlsfin571_guillermo_furniture_financial_3.xls

While many people know that Sonora, Mexico is a beautiful vacation spot, it is also a large furniture manufacturing location in North America. Guillermo Navallez made furniture for years near his Sonoran home. The area had a good supply of timber for the variety of tables and chairs produced by his company. Labor was also relatively inexpensive. In addition, he priced his handcrafted products at a slight premium for the quality they represented. Overall, life was good for Guillermo.

 

All of that was true until late in the 1990s when two forces combined to cause a large dent in his business. First, a new competitor from overseas entered the furniture market. Using a high-tech approach, this foreign competition provided furniture to exact specifications and did so with rock-bottom prices. Second, the sleepy communities in Sonora woke up. One of the largest retailers in the nation’s headquarters was just a few miles down the road, and its influence had expanded considerably. With inexpensive housing, mild weather, beautiful scenery, un-congested roads, a new International Airport, and plenty of development, an influx of people and jobs raised the cost of labor substantially. Guillermo watched his profit margins shrink as prices fell and costs rose.

 

After doing some research on his competition to see how they are handling these changes, it is clear that many of them are consolidating into larger organizations by merger or acquisition. Being independent, Guillermo does not relish the idea of being acquired by a larger competitor and then retired as the new company squeezes every peso it could out of the overhead costs. Guillermo also is not looking to expand his management responsibilities by acquiring another organization either; that could affect his time with his family in ways that he will not enjoy.

 

Guillermo then spent some time looking at the foreign competition and their high-tech solution. Essentially, their production utilizes a computer controlled laser lathe to produce exact cuts in the wood. Highly automated, the plant in Norway uses very little labor as robots even perform the precise movement and assembly functions. The cost of the technology is immense, as is the reduction in the labor needed for production. In addition, the production can move between products quickly, and it runs on a 24-hour basis, as the shift-differentials are more than offset by the reduction in labor. Converting his production to this model would be expensive, but he saw how he could also decrease dramatically his production costs.

 

When talking to some of his distributors about their wants, he had another idea that appealed to him. A second competitor, currently operating only in Norway, has been looking for channels to distribute in North America. This second potential rival, however, did not operate furniture outlets favoring instead to rely on chain distributors. Perhaps Guillermo could coordinate his existing distributor network and essentially become a representative for this other manufacturer.  While he may retain some of the high end custom work, he could move his company from primarily manufacturing to primarily distribution.

 

Guillermo also has a patented process for creating a coating for his furniture. In producing this product, the process first creates a common flame-retardant, and upon further processing, the coating is complete and stain resistant. There is market for the flame retardant, but not as much of a market for the finished coating. There is another product that Guillermo could buy to apply to his furniture as well that would add the same amount of value for the furniture.

Scenarios

is when the initial investment is made, there are no sales or costs in that period … it is the “present” of present value. There may be follow-on investments in subsequent years.

and beyond —

s projected at 10% increase per year.

increases at 20% per year.

Year 0

Year 2

Revenue

High-End
Mid-Grade

& Depr

Depreciation
Earnings before Taxes

1) Year one projections are the same as the June Budget x 12

3) Year 2 and beyond — Varriable costs* — increase at the same rate as sales.
4) Year two and beyond — Fixed costs increase at a standard inflation rate of 3% per year.

Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Revenue
High-End
Mid-Grade
Total Revenue
Cost of Goods *
High-End
Mid-Grade
Total Cost of Goods
Net Revenue
Labor Wages
Office Salaries
Benefits
Supplies
Utilities
Insurance
Property Taxes
Total Operating Expense

Depreciation
Earnings before Taxes
Income Taxes (42%)
Net Earnings
Add back Depreciation
Subtract Out Capital Expenditures
TOTAL CASH FLOW FOR ANALYSIS
DISCOUNT RATE to use for calculating the NPV:
Add the calculated WACC to a risk rate your analysts have assigned to this scenario

WACC data needed for calculation:
Cost of Debt = 8%
Desired Mix for weighting (Debt = 40%; Equity = 60%)
Cost of Equity data needed:
Next Annual Dividend = $2
Current Stock Market Value = $40
Dividend Growth Factor = 7%
NPV =
IRR =

3) Year 2 and beyond — Varriable costs* — increase at the same rate as sales.
4) Year two and beyond — Fixed costs increase at a standard inflation rate of 3% per year.

Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Revenue
High-End
Mid-Grade
Total Revenue
Cost of Goods *
High-End
Mid-Grade
Total Cost of Goods
Net Revenue

Office Salaries

Supplies
Utilities
Insurance
Property Taxes
Total Operating Expense
Earnings before Taxes & Depr
Depreciation
Earnings before Taxes
Income Taxes (42%)
Net Earnings
Add back Depreciation
Subtract Out Capital Expenditures
TOTAL CASH FLOW FOR ANALYSIS
DISCOUNT RATE to use for calculating the NPV:
Add the calculated WACC to a risk rate your analysts have assigned to this scenario

WACC data needed for calculation:
Cost of Debt = 8%
Desired Mix for weighting (Debt = 40%; Equity = 60%)
Cost of Equity data needed:

Current Stock Market Value = $40
Dividend Growth Factor = 7%
NPV =
IRR =

INSTRUCTIONS

The major point of this assignment is for you to realize there are three separate alternatives presented in the case verbiage.

Using the budget figures and projection assumptions given, you are to calculate a 5 year projected income statement and cash flow as well as the WACC for each alternative.

As in a real corporation, you can take each alternative, calculate the decision-making capital budgeting analyses

(NPV and IRR) using a discount rate and risk factors (sometimes referred to as the hurdle rate and/or required rate of return).

Then, a decision can be made as to which alternative would be best for the organization. As you will see, each alternative not only has a different projection,

but a different level of risk that is built into the NPV calculations. Of course, all possible variables are not accounted – but this exercise will give you a

flavor of what is used in the real-world in regards to capital budgeting.

Below are the three scenarios from the case along with additional assumptions in order for you to fulfill the requirements of this assignment.

You can use the formats provided below as a way to organize the analysis. You will need to do the calculations for the various elements and factors as part of your analysis.

Remember …

Year 0

Note — please realize that the assumptions in the three below scenarios are just that — “assumptions”. They are given to create an example of the decision making process used

Please show your calculations. If necessary, set up additional spreadsheets using the other “sheets”, linking from the Scenarios sheet to the appropriate information on the back sheets. That will help you organize your thoughts and help in tracing data to your conclusions. Please submit your completed spreadsheet with your written analysis.

SCENARIO ONE – Paragraph 4 of Furniture Scenario – increase in technology.

1) Year one projections are the same as the June Budget x 12

2)

Year 2

Revenue

3) Year 2 and beyond — Varriable costs* — increase at the same rate as sales.

4) Year two and beyond — Fixed costs increase at a standard inflation rate of 3% per year.

5) Year two and beyond —

Depreciation

6) Capital Expenditures — Initially $7,000,000; and an additional $700,000 is years two, four, and in year five.

7) Labor wages & benefits decrease by 20% for year two due to technology savings — then a 10% per year increase in year three and beyond.

Year 1

Year 3

Year 4

Year 5

High-End

Mid-Grade

Total Revenue

Cost of Goods *

Total Cost of Goods

Net Revenue

Labor Wages

Office Salaries

Benefits

Supplies

Utilities

Insurance

Property Taxes

Total Operating Expense

Earnings before Taxes

Income Taxes (42%)

Net Earnings

Add back Depreciation

Subtract Out Capital Expenditures

TOTAL CASH FLOW FOR ANALYSIS

DISCOUNT RATE to use for calculating the NPV:

Add the calculated WACC to a risk rate your analysts have assigned to this scenario

Risk Rate Assigned = 1.6%

WACC data needed for calculation:

Cost of Debt = 8%

Desired Mix for weighting (Debt = 40%; Equity = 60%)

Cost of Equity data needed:

Next Annual Dividend = $2

Current Stock Market Value = $40

Dividend Growth Factor = 7%

NPV =

IRR =

SCENARIO TWO – Paragraph 5 of Furniture Scenario – converting factory from primary manufacturing to primary distribution

2) Year 2 — Revenues decrease 10% and then increase at 5% .

5) Year two and beyond — Depreciation increases at 10% per year.

6) Capital Expenditures — Initially $5,000,000; and an additional $500,000 is years two, four, and in year five.

7) Labor wages & benefits decrease by 40% for year two due to converting to distribution — then a 5% per year increase in year three and beyond.

8) Office Salaries increase by 20% for year two due to converting to distribution — then a 5% per year increase in year three and beyond.

Earnings before Taxes & Depr

Risk Rate Assigned = 5.1%

SCENARIO THREE – Paragraph 6 of Furniture Scenario – adding another product to the finish of the furniture – cost is minimal.

1) Year one projections are the same as the June Actual x 12

2) Year 2 and beyond — Revenues projected at 10% increase per year.

5) Year two and beyond — Depreciation increases at 5% per year.

6) Capital Expenditures — Initially 500,000; and an additional 50,000 is years two, four, and in year five.

Labor Wages*

Benefits *

Risk Rate Assigned = 0%

Next Annual Dividend = $2

Sheet2

Sheet3

Income Information

.06

0

Standards

Current

.00

3,798.00

and the broker also anticipates that same level

.00

759.00 Production can be increased by 50%

($)/Unit

Mid-Grade

High-End

0.00

250.00

40.00 40.00

Mid-Grade

High-End

4.00 4.00

/Unit

Mid-Grade

High-End

410.00

Mid-Grade

459.00

because supply is increased

High-End

789.00 Prices are reduced by 10% because supply is increased

/Yr

95,000

15,000

3,900

50,000

466,667

are at 30 years and

is at 10 years, straight line

6,000 6,000

2,708

Overhead

																																																																																										Setup Information
		0
Peso? (1=Yes)	1.00	10.814 Mexican Pesos = 1.000 US Dollars
Income Information-	Current
Hi-Tech	Broker
	Production	Current Production =	Sales Forecast
														Mid-Grade				2,532		3,798.00		Production can be increased by 50%
														High-End				506		759.00
	Direct	Materials
	1			40.00		140.00	There are no material costs for brokered units
					25	250.00
	Direct Labor ($/HR)/Unit			15.00	The labor rate is increased due to the technical skill level of operators
	Labor Time (Hrs)/Unit
		20.00			4.00	There are no labor times for brokered units and production times are 20% of original times
		30.00	Production times are now equal to the mid-grade level
		Direct Cost
	440.00	300.00	3	60.00	The Broker cost for Mid-Grade is based on net FOB destination including shipping/tariffs
	700.00		4		10.00
	Price/Unit
	509.00		459.00		Prices are reduced by	10%
	879.00		789.00
Plant	Overhead
	Salaries											50,000		95,000	Need to add a 45,000 a year maintenance position for the equipment
		Utilities				9,000	27,000	4,	497	Utilities are expected to be 3 x’s current at full production (150% above current levels) based on units produced
		Benefits		103,730	82,412	21,644	Benefits are 10% of all wages (including direct labor)
		Insurance				3,000		15,000	Insurance will increase by 12,000 with the addition of the equipment and building expansion
		Property Taxes					975		3,900	Property taxes are 6.5%, assessment is 1% of original value, and that is on all plant/equipment
	Depreciation		466,667		Buildings	Equipment
		Supplies						6,000	Supply expense is miscellaneous and does not vary
	Income Tax Expense			17,882	82,137	21,401	Taxes are 42% of Net Income
265,282	891,543	663,663	Net Margins
222,705	695,979		61
	42,577	195,564	50,955	Net Income before taxes

Assets, Liabilities & Equity In

USD

,074

USD

USD

Buildings

1,500,000

Equipment 50,000 50,000
Less: Accumulated Depreciation

(50,000)

USD

USD

USD

USD

Payable

17,882

USD

USD

USD

USD

USD $ 10,000 USD

USD

USD

$ 1,342,074 USD $ 1,345,163 USD

Assets, Liabilities & Equity Information
12/31/2011	12/31/2012
Cash	$ 120,872																		USD	$ 165,933
Accounts Receivable	201,266	205,374	DSO = 45 days	Sales growth has slowed to 1%
Inventory	118,686	122,357	The plant completes all work-in-process before year end inventory	Inflation is running at 3%
Pre-paid Insurance	1,250	1,500	1/2 a year pre-paid
TOTAL CURRENT ASSETS	$	442	$ 495,163
	1,500,000
	Less: Accumulated Depreciation	(600,000)	(650,000)	Current Building has been in use for 13 years
	(50,000)	Equipment fully depreciated several years ago
TOTAL ASSETS		$ 1,342,074		$ 1,345,163
Accounts Payable	$ 79,917	$ 82,388	A/P represents 2 months of purchases & 1 month of bills & Prop Tax
	Income Taxes	16,988	All timing issues wash out (for simplicity)
Wages Payable	41,060	43,221	Wages are two weeks
Current Portion of Notes Payable	27,132	29,238
TOTAL CURRENT LIABILITIES	$ 165,097	$ 172,730
Mortgage Note Payable	965,867	936,628	Building was financed Jan 1, 12 years ago at 7.5% and 80% LTV
TOTAL LIABILITIES	$ 1,130,963	$ 1,109,358
Common Stock		$ 10,000	No Par Value 10,000 shares
Retained Earnings	201,111	225,805
TOTAL EQUITY	$ 211,111	$ 235,805
TOTAL LIABILITIES & EQUITY

Budget

Production
Mid-Grade 2,532

High-End 506

Direct Materials ($)/Unit

Mid-Grade 140.00
High-End 250.00
Direct Labor ($/HR)/Unit 15.00

Labor Time (Hrs)/Unit

Mid-Grade 20.00

High-End 30.00

Direct Cost/Unit

Mid-Grade 440.00
High-End 700.00

Price/Unit

Mid-Grade 509.00
High-End 879.00
Salaries 50,000
Utilities 9,000
Benefits 10%
Insurance 3,000
Property Taxes 975
Depreciation 50,000
Supplies 6,000
Income Tax Expense

Units Budgeted $ Budgeted Units Actual

High-End 506

Mid-Grade 2,532

1,418,583 – 0

Revenue

(18,503)

High-End

Mid-Grade

50,000 50,000

(2,500)

Benefits 103,730

Supplies 6,000 6,000

25 25

Utilities 9,000 9,000

(100)

Insurance 3,000 3,000

– 0 – 0

Property Taxes 975 975 975 – 0 – 0

& Depr

50,000 50,000 50,000 – 0 – 0

Earnings before Taxes 42,577

(73,913) (75,358)

Income Taxes 17,882

Sales Forecast

June

High-End

497

506

Mid-Grade

2,532

High-End

442

421

Mid-Grade

Flex Budget
Budget Data
	Units Budgeted		$ Budgeted		Units Actual
2,800
400
2	1.50
28.00
Plant Overhead/Yr
42.00%								– 0
Variance Analysis –	June
$ Budget-Flex	$ Actual	Var-Flex	Var-Gross
Revenue
444,774		421	370,059	351,556		(18,503)	(93,218)
1,288,788	2,787		1,418,583	129,795
	Total	1,733,562	1,788,642	1,770,139	36,577
Cost of Goods
126,500	2	25.00	105,250	94,725	10,525	31,775
354,480	142.25	390,180	396,451	(6,271)	(41,971)
Total Cost of Goods	480,980	495,430	491,176	4,254	(10,196)
Net Revenue	1,252,582	1,293,212	1,278,963	(14,249)	26,381
Labor Wages	987,300	15.02	1,025,550	1,077,222	(51,672)	(89,922)
Office Salaries	52,500		(2,500)
107,555	112,972	(5,417)	(9,242)
5975
9100		(100)
3000
Total Operating Expense	1,160,005	1,202,080	1,261,745	(59,665)	(101,740)
	Earnings before Taxes	92,577	91,132	17,219		(73,913)		(75,358)
Deprecition
41,132	(32,781)
17,275	(13,768)	31,044	31,650
Net Earnings	24,695	23,857	(19,013)	(42,870)	(43,708)
Budget=>	January	February	March	April	May
467	477	487	507
2458	2483	2508	2533	2559
Actual=>
470		456	429	416
2460	2522	2585	2650	2716	2787

Production for March

Direct Cost Total

Materials

(per liter)

10.00 1.50

(per liter)

25.00

15.00

140.00

40.00 20.00

250.00

60.00 30.00

10.00

61

Flame Retardent

Coating 456
Mid-Grade

High-End

Production Data
Wood	Foam	Chem A	Chem B	Chem C
	Flame Retardent	0.50
	Coating	7.50	2.50
Mid-Grade (per unit)	80.00
High-End (per unit)	160.00
Alternative Coating (per liter)	27.50
Market Price of Flame Retardent (per liter)
Liters of Flame retardent per year
Liters of Coating per year	304
Plant Capacity
182
5,064
1,012