Guillermo’s Furniture Store Scenario

profileneel
fin571_guillermo_furniture_financial_3.xls
Home>Business & Finance homework help>Guillermo’s Furniture Store Scenario

Income Information

Setup Information
<Insert Facilitator's Name> 0.06
Peso? (1=Yes) 0 1.00 10.814 Mexican Pesos = 1.000 US Dollars
Income Information-Current Standards
Current Hi-Tech Broker
Production Current Production = Sales Forecast
Mid-Grade 2,532.00 3,798.00 3,798.00 Production can be increased by 50% and the broker also anticipates that same level
High-End 506.00 759.00 759.00 Production can be increased by 50%
Direct Materials ($)/Unit
Mid-Grade 140.00 140.00 There are no material costs for brokered units
High-End 250.00 250.00 250.00
Direct Labor ($/HR)/Unit 15.00 40.00 40.00 The labor rate is increased due to the technical skill level of operators
Labor Time (Hrs)/Unit
Mid-Grade 20.00 4.00 There are no labor times for brokered units and production times are 20% of original times
High-End 30.00 4.00 4.00 Production times are now equal to the mid-grade level
Direct Cost/Unit
Mid-Grade 440.00 300.00 360.00 The Broker cost for Mid-Grade is based on net FOB destination including shipping/tariffs
High-End 700.00 410.00 410.00
Price/Unit
Mid-Grade 509.00 459.00 459.00 Prices are reduced by 10% because supply is increased
High-End 879.00 789.00 789.00 Prices are reduced by 10% because supply is increased
Plant Overhead/Yr
Salaries 50,000 95,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 15,000 Insurance will increase by 12,000 with the addition of the equipment and building expansion
Property Taxes 975 3,900 3,900 Property taxes are 6.5%, assessment is 1% of original value, and that is on all plant/equipment
Depreciation 50,000 466,667 466,667 Buildings are at 30 years and Equipment is at 10 years, straight line
Supplies 6,000 6,000 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 612,708 Overhead
42,577 195,564 50,955 Net Income before taxes

Assets, Liabilities & Equity In

Assets, Liabilities & Equity Information
12/31/2011 12/31/2012
Cash $ 120,872 USD $ 165,933 USD
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,074 USD $ 495,163 USD
Buildings 1,500,000 1,500,000
Less: Accumulated Depreciation (600,000) (650,000) Current Building has been in use for 13 years
Equipment 50,000 50,000
Less: Accumulated Depreciation (50,000) (50,000) Equipment fully depreciated several years ago
TOTAL ASSETS $ 1,342,074 USD $ 1,345,163 USD
Accounts Payable $ 79,917 USD $ 82,388 USD A/P represents 2 months of purchases & 1 month of bills & Prop Tax
Income Taxes Payable 16,988 17,882 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 USD $ 172,730 USD
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 USD $ 1,109,358 USD
Common Stock $ 10,000 USD $ 10,000 USD No Par Value 10,000 shares
Retained Earnings 201,111 225,805
TOTAL EQUITY $ 211,111 USD $ 235,805 USD
TOTAL LIABILITIES & EQUITY $ 1,342,074 USD $ 1,345,163 USD

Budget

Flex Budget
Budget Data
Units Budgeted $ Budgeted Units Actual
Production
Mid-Grade 2,532 2,800
High-End 506 400
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 21.50
High-End 30.00 28.00
Direct Cost/Unit
Mid-Grade 440.00
High-End 700.00
Price/Unit
Mid-Grade 509.00
High-End 879.00
Plant Overhead/Yr
Salaries 50,000
Utilities 9,000
Benefits 10%
Insurance 3,000
Property Taxes 975
Depreciation 50,000
Supplies 6,000
Income Tax Expense 42.00% - 0
Variance Analysis - June
Units Budgeted $ Budgeted Units Actual $ Budget-Flex $ Actual Var-Flex Var-Gross
Revenue
High-End 506 444,774 421 370,059 351,556 (18,503) (93,218)
Mid-Grade 2,532 1,288,788 2,787 1,418,583 1,418,583 - 0 129,795
Total Revenue 1,733,562 1,788,642 1,770,139 (18,503) 36,577
Cost of Goods
High-End 126,500 225.00 105,250 94,725 10,525 31,775
Mid-Grade 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 50,000 50,000 52,500 (2,500) (2,500)
Benefits 103,730 107,555 112,972 (5,417) (9,242)
Supplies 6,000 6,000 5975 25 25
Utilities 9,000 9,000 9100 (100) (100)
Insurance 3,000 3,000 3000 - 0 - 0
Property Taxes 975 975 975 - 0 - 0
Total Operating Expense 1,160,005 1,202,080 1,261,745 (59,665) (101,740)
Earnings before Taxes & Depr 92,577 91,132 17,219 (73,913) (75,358)
Deprecition 50,000 50,000 50,000 - 0 - 0
Earnings before Taxes 42,577 41,132 (32,781) (73,913) (75,358)
Income Taxes 17,882 17,275 (13,768) 31,044 31,650
Net Earnings 24,695 23,857 (19,013) (42,870) (43,708)
Sales Forecast
Budget=> January February March April May June
High-End 467 477 487 497 507 506
Mid-Grade 2458 2483 2508 2533 2559 2,532
Actual=>
High-End 470 456 442 429 416 421
Mid-Grade 2460 2522 2585 2650 2716 2787

Production for March

Production Data
Direct Cost Total Wood Materials Foam Chem A Chem B Chem C
Flame Retardent (per liter) 10.00 1.50 0.50
Coating (per liter) 25.00 7.50 2.50 15.00
Mid-Grade (per unit) 140.00 80.00 40.00 20.00
High-End (per unit) 250.00 160.00 60.00 30.00
Alternative Coating (per liter) 27.50
Market Price of Flame Retardent (per liter) 10.00
Liters of Flame retardent per year 61
Liters of Coating per year 304
Plant Capacity
Flame Retardent 182
Coating 456
Mid-Grade 5,064
High-End 1,012