Construct a new one year, month-by-month, interactive master

Question Description:


Construct a new one year, month-by-month, interactive master budget in Excel® for the company, including all operating budgets, a capital budget, and pro-forma financial statements for the end of the year, reflecting your Team’s consensus strategic recommendations for Guillermo in Week One and Week Two as well as the information provided.

Setup Information Brian Lindquist Peso? (1=Yes) 0.08 1.00 10.814 Mexican Pesos = 1.000 US Dollars 0 Income Information Current Production (Units) Mid-Grade High-End Hi-Tech Broker 2,585 517 3,878 776 3,878 517 Production can be increased by 50% and the broker also anticipates that same level Production can be increased by 50% with Hi-Tech, but no reason for an increase with the Broker option Direct Materials ($)/Unit Mid-Grade High-End 140.00 250.00 140.00 250.00 Direct Labor ($/HR)/Unit 15.00 40.00 20 4 30 4 Direct Cost/Unit Mid-Grade High-End 440.00 700.00 300.00 410.00 360.00 The Broker cost for Mid-Grade is based on net FOB destination including shipping/tariffs 700.00 The Direct Cost for High-End units is reduced by the High Tech option, but not for the Broker Option Price/Unit Mid-Grade High-End 509.00 879.00 459.00 789.00 459.00 Prices are reduced by 10% because supply is increased with high tech & broker 879.00 Prices are reduced by 10% because supply is increased with high tech, but not with the broker option Wages 50,000 95,000 Utilities 9,000 27,000 105,815 83,964 3,000 15,000 975 3,900 50,000 6,000 466,667 6,000 19,370 89,534 270,908 224,790 46,118 910,706 697,531 213,175 476,465 Gross Margins 141,239 Overhead 335,226 Net Income before taxes 26,748 123,642 194,431 Net After Tax Income 76,748 590,308 244,431 513,560 Increase in Cash Flow compared to current operations. This is the relevant information for your capital budgeting 167,682 calculations. Labor Time (Hrs)/Unit Mid-Grade High-End There are no material costs for brokered units; instead the cost of the units shown below must be paid. 250.00 The labor rate is increased for Hi-Tech due to the technical skill level of operators, but since no additional skill is 15.00 needed for the Broker option, labor costs per hour remain unchanged from Current. There are no labor times for brokered units and production times are 20% of original times for High Tech Production times are now equal to the mid-grade level with the Hi-Tech option, but no change from Current with the 30 Broker alternative Plant Overhead/Yr Benefits Insurance Property Taxes Depreciation Supplies Income Tax Expense Cash Flow Incremental Cash Flow Need to add a $45,000 a year maintenance position for the Hi-Tech equipment under that option, but not necessary 50,000 with the Broker option Utilities are expected to be 3 x’s current at full production (150% above current levels) based on units produced for the high tech option. Since there is much less manufacturing activity for the Broker option, utilities are lower than the 2,999 Current situaton. Benefits are 10% of all wages (including direct labor– don’t forget direct labor to manufacture high end units under the 28,265 Broker option) Insurance will increase by $12,000 with the addition of the equipment and building expansion under the High Tech 3,000 option. No change from Current with Broker. Property taxes are 6.5%. The assessment is 1% of original value, and that is on all plant/equipment–no new 975 equipment with the Broker alternative. Buildings are at 30 years and Equipment is at 10 years, straight line. (Note the additional equipment acquired under 50,000 the High Tech option. This is the out-of-pocket investment required with that strategy. 6,000 Supply expense is miscellaneous and does not vary 140,795 Taxes are 42% of Net Income calculated below for each option. Assets, Liabilities & Equity Information 12/31/2012 Cash Accounts Receivable Inventory Pre-paid Insurance TOTAL CURRENT ASSETS Buildings Less: