Please see attachment. Please use excel. Also, under the "Requirements"seperate part A and part B on excel. Thank you.

Budgeting Project TO THE STUDENT Fantastic, Inc. is a case study which allows you to incorporate numerous financial and managerial accounting concepts into a single business setting. You will take the position of the company controller who will prepare the budget for the year ended December 31, 2006, using the actual data from 2001 through 2005 and information given to you by various departments. You will prepare a report for the president of the company describing the strengths and weakness of the corporation as well as to provide suggestions for the future. In short, you will be responsible for the planning and control procedures for the company from an accounting standpoint. In order to focus on important accounting concepts, certain simplifications are necessary to make this case manageable. The student should keep the following simplifications in mind while working on this case: Work in process inventories will be ignored. Financial and IRS tax will be the same. Some projections for 2006 will be given. Standards used for the 2006 budget will be the reasonably obtainable standards. No hourly worker will work overtime. All price changes will occur on January 1st and will remain in effect for the entire year. The actual 2005 information is available while preparing the 2006 budget. All debt transactions will occur either on January 1st or December 31st. There are no bad debts. The student should also keep in mind that the budgeting process is not an exact science; therefore, approximate figures provide adequate information for the decision maker. Figures should be rounded to the whole dollar throughout the budgeting process and the control applications. Since it is not possible to have a partial machine or person, certain figures will always have to be rounded up. GENERAL COMPANY INFORMATION Fantastic, Inc. is a paint manufacturing company that produces two qualities of paint, Super and Stupendous. The company was established eight years ago and began with only one type of paint. Sales of the original product have been rather stable in the past 5 years. In 2003, a second, higher quality paint was introduced, and sales of this product have increased each year due to reasonably effective sales efforts. The president is currently concerned about the potential inefficient use of capacity and the effect that this has on profits. All raw materials are currently purchased from outside suppliers and no difficulty is foreseen in obtaining the necessary inventories for production in the future. All inventories are currently considered to be at the lowest safe levels possible given the delivery, production, and sales cycles. Given the current production capacity, the company will have room for expansion for the next few years without building new facilities or expanding the current building. The company will also have the option of starting a second production shift to support future sales if necessary; therefore, increased production will be obtainable through purchasing additional equipment or increasing production hours. At this time, however, the president is not considering a second shift due the additional $1.00 per hour shift differential that would be necessary to pay the hourly second shift workers. The company had a cash flow problem in 2005 but has always managed to make all payments on a timely basis. The president wishes to increase the amount of cash on hand in the future so that the company will have a greater margin of safety. To date, the company has not had difficulty obtaining financing for expansion and does not foresee any future difficulties in obtaining necessary funding for legitimate purposes. 1 The company has paid a consistently good dividend to the stockholders. The president would like to continue this policy in the future. DEPARTMENT STRUCTURE Fantastic, Inc. has two separate production departments - one for each of the paint types. Since the end products are not distinguishable, the company uses process costing employing a FIFO inventory. Absorption costing is used for all outside reports. All non-direct fixed costs are allocated using various allocation bases as indicated throughout the project. The Company does not use a full ABC costing system; however, it does employee some of the ABC concepts in the budgeting process. The administrative department handles all of the purchasing, accounting, and secretarial duties in a highly efficient manner. The need for separate departments is not necessary at this time. 2005 INFORMATION SUPER DEPARTMENT Number of machines available Annual capacity per machine Annual production capacity Machine hours available per machine Standard machine hour per gallon Standard labor hour per machine hour Actual sales volume Number of hourly employees Supervisors (one per each eight hourly employees) Raw material prices Cans Pigment Raw material usage Cans Pigment Direct labor rate STUPENDOUS DEPARTMENT 26 15,000 gallons 390,000 gallons 1,800 hours .12 hour/gallon 1.25 labor hr/mach.hr 420,000 gallons 29 20 15,000 gallons 300,000 gallons 1,800 hours .12 hour/gallon 1.25 labor hr/mach.hr 268,200 gallons 21 4 3 40 each $2.75 per pound 40 each $3.75 per pound one per gallon two pounds per gallon $8.25 per hour one per gallon two pounds per gallon $8.25 per hour 2006 PROJECTED SALES INFORMATION The sales department feels very good about the prospects for 2006. The reputation of the new product, Stupendous Paint, has increased significantly due to effective adverting in the past few years. The sales department is ready to launch a major advertising campaign which is expected to result in the trend of the last three year to continue for the Stupendous brand even with a small price increase per gallon. Super Paint has been produced since the company began production. The sales level of this product has been rather stagnant for the last 5 years, and that trend is expected to continue in the future. The following information regarding the two products has been gathered: 2 Super Paint Anticipated 2006 Sales price Actual 2001 Sales Actual 2002 Sales Actual 2003 Sales Actual 2004 Sales Actual 2005 Sales Stupendous Paint $10.30 per gallon $15.45 per gallon 411,000 gallons 412,000 gallons 405,000 gallons 430,000 gallons 420,000 gallons None None 186,250 gallons 223,500 gallons 268,200 gallons You have made the following conclusions about the two different products: Super Paint Sales growth in units will continue to be stagnant. The more recent years are more representative of continuing unit sales than years in the further past. Exponential Smoothing will be an appropriate unit sales projection method to use. Stupendous Paint Unit sales growth will continue on its current increasing trend An exponential growth function with the year being the independent variable and the sales volume being the dependent variable will be an appropriate sales projection method. 2006 DIRECT MATERIAL AND INVENTORY BUDGET INFORMATION The production department worked in conjunction with both the sales and purchasing departments in developing desired projected inventory levels for December 31, 2006. Information regarding beginning and desired ending inventories for 2006 are as follows: Cans Pigment for Super Paint Pigment for Stupendous Paint Finished Goods Inventory Super Paint Stupendous Paint Beginning inventory 56,550 cans 63,300 pounds 49,700 pounds Ending inventory 61,700 cans 70,000 pounds 53,750 pounds 31,700 gallons 24,850 gallons 35,000 gallons 27,000 gallons The following direct material price increases are expected to occur on January 1, 2006, and remain effective for the entire year: Price increase expected Cans 2 each Pigments Super Paint $0.14 increase over 2005 prices Stupendous Paint $0.19 increase over 2005 prices Inventories are accounted for using the FIFO method. Hint: Remember that each gallon of paint requires two pounds of pigment. 2006 DIRECT LABOR BUDGET INFORMATION 3 During 2005, a new contract was signed with the union. As part of that agreement, no worker can work in more than one department, and no single worker can work over 2,000 hours during the year. This means that for every 2,000 hours of labor that is required for production in each department, one hourly employee is needed. For example, if a department needs 10,150 labor hours during the year, six employees will be needed (10,150 total hours /2,000 per employee = 5.075 which needs to be rounded to 6 employees). The standard direct labor rate will increase $0.25 per hour over the 2005 rate. Additional information regarding employee benefits is included in the section on manufacturing overhead. 2006 OVERHEAD BUDGET INFORMATION The following information is available regarding the actual overhead costs incurred for 2005: Indirect materials Indirect labor annual rate Hourly employee fringe benefits Hourly health benefits Supervisor fringe benefits Supervisor health benefits Utilities Maintenance Insurance Property taxes Supplies Depreciation (items held during 2005) Variable costs 20 per gallon Fixed costs $50,000 per supervisor 20% of wages $1,500 per employee 20% supervisor's salaries $1,500 per employee 40 per machine hour 20 per machine hour *$10,000 annually *$50,000 annually *$10,000 annually *$5,000 annually **$250,000 annually *These items are allocated to departments based upon production levels in gallons. **The 2005 allocation was $141,300 to the Super Department and $108,700 to the Stupendous Department It is expected that the following changes will occur during 2006: Variable costs Indirect materials No change Indirect labor annual rate Hourly fringe benefits No change Hourly health benefits Supervisor fringe benefits Supervisor fringe benefits Utilities No change Maintenance Increase 5/MHr* Insurance Property taxes Supplies Depreciation (items held during 2005) Depreciation (machinery purchased in 2006) *Mhr stands for Machine hours. ** There will be no salvage value for the equipment purchased in 2006 4 Fixed costs 2.5% increase Increase $200/employee No change Increase$200/supervisor Increase $500 annually Increase $500 annually Increase 8.0% Increase $200 annually No change Five year life** 2006 SALES DEPARTMENT INFORMATION The sales department consists of 10 representatives who report directly to the president. Each individual is on a base salary plus a commission, and the sales reps also submit meal and entertainment expenses for reimbursement. (In 2005, the meal and entertainment expenses were limited to $50 per week per sales representative based upon a 52 week year.) The following information is available regarding the actual selling costs incurred for 2005: Commissions Salaries Fringe benefits Health benefits Advertising Meals & entertainment Depreciation Variable 35 per gallon sold 20% of commissions Fixed $15,000 per sales representative 20% of salaries $1,500 per sales representative $10 per 100 gallons sold $50 per week per representative $7,500 It is felt that the company will not need any additional sales representatives for 2006. It is expected that the following changes will occur in 2006: Variable Fixed Commissions No change Salaries No change Fringe benefits No change No change Health benefits Increase $200 per representative Advertising $12 per 100 gallons sold Meals & entertainment $60 per week per representative Depreciation No change 2006 ADMINISTRATIVE BUDGET INFORMATION The administrative department consists of the president and an office staff of eight who handle the secretarial, purchasing, and accounting duties. (Total of nine people.) The following information is available regarding the 2005 actual costs: Variable Salaries Fringe benefits Health benefits Professional fees Office supplies Telephone Depreciation Fixed $250,000 annual 20% of salaries $1,500 per employee $20,000 annually 3 per gallon sold 1.5 per gallon sold $6,000 annually It is felt that the level of the office and administrative staff will be adequate for the coming year. It is expected that the following changes will occur in 2006: Variable Fixed Increase 3% annually No change Salaries Fringe benefits 5 Health benefits Professional fees Office supplies Telephone Depreciation Increase $200 per employee Increase of $1,500 Increase 1 per gallon sold Increase per gallon sold No change 2006 INCOME TAX INFORMATION The current federal and state income tax rates total 40%. All taxes for the year will be paid by the end of the year which means that there will be no accrued taxes on December 31, 2006. 2006 ACCOUNTS RECEIVABLE INFORMATION It is expected sales will occur evenly throughout the year and that there will be no cash sales. 75% of monthly sales will be collected in the month following the sale, and the remaining 25% will be collected by the end of the second month. No bad debts are anticipated. 2006 ACCOUNTS PAYABLE INFORMATION This account represents the purchases for raw materials only. It is expected that purchases will be made evenly throughout the year, and that all purchases will be paid for during the month following the purchase. 2006 WAGE AND SALARY INFORMATION The accrued wage account will include all direct labor, indirect labor, sales commissions and salaries, and administrative salaries. All wages are earned evenly throughout the year, and employees are paid twice each month. On December 31, 2006, the accrued wages will include wages representing one full payroll period. 2006 ACCRUED OTHER INFORMATION This account includes all other cash expenses not included in Account Payable and Accrued Wages. It is the company's policy to pay all other expenses during the month following the purchase. These expenses will be incurred evenly throughout the year. The 20% fringe benefits will be paid on all wages, salaries, and commissions. 2006 PROPERTY, PLANT AND EQUIPMENT INFORMATION Property, plant and equipment consist of the following on December 31, 2005: Property and plant Equipment Total Property, Plant and Equipment $750,000 1,025,000 $1,775,000 Each additional machine that is needed to support the production level expected during 2006 will cost $30,000 and be depreciated over five years using the straight-line method. Assume that any equipment purchases are made on the first day of the January and are operational throughout the entire year. 6 No new equipment will be needed for the sales and administrative departments. 2006 LONG-TERM DEBT INFORMATION A long-term debt repayment (principle only) will be made on December 31, 2006 for $255,000. The interest rate charged on the debt balance throughout 2006 will be 7.5% and will be paid on December 31, 2006. If an additional machine is purchased, $5,000 will be paid in cash and the remaining $25,000 will be financed at the 7.5% rate. This same proportion of cash/additional debt will be applied to all additional equipment purchased during 2006. Again, any purchases will be made on January 1, 2006. DIVIDEND POLICY Fantastic, Inc.'s policy is to pay dividends on the last day of each quarter. The total anticipated dividends for 2006 are $2.50 per share. OTHER INFORMATION Unless otherwise noted, information will remain unchanged from 2005 through 2006. 2005 BALANCE SHEET AND INCOME STATEMENT The Income Statement and Balance Sheet for Fantastic, Inc. for 2005 are as follows: FANTASTIC, INC STATEMENT OF INCOME FOR THE YEAR ENDED DECEMBER 31, 2005 Sales Cost of goods sold Gross Margin $8,270,000 6,620,946 1,649,054 Selling expenses Office& administrative expenses Operating income Interest expense Income before tax Income tax Net income $581,060 373,010 954,070 694,984 85,950 609,034 243,614 $365,420 FANTASTIC, INC. BALANCE SHEET DECEMBER 31, 2005 ASSETS Cash $150,000 7 Accounts receivable Inventory-Raw materials Inventory-Finished goods Plant and equipment Less Accumulated Depreciation Total Assets 827,000 383,037 551,835 1,775,000 (615,000) $3,071,872 LIABILITIES Accounts payable Accrued wages Accrued other Long-term debt Total liabilities $383,016 76,097 74,083 1,125,000 1,658,196 STOCKHOLDERS' EQUITY Common stock, $5 par value $400,000 Additional paid-in 495,000 Retained earnings 518,676 Total Stockholders' equity 1,413,676 Total liabilities and stockholders' equity $3,071,872 REQUIREMENTS: Part A 1. Prepare the following budgets for the fiscal year ending December 31, 2006 using the provided spreadsheets. Note: it is required to use the provided worksheets using Excel formulas. a. A sales projection in units using the methods suggested. (10 points) b. Sales budget (10 points) c. Production budget (10 points) d. Direct materials budget (10 points) e. Direct labor budget (10 points) f. Manufacturing overhead budget (10 points) g. Projected Cost of Goods Manufactured (absorption costing) (10 points) Loss of 20 points for not using the provided Excel worksheets Part B h. Capital expenditures budget (10 points) i. Selling expenses budget (10 points) j. Administrative expenses budget (10 points) k. Proforma income statement (absorption costing) (20 points) l. Cash budget (25 points) m. Proforma balance sheet (25 points) 2. Comment on the strengths and weaknesses of this company. What concerns do you have? What recommendations do you have for the future? (10 points) Loss of 25 points for not using provided Excel worksheets. 8 Fantastic, Inc Historical Information Sales in Gallons Year 2001 2002 2003 2004 2005 Super 411,000 412,000 405,000 430,000 420,000 Stupendous none none 186,250 223,500 268,200 Projected Selling Price for 2006 per gallon Super Stupendous $10.30 $15.45 Inventory and material information Desired Ending Beginning Inventory Inventory 56,550 cans 61,700 cans Cans Pigment Super Stp'dous Finished Good Inv Super Stp'dous 63,300 lbs 49,700 70,000 53,750 lbs 31,700 gal 24,850 gal 35,000 gal 27,000 gal 2005 Prices Cans Super pigment Stupendous pigment $0.40 $2.75 per pound $3.75 per pound Expected 2006 prices: Cans Super pigment Stupendous pigment $0.02 increase over 2005 prices $0.14 increase over 2005 prices $0.19 increase over 2005 prices Usage Standards Super Stupendous 2 pounds per gallon 2 pounds per gallon Direct Labor and machine hour information information 2005 labor rate - both departments $8.25 per hour Expected 2006 rate increase $0.25 Per hour rate increase over 2005 Production standards and information Super Machine hours/gallon 0.12 hours Labor hours per machine hr. 1.25 hours 2005 machines available 26 Annual capacity per machine 15,000 gal Machine hours per machine 1,800 hours Maximum annual hours per employee 2,000 hours Employees per supervisor 8 Stp'dous 0.12 hours 1.25 hours 20 15,000 gal 1,800 hours 2,000 hours 8 Overhead information 2005 information Indirect materials Indirect labor rate-annual Employee fringe benefits Health benefits per employee Utilities Maintenance Insurance Property taxes Supplies Depreciation - mfg Variable $0.20 per gal Fixed $50,000 per supervisor 20% of wages $1,500 per employee $0.40 per Mhr $0.20 per Mhr $10,000 $50,000 $10,000 $5,000 $250,000 annually* annually* annually* annually* annually** * These items are allocated to dpts based upon production levels in gallons **The 2005 alloction ws $141,300 for Super and $108,700 to Stupendous It is expected that the following changes will occur in 2006: Indirect materials Indirect labor rate-annual Employee fringe benefits Health benefits per employee Utilities Maintenance Insurance Property taxes Supplies Depreciation - mfg 2005 equip Depreciation- new purchases Variable no change Fixed 2.50% increase per employee no change $200 increase per employee no change $0.05 inc.per Mhr $500 annual $500 annual 8% annual $200 annual no change Five year life** increase* increase* increase* increase* * These items are allocated to dpts based upon production levels in gallons **The 2005 alloction ws $141,300 for Super and $108,700 to Stupendour 2005 depreciation Super Stupendous $108,700 $141,300 Cash Purchases for each new piece of equipment Increase Debt $25,000 $5,000 Selling department information 2005 information Variable Commissions Salaries Fringe benefits Health benefits Advertising Meals&entertainment Depreciation Fixed $0.35 per can 20% commissions $15,000 per representative 20% salaries $1,500 per representative per 100 cans $10 sold $50 per week per representative $7,500 It is expected that the following changes will occur in 2006: Variable Commissions Salaries Fringe benefits Health benefits Advertising Meals&entertainment Depreciation Fixed no change no change No change no change salaries $200 increase per representative per 100 cans $12 sold $60 per week per representative no change It is expected number of sales reps Employees during 2006 10 Administrative Department Information 2005 information Variable Salaries Fringe benefits Health benefits Professional fees Office supplies Telephone Depreciation Fixed $250,000 annual 20% wages $1,500 per employee $20,000 annually $0.03 per gal sold $0.02 per gal sold $6,000 annually It is expected that the following changes will occur in 2006: Variable Salaries Fringe benefits Health benefits Professional fees Office supplies Telephone Depreciation 3% annual increase no change $200 increase per employee $1,500 annual increase $0.01 inc per gal sold $0.0025 inc per gal sold no change It is expected number of admin. Employees during 2006 It is expected that interest rates will be 2005 Balance sheet Cash Account receivable Inventory - raw materials Inventory - finished goods Plant and equipment Less accumulated depr Fixed 150,000 827,000 383,037 551,835 1,775,000 -615,000 Total assets 3,071,872 Accounts payable Accrued wages Accrued other Long-term debt Common stock Additional Paid-in Retained earnings 383,016 76,097 74,083 1,125,000 400,000 495,000 518,676 Total liab and equity 3,071,872 9 7.50% Fantastic, Inc. Sales Volume Projection Sales in Gallons Year 2001 2002 2003 2004 2005 Super 411,000 412,000 405,000 430,000 420,000 Stupendous none none 186,250 223,500 268,200 Super Paint Volume Projection using Exponential Smoothing Actual Sales Year in Gallons Weight 2001 411,000 2002 412,000 2003 405,000 2004 430,000 2005 420,000 Weighted Sales Sales Volume Projection for 2006 Totals Stependous Paint Volume Projection using Growth Function Known x's Known y's New x Year Stupendous Year 2003 186,250 2006 2004 223,500 2005 268,200 Projection of Stupendous 2006 sales volume Fantastic Inc. Sales Budget For the Year Ended December 31, 2006 Super Paint Projected Sales Volume Selling Price Projected Sales Stupendous Paint Total Fantastic Inc. Production Budget For the Year Ended December 31, 2006 Super Paint Projected Sales Volume Desired Ending Inventory Units Needed Beginning Inventory Projected Production Stupendous Paint Total Fantastic Inc. Direct Materials Budget For the Year Ended December 31, 2006 Super Paint Stupendous Paint Cans (Units) Projected Production-gallons Desired ending inventory xxxxxxxxxxx xxxxxxxxxxx xxxxxxxxxxx xxxxxxxxxxx Units needed Beginning inventory(gal) xxxxxxxxxxx xxxxxxxxxxx xxxxxxxxxxx xxxxxxxxxxx Purchases needed Cost per unit xxxxxxxxxxx xxxxxxxxxxx xxxxxxxxxxx xxxxxxxxxxx $0.00 Cost of can purchases xxxxxxxxxxx xxxxxxxxxxx $0 Total Pigments (pounds) Projected Production-gallons Pounds per gallon Pound needed for production Desired ending inventory Pounds needed Beginning inventory Purchases needed in pounds Cost per pound $0.00 $0.00 $0 $0 Cost of pigments xxxxxxxxx xxxxxxxxx $0 $0 Fantastic Inc. Direct Labor Budget For the Year Ended December 31, 2006 Stupendous Paint Super Paint Total Projected employees needed Projected production Machine hours needed per gallon Machine hours needed Labor hours per machine hours Labor hours needed Maximum hours per employee Please use ROUND function here Projected employees needed Projected Labor costs Labor hours needed (from above) Predicted labor rate Watch for rounding here!! Please be sure this is a whole number Remember to round up! Suggestion - use ROUNDUP function. $0.00 $0.00 Labor dollars needed Direct labor fringe benefits Direct labor health benefit Total Direct labor costs This is the total wage figure for cash payments and accrual This is an "other expense" This is an "other expense" $0 $0 $0 Fantastic Inc. Manufacturing Overhead Budget For the Year Ended December 31, 2006 Stupendous Paint Super Paint Total Number of supervisors Direct labor employees needed Direct labor employees/supervisor Supervisors needed Manufacturing Overhead Variable overhead Indirect materials Utilitities Variable maintenance Watch for whole number here! Use round up function $0 $0 $0 Total variable overhead Fixed Overhead Supervisor salaries Supervisor fringe benefits Supervisor health insurance Fixed maintenance Insurance Property taxes Supplies Depreciation - manufacturing Remember to depreciation old and new equipment Total fixed overhead Total manufacturing overhead $0 $0 $0 Fantastic Inc. Capital Expenditures Budget For the Year Ended December 31, 2006 Stupendous Paint Super Paint Total Machine hours needed Machine hours per machine Number of machine needed* Machines Jan 1, 2006 Remember to round up to a whole machine!! Machine purchases needed ($30,000 per machine) Cost per machine $0 $0 $0 Total cost of desired purchases $0 $0 $0 Cash outlay for purchases Increase in debt for purchases $0 $0 $0 $0 $0 $0 * Remember to round up! For example: If you calculation determines that you will need 30.1 machines, you will have to purchase 31 machines. Fantastic Inc. Budgeted Cost of Goods Manufactured For the Year Ended December 31, 2006 Direct Materials Beginning Direct Materials Inventory Material Purchases Remember to look at the Beginning Balance Sheet Direct materials available for use Ending Direct Materials Inventory Total Raw Materials Used Direct Labor Overhead Cost of Goods Manufactured. Units costs for products Super Cost of materials per unit Stupendous $0.00 $0.00 $0 $0 Total Remember the cost of the cans Unit cost for Direct labor Unit Cost for overhead Total unit cost for 2006 production Units in finished goods inventory Value of finished goods inventory $0 Use this figure on the ending balance sheet Fantastic Inc. Selling Department Budget For the Year Ended December 31, 2006 Fixed Variable Commissions Salaries Selling fringe benefits Selling health benefits Advertising Meals & Entertainment Depreciation Totals Total $0 $0 $0 $0 $0 Fantastic Inc. Administrative Budget For the Year Ended December 31, 2006 Fixed Variable Salaries Administrative fringe benefits Administrative health benefits Professional fees Office supplies Telephone Depreciation $0 Total administative costs $0 Total $0 $0 $0 $0 Fantastic Inc. Budgeted Income Statement (Absorption) For the Year Ended December 31, 2006 Sales $0 Cost of Sales Beginning finished goods inventory Cost of goods manufactured $0 Remember to look at the beginning balance sheet Good available for sale Ending inventory Remember to look at COGMfg statement Cost of goods sold Gross margin 0 Selling expenses Administrative expenses Total selling and admin. Expenses Operating Income Interest expense Income before tax Income tax (40% rate) Net income $0 Fantastic Inc. Cash Budget For the Year Ended December 31, 2006 Increase in Cash Cash receipts Cash payments for materials Wages and commissions paid Other expenses paid Interest paid on long-term debt Income taxes paid Cash paid for new fixed assets Long-term debt repayment Dividend paid Decrease in Cash Total $0 $0 Total increases and decreases Prior year cash xxxxxxxxxx xxxxxxxxxx Cash balance December 31, 2006 xxxxxxxxxx xxxxxxxxxx Use this balance on the balance sheet Fantastic Inc. Balance Sheet December 31, 2006 Assets Cash Accounts receivable Inventory - raw materials Inventory - finished goods Plant and equipment Less accumulated depreciation $0 Remember to look at the COGMfg Remember to look at the COGMfg Total Assets Liabilities Accounts payable Accrued wages Accrued other Long-term debt $0 $0 Be sure NOT to include employee benefits Do not include non-cash expenses but remember employee benefits Total liabilities Stockholders' equity Common stock Additional paid-in capital Retained earnings $0 0 Total stockholders' equity Total liabilities and stockholders' equity $0 Strengths&Weaknesses - and Recommendations What strengths does Fantastic, Inc. have? What weaknesses does Fantastic, Inc. have? What are your recommendations? Fantastic, Inc Historical Information Sales in Gallons Year 2001 2002 2003 2004 2005 Super 411,000 412,000 405,000 430,000 420,000 Stupendous none none 186,250 223,500 268,200 Projected Selling Price for 2006 per gallon Super Stupendous $10.30 $15.45 Inventory and material information Desired Ending Beginning Inventory Inventory 56,550 cans 61,700 cans Cans Pigment Super Stp'dous Finished Good Inv Super Stp'dous 63,300 lbs 49,700 70,000 53,750 lbs 31,700 gal 24,850 gal 35,000 gal 27,000 gal 2005 Prices Cans Super pigment Stupendous pigment $0.40 $2.75 per pound $3.75 per pound Expected 2006 prices: Cans Super pigment Stupendous pigment $0.02 increase over 2005 prices $0.14 increase over 2005 prices $0.19 increase over 2005 prices Usage Standards Super Stupendous 2 pounds per gallon 2 pounds per gallon Direct Labor and machine hour information information 2005 labor rate - both departments $8.25 per hour Expected 2006 rate increase $0.25 Per hour rate increase over 2005 Production standards and information Super Machine hours/gallon 0.12 hours Labor hours per machine hr. 1.25 hours 2005 machines available 26 Annual capacity per machine 15,000 gal Machine hours per machine 1,800 hours Maximum annual hours per employee 2,000 hours Employees per supervisor 8 Stp'dous 0.12 hours 1.25 hours 20 15,000 gal 1,800 hours 2,000 hours 8 Overhead information 2005 information Indirect materials Indirect labor rate-annual Employee fringe benefits Health benefits per employee Utilities Maintenance Insurance Property taxes Supplies Depreciation - mfg Variable $0.20 per gal Fixed $50,000 per supervisor 20% of wages $1,500 per employee $0.40 per Mhr $0.20 per Mhr $10,000 $50,000 $10,000 $5,000 $250,000 annually* annually* annually* annually* annually** * These items are allocated to dpts based upon production levels in gallons **The 2005 alloction ws $141,300 for Super and $108,700 to Stupendous It is expected that the following changes will occur in 2006: Indirect materials Indirect labor rate-annual Employee fringe benefits Health benefits per employee Utilities Maintenance Insurance Property taxes Supplies Depreciation - mfg 2005 equip Depreciation- new purchases Variable no change Fixed 2.50% increase per employee no change $200 increase per employee no change $0.05 inc.per Mhr $500 annual $500 annual 8% annual $200 annual no change Five year life** increase* increase* increase* increase* * These items are allocated to dpts based upon production levels in gallons **The 2005 alloction ws $141,300 for Super and $108,700 to Stupendour 2005 depreciation Super Stupendous $108,700 $141,300 Cash Purchases for each new piece of equipment Increase Debt $25,000 $5,000 Selling department information 2005 information Variable Commissions Salaries Fringe benefits Health benefits Advertising Meals&entertainment Depreciation Fixed $0.35 per can 20% commissions $15,000 per representative 20% salaries $1,500 per representative per 100 cans $10 sold $50 per week per representative $7,500 It is expected that the following changes will occur in 2006: Variable Commissions Salaries Fringe benefits Health benefits Advertising Meals&entertainment Depreciation Fixed no change no change No change no change salaries $200 increase per representative per 100 cans $12 sold $60 per week per representative no change It is expected number of sales reps Employees during 2006 10 Administrative Department Information 2005 information Variable Salaries Fringe benefits Health benefits Professional fees Office supplies Telephone Depreciation Fixed $250,000 annual 20% wages $1,500 per employee $20,000 annually $0.03 per gal sold $0.02 per gal sold $6,000 annually It is expected that the following changes will occur in 2006: Variable Salaries Fringe benefits Health benefits Professional fees Office supplies Telephone Depreciation 3% annual increase no change $200 increase per employee $1,500 annual increase $0.01 inc per gal sold $0.0025 inc per gal sold no change It is expected number of admin. Employees during 2006 It is expected that interest rates will be 2005 Balance sheet Cash Account receivable Inventory - raw materials Inventory - finished goods Plant and equipment Less accumulated depr Fixed 150,000 827,000 383,037 551,835 1,775,000 -615,000 Total assets 3,071,872 Accounts payable Accrued wages Accrued other Long-term debt Common stock Additional Paid-in Retained earnings 383,016 76,097 74,083 1,125,000 400,000 495,000 518,676 Total liab and equity 3,071,872 9 7.50% Fantastic, Inc. Sales Volume Projection Sales in Gallons Year 2001 2002 2003 2004 2005 Super 411,000 412,000 405,000 430,000 420,000 Stupendous none none 186,250 223,500 268,200 Super Paint Volume Projection using Exponential Smoothing Actual Sales Year in Gallons Weight 2001 411,000 2002 412,000 2003 405,000 2004 430,000 2005 420,000 Weighted Sales Sales Volume Projection for 2006 Totals Stependous Paint Volume Projection using Growth Function Known x's Known y's New x Year Stupendous Year 2003 186,250 2006 2004 223,500 2005 268,200 Projection of Stupendous 2006 sales volume Fantastic Inc. Sales Budget For the Year Ended December 31, 2006 Super Paint Projected Sales Volume Selling Price Projected Sales Stupendous Paint Total Fantastic Inc. Production Budget For the Year Ended December 31, 2006 Super Paint Projected Sales Volume Desired Ending Inventory Units Needed Beginning Inventory Projected Production Stupendous Paint Total Fantastic Inc. Direct Materials Budget For the Year Ended December 31, 2006 Super Paint Stupendous Paint Cans (Units) Projected Production-gallons Desired ending inventory xxxxxxxxxxx xxxxxxxxxxx xxxxxxxxxxx xxxxxxxxxxx Units needed Beginning inventory(gal) xxxxxxxxxxx xxxxxxxxxxx xxxxxxxxxxx xxxxxxxxxxx Purchases needed Cost per unit xxxxxxxxxxx xxxxxxxxxxx xxxxxxxxxxx xxxxxxxxxxx $0.00 Cost of can purchases xxxxxxxxxxx xxxxxxxxxxx $0 Total Pigments (pounds) Projected Production-gallons Pounds per gallon Pound needed for production Desired ending inventory Pounds needed Beginning inventory Purchases needed in pounds Cost per pound $0.00 $0.00 $0 $0 Cost of pigments xxxxxxxxx xxxxxxxxx $0 $0 Fantastic Inc. Direct Labor Budget For the Year Ended December 31, 2006 Stupendous Paint Super Paint Total Projected employees needed Projected production Machine hours needed per gallon Machine hours needed Labor hours per machine hours Labor hours needed Maximum hours per employee Please use ROUND function here Projected employees needed Projected Labor costs Labor hours needed (from above) Predicted labor rate Watch for rounding here!! Please be sure this is a whole number Remember to round up! Suggestion - use ROUNDUP function. $0.00 $0.00 Labor dollars needed Direct labor fringe benefits Direct labor health benefit Total Direct labor costs This is the total wage figure for cash payments and accrual This is an "other expense" This is an "other expense" $0 $0 $0 Fantastic Inc. Manufacturing Overhead Budget For the Year Ended December 31, 2006 Stupendous Paint Super Paint Total Number of supervisors Direct labor employees needed Direct labor employees/supervisor Supervisors needed Manufacturing Overhead Variable overhead Indirect materials Utilitities Variable maintenance Watch for whole number here! Use round up function $0 $0 $0 Total variable overhead Fixed Overhead Supervisor salaries Supervisor fringe benefits Supervisor health insurance Fixed maintenance Insurance Property taxes Supplies Depreciation - manufacturing Remember to depreciation old and new equipment Total fixed overhead Total manufacturing overhead $0 $0 $0 Fantastic Inc. Capital Expenditures Budget For the Year Ended December 31, 2006 Stupendous Paint Super Paint Total Machine hours needed Machine hours per machine Number of machine needed* Machines Jan 1, 2006 Remember to round up to a whole machine!! Machine purchases needed ($30,000 per machine) Cost per machine $0 $0 $0 Total cost of desired purchases $0 $0 $0 Cash outlay for purchases Increase in debt for purchases $0 $0 $0 $0 $0 $0 * Remember to round up! For example: If you calculation determines that you will need 30.1 machines, you will have to purchase 31 machines. Fantastic Inc. Budgeted Cost of Goods Manufactured For the Year Ended December 31, 2006 Direct Materials Beginning Direct Materials Inventory Material Purchases Remember to look at the Beginning Balance Sheet Direct materials available for use Ending Direct Materials Inventory Total Raw Materials Used Direct Labor Overhead Cost of Goods Manufactured. Units costs for products Super Cost of materials per unit Stupendous $0.00 $0.00 $0 $0 Total Remember the cost of the cans Unit cost for Direct labor Unit Cost for overhead Total unit cost for 2006 production Units in finished goods inventory Value of finished goods inventory $0 Use this figure on the ending balance sheet Fantastic Inc. Selling Department Budget For the Year Ended December 31, 2006 Fixed Variable Commissions Salaries Selling fringe benefits Selling health benefits Advertising Meals & Entertainment Depreciation Totals Total $0 $0 $0 $0 $0 Fantastic Inc. Administrative Budget For the Year Ended December 31, 2006 Fixed Variable Salaries Administrative fringe benefits Administrative health benefits Professional fees Office supplies Telephone Depreciation $0 Total administative costs $0 Total $0 $0 $0 $0 Fantastic Inc. Budgeted Income Statement (Absorption) For the Year Ended December 31, 2006 Sales $0 Cost of Sales Beginning finished goods inventory Cost of goods manufactured $0 Remember to look at the beginning balance sheet Good available for sale Ending inventory Remember to look at COGMfg statement Cost of goods sold Gross margin 0 Selling expenses Administrative expenses Total selling and admin. Expenses Operating Income Interest expense Income before tax Income tax (40% rate) Net income $0 Fantastic Inc. Cash Budget For the Year Ended December 31, 2006 Increase in Cash Cash receipts Cash payments for materials Wages and commissions paid Other expenses paid Interest paid on long-term debt Income taxes paid Cash paid for new fixed assets Long-term debt repayment Dividend paid Decrease in Cash Total $0 $0 Total increases and decreases Prior year cash xxxxxxxxxx xxxxxxxxxx Cash balance December 31, 2006 xxxxxxxxxx xxxxxxxxxx Use this balance on the balance sheet Fantastic Inc. Balance Sheet December 31, 2006 Assets Cash Accounts receivable Inventory - raw materials Inventory - finished goods Plant and equipment Less accumulated depreciation $0 Remember to look at the COGMfg Remember to look at the COGMfg Total Assets Liabilities Accounts payable Accrued wages Accrued other Long-term debt $0 $0 Be sure NOT to include employee benefits Do not include non-cash expenses but remember employee benefits Total liabilities Stockholders' equity Common stock Additional paid-in capital Retained earnings $0 0 Total stockholders' equity Total liabilities and stockholders' equity $0 Strengths&Weaknesses - and Recommendations What strengths does Fantastic, Inc. have? What weaknesses does Fantastic, Inc. have? What are your recommendations