OK, here is the problem. It is supposed to be done in excel. E-mail me if you have any questions. Thank you! ashley_bates05@hotmail.com

Part One Big Al's Pizza, Inc., a new subsidiary of Big Al's Pizza Emporium, has been established to produce partially baked, fl ash-frozen, 16-inch meat pizzas to be sold wholesale to grocery and convenience stores and school cafeterias. As a new entrant to the market, the company's goal is to produce and sell 319,500 pizzas in the first year. Big Al's plans to keep approximately a one-month supply of frozen pizzas in finished goods inventory. Big Al's Pizza will have 10 production lines. Each of the five workers on each line will be responsible for one of the five stages of production: dough, sauce, cheese, toppings, and packaging. Each of the 10 production lines can produce 20 meat pizzas per hour. Big Al's deals exclusively with Pizza Products, Inc. to purchase raw materials and equipment. All ingredients (dough, sauce, cheese, meat, etc.) are fresh, and Big Al's generally holds a two- or three-day supply in raw materials inventory. The projected material costs are as follows: Material Costs (per pizza) Complete dough shells $.12 Complete sauce package .20 Complete cheese package .08 Complete meat package .30 Complete assembly package .04 Direct labor employees are paid on an hourly basis according to hours worked. Once production-line workers finish a day's scheduled production, they are sent home. They can work a maximum of 8 hours each day without earning overtime. The overtime premium is an additional 50 percent of the basic hourly rate of $7.50 per hour. Supervisors and other indirect labor employees are salaried. Labor Costs (estimated) Rate for direct labor $7.50 per hour (plus $2.50 per hour in fringe benefits) Indirect labor (per month): Supervisor (including fringe benefits) $3,000 Other indirect labor (including fringe benefits) $2,000 Overhead Costs (estimated, per month) Rent on production facility $1,000 Utilities $1,475 Other overhead: Indirect material $2,500 Maintenance costs $1,500 Quality inspection costs $2,000 Equipment (lease costs) $2,500 Selling and Administrative Costs (estimated, per month) Administrative salaries $4,000 Salaries of sales staff $5,000 Product promotion and advertising $2,000 Rent on offi ce space for staff $2,000 Utilities and insurance $ 500 Lease of offi ce furniture and equipment $ 800 During the fi rst year of operation, Big Al's estimated that they would produce 346,125 pizzas but actually produced 336,033 pizzas. Direct material costs were $248,664 and direct labor costs were $840,082 for 84,008 hours worked. Estimated overhead costs for the year were $191,700 while actual overhead was $193,000. Required: A. What is an appropriate cost driver for allocating overhead to pizzas in year 1? B. Using normal costing, compute the cost of each of the 336,033 meat pizzas produced in year 1. (Round down to nearest dollar.) C. Was overhead over- or underapplied during the year? By how much? Why do you think overhead was over- or underapplied during the year? Part Two In its second year of operations, Big Al's has decided to expand the product line by producing a veggie pizza. This new pizza will require the purchase of a veggie package from Pizza Products at a cost of $.18 per pizza. Of course, the meat package will not be required. All other ingredients and prices remain the same. The veggie toppings require additional processing time (cutting and dicing) that limits production to 18 veggie pizzas per hour per assembly line. The new veggie pizza is not expected to affect sales of the meat pizza. In the second year of operations, Big Al's expects to produce about 352,800 meat pizzas and 25,200 veggie pizzas . Increasing production is expected to increase overhead costs by 5 percent in year 2. Direct labor costs per hour are not expected to change, and the number of labor hours is estimated to be 94,500. The costs of product promotion and advertising are expected to increase to $3,000 per month. All other selling and administrative costs are expected to remain the same as in year 1. Actual production in year 2 was 345,132 meat pizzas and 25,200 veggie pizzas. Direct material costs transferred to WIP were $259,000 for the meat pizzas and $15,624 for the veggie pizzas. Direct labor costs were $862,830 for the meat pizzas and $70,010 for the veggie pizzas, representing 86,283 and 7,001 direct labor hours, respectively. Actual overhead costs were $203,600. Purchases of raw materials during year 2 were $276,138. Sales during year 2 were $1,701,410 for 340,282 meat pizzas and $121,275 for 23,100 veggie pizzas. SCHEDULE OF BEGINNING AND ENDING INVENTORY AMOUNTS FOR YEAR 2. Beginning Inventory Raw materials $ 1,973 Work in process 0 Finished goods 95,000 Ending Inventory $ 3,487 0 121,645 Required: A. Can Big Al's still allocate overhead based on the number of pizzas produced in year 2? B. What appears to be the most logical cost driver for allocating overhead in year 2? C. Compute a predetermined overhead rate for Big Al's in year 2. D. Using normal costing and the predetermined overhead rate calculated in requirement C, compute the total manufacturing cost for the 345,132 meat and 25,200 veggie pizzas produced in year 2, as well as the cost per pizza for each type. Was overhead over- or underapplied for the year? By how much? E. Prepare a cost of goods manufactured schedule for year 2 using actual overhead . F. Prepare a cost of goods sold schedule for year 2 using actual overhead. G. Calculate Big Al's operating income (before income taxes) for year 2. H. The marketing manager of Big Al's estimates year 3 sales of 385,000 meat and 30,000 veggie pizzas. The production manager is concerned about being able to produce that number of pizzas without incurring signifi cant overtime or making changes in the production process. Outline the problem, potential objectives, and options that the management team of Big Al's should consider. Part Three Charles Jackson, the accounting manager at Big Al's, has just returned from a conference on activity-based costing in Seattle and thinks Big Al's should consider implementing an ABC system at the beginning of year 2. Charles has identifi ed fi ve primary activities taking place in the production facility at Big Al's, has traced overhead costs to each activity, and has identifi ed a cost driver for each activity as follows : ESTIMATED YEAR 2 OVERHEAD COSTS: Activity Monthly Material delivery and handling Assembly of pizzas Packaging Storage of materials Quality inspections Overhead Cost Cost Driver Number of shipments Direct labor hours Number of pizzas Refrigerator space Number of inspections $4,620 $5,670 $1,260 $3,150 $2,100 Big Al's gets material for the veggie pizzas twice per week so the veggies can remain fresh and crisp until used in production. Veggie pizzas are also thicker, due to the height of the toppings, than a one-topping meat pizza so more refrigerator space is estimated to be needed. Big Al's has also determined that meat pizzas need to be inspected more frequently for quality control due to the much higher volume of production. Charles has estimated the activity for each cost driver as follows: Number of material shipments Direct labor hours Number of pizzas Refrigerator space (cubic feet) Number of inspections 80,000 4,000 84,000 52 2,500 Meat Pizzas 108 87,500 352,800 500 Veggie Pizzas Total 160 7,000 25,200 94,500 378,000 3,000 Required: A. Using the overhead activity categories identified above, classify Big Al's overhead costs as unit-level, batch-level, product-level, or facility-level. B. Using the preceding activities and cost drivers, calculate a predetermined overhead rate for each activity. C. Using ABC, how much estimated overhead would be allocated to a meat pizza and a veggie pizza? D. Compare the estimated overhead allocation using ABC to the estimated overhead allocation using direct labor hours. E. What are some of the advantages and disadvantages of using ABC in this case? F. Would you suggest that Big Al's adopt an ABC system? Why? G. Does the information provided by the ABC system give you some insight into areas of cost reduction? What areas have the greatest potential for cost reduction, and what are the potential impacts on the business from these cost reductions ? Part Four During the fi rst two years of operations, Big Al's overhead costs have fl uctuated from month to month. Although overhead appears to go up and down according to the number of pizzas produced each month, the accounting manager has noticed that this is not always the case. For example, in the fourteenth month of operations, the manufacturing facility was closed for almost 10 days because of severe weather, and production for the month was very low. However, overhead costs were only about 25 percent lower than those incurred in the previous month. In an effort to estimate overhead costs in year 3, the manager has collected the following information for the past 24 months of operation and has asked for your assistance in analyzing the data. Month 1 2 3 4 5 6 7 8 9 10 11 12 13 Pizzas Produced 25,784 25,897 25,750 26,352 27,567 28,492 27,398 28,112 29,499 28,879 29,344 29,399 30,879 Total Overhead Costs $17,200 17,300 17,450 17,600 17,950 18,125 17,900 17,955 18,507 18,295 18,325 18,550 19,825 14 Month 15 17 18 19 20 21 22 23 24 15,167 Pizzas Produced 28,379 30,334 30,761 31,300 31,804 31,795 32,016 32,379 32,675 14,800 Total Overhead Costs 18,732 $19,965 19,786 20,359 20,149 20,508 20,489 21,166 20,852 Required: A. Do the data present any special problems that might impact the accuracy of using either the high /low method or regression analysis to predict overhead costs? What suggestions would you make to the company accountant before analyzing the data? B. Analyze the data (both with and without month 14) using the high /low method. Estimate the fixed and variable components of total overhead costs. C. Analyze the data (both with and without month 14) using regression analysis. Estimate the fixed and variable components of total overhead costs. D. Should the accounting manager consider using an independent variable other than the number of pizzas produced to predict overhead costs? What suggestions do you have? E. During year 3, Big Al's estimates January production of 33,000 pizzas. Based on the expected production of 33,000 pizzas and the results in parts B and C, how much overhead would you estimate that Big Al's will incur in January? Part Five During the third year of operations, Big Al's estimates that 415,000 pizzas (385,000 meat and 30,000 veggie) will be produced. Direct material costs per unit are $.74 per meat pizza and $.62 per veggie pizza. Direct labor costs are $2.51 per meat pizza and $2.78 per veggie pizza. Monthly fi xed selling and administrative costs are $15,300 while monthly fi xed manufacturing overhead is $2,851. The variable overhead cost is $.55 per pizza. The sales price for veggie pizzas is $5.25 per pizza and the sales price for meat pizzas is $5.00. Required: A. Compute the break-even point in year 3 for Big Al's pizzas. How many veggie and meat pizzas must be sold in order to break even? B. What options does Big Al's have to reduce the break-even point? Discuss both the quantitative and the qualitative factors that must be considered with each option. C. How many meat and veggie pizzas, respectively, would Big Al's need to sell in year 3 to earn a before-tax profit of $150,000? D. If its tax rate is 30 percent, how many pizzas does Big Al's need to sell in year 3 to earn an after-tax profit of $150,000? E. How will the break-even point change if the sales mix changes to 80 percent meat pizzas and 20 percent veggie pizzas? F. What would happen to the break-even point if labor costs increased by 10 percent ? G. What would happen to the break-even point if Big Al's increases the sales price of veggie pizzas to $5.50 and meat pizzas to $5.25? Part Six In March of year 3, Big Al's receives a special order from an athletic arena to purchase 5,000 meat pizzas and 3,000 veggie pizzas for a special charity event. Required: A. Assuming that Big Al's has suffi cient excess capacity, what is the minimum price that Big Al's would be willing to accept for this special order? Assuming that Big Al's does not have suffi cient excess capacity, what minimum price would be acceptable? What qualitative factors should Big Al's consider before agreeing to accept the special order? B. Big Al's is nearing its manufacturing capacity and needs to consider ways to increase throughput. What options does Big Al's have to increase capacity? What bottlenecks does it face? What recommendations would you make? Part Seven Big Al's currently leases its equipment from Pizza Products for $2,500 per month. Two years of the fi ve-year lease term remain. Big Al's can terminate the lease at any time by paying a penalty of $10,000. Big Al's is considering purchasing equipment to replace the leased equipment. Big Al's must purchase 10 units of each piece of equipment. Big Al's can purchase equipment at the following prices: Equipment Price Dough ball press Assembly table Cardboard cutter Plastic sealer Label installer (per unit) $5,450 2,100 4,100 2,695 1,000 Required: A. Using NPV analysis, compare the present value of the lease payments with the cost of buying the equipment. Assuming a discount rate of 10 percent (before tax), which option is preferable? B. Big Al's has the option of purchasing equipment from another supplier at a cost of $190,000. The supplier promises that the new equipment will reduce operating costs by $1,000 per month over the life of the equipment. Assuming a 10 percent discount rate (before tax), which option is preferable? C. Calculate the after-tax NPV for each option discussed previously. If purchased, all equipment will be depreciated over fi ve years, using straight-line depreciation, and will have no salvage value. Big Al's tax rate is 30 percent. Is your decision still the same? D. What factors other than cost savings should Big Al's consider in these decision problems? Part Eight Big Al's Pizza, Inc. needs a cash budget for year 3 and has provided you with the following information. Sales are all on account and are estimated to be collected over a three-month period, with 70 percent collected in the month of sale, 25 percent collected in the next month and 4 percent collected in the third month. The remaining 1 percent is estimated to be uncollectible. December and November sales from the previous year were $201,638 and $185,000 respectively. Because of the lag in collecting cash from sales on account, Big Al's delays payment on some of its purchases of materials. Big Al's estimates that 60 percent of each month's material purchases are paid in the month of purchase and 40 percent in the following month. The accounts payable balance for materials at the end of the previous year was $20,000. Big Al's also requires a minimum balance of $40,000 in cash at the end of each month. The company will use its line of credit when needed to bring the balance up to that minimum level. For any money borrowed, the interest rate is 6 percent compounded annually. For simplicity, you can assume that cash is borrowed on the fi rst day of the month and that loan repayments are made at the end of the month. Big Al's plans to exercise the option on the leased production equipment in March (as described in Part Seven). The purchase price on the equipment will be $153,450, with payments of $3,260.36 per month. Big Al's also plans on expanding the existing production space in May at a cost of $200,000. Big Al's would like to fi nance the expansion out of current earnings and so will use the line of credit, if necessary, in May. The expansion will cause fi xed manufacturing overhead to increase by $10,000 per month, starting in May. Required: A. Prepare a cash receipts budget for year 3, assuming estimated sales of 385,000 meat pizzas and 30,000 veggie pizzas and the following monthly distribution of sales. January February March April May June 8.3% 9.2 10.3 7.6 8.0 6.9 July August September 7.5 October November 7.2 December 7.6 8.5% 9.8 9.1 Direct material costs per unit are $.74 per meat pizza and $.62 per veggie pizza. Direct labor costs are $2.51 per meat pizza and $2.78 per veggie pizza. Monthly fi xed selling and administrative costs are $15,300, while monthly fi xed manufacturing overhead is $2,851. The variable overhead cost is $.55 per pizza. The sales price for veggie pizzas is $5.25 per pizza and the sales price for meat pizzas is $5.00. B. Prepare a cash disbursements budget for the year. C. Prepare a summary cash budget for the year, showing any borrowing and repayment of debt with interest. Discuss Big Al's ability to repay the expansion loan. Include a discussion of the feasibility of the project. Include qualitative factors to be considered. D. What if the sales forecast was increased by 50 percent? What impact does that have on the budget, and what is the potential impact on the company? Part Nine In December (month 24) of year 2 of operations, Big Al's produced and sold 32,675 pizzas, consisting of 30,570 meat pizzas and 2,105 veggie pizzas. The budgeted sales price for meat pizzas was $5.00, and for veggie pizzas, $5.25. The estimated production and sales during December was 31,678 meat pizzas and 2,595 veggie pizzas. Required: A. Compute the price and volume variances for sales, assuming that Big Al's sold all pizzas produced for $137,565 (meat) and $9,051 (veggie). What might explain these variances? B. Compute the price and quantity variances for direct materials for each type of pizza, assuming that Big Al's paid $29,093 for 32,325 units of raw material for meat pizzas and $1,453 for 2,401 units of raw material for veggie pizzas. (A unit consists of dough shell, sauce, cheese, meat or veggies, and assembly materials.) In addition, 30,995 units were used to produce meat pizzas, and 2,149 units were used to produce veggie pizzas. How would these variances be interpreted? What might explain these variances? Would you consider them to be large enough to be important? C. Compute the labor rate and effi ciency variances, assuming that Big Al's paid $71,350 in labor costs for 7,150 hours of labor for meat pizzas and $6,425 in labor costs for 650 hours of labor for veggie pizzas. How would these variances be interpreted? What might explain them? Would you consider them to be large enough to be important? D. Using the predetermined overhead rate from Part Three (with direct labor hours as the cost driver), compute the variable overhead rate and effi ciency variances. Assume that Big Al's paid $20,852 in total overhead costs, consisting of $17,002 of variable overhead and $3,850 of fi xed overhead. How would these variances be interpreted? What might explain these variances? Would you consider them to be large enough to be important? E. How might Big Al's extend its variance analysis to be compatible with its use of activity-based costing as discussed in Part Three? Part Ten Big Al's Pizza Emporium, a subsidiary of Big Al's Pizza, Inc., has decided to sell frozen pizzas in its restaurants. Restaurant customers will take these pizzas home and cook them. The restaurants will sell frozen meat pizzas for $10 and frozen veggie pizzas for $11.50. Although the restaurants would like to purchase the pizzas from Big Al's Pizza, Inc., Big Al's Pizza Emporium has found an unrelated supplier that will provide the meat pizzas for $4.55 and veggie pizzas for $5.75. Assume that Big Al's Pizza, Inc. has excess capacity and can supply the frozen pizzas to Big Al's Pizza Emporium without impacting current sales. Required: A. Using estimated cost data for year 3, given in Part Eight (question A), at what minimum price should Big Al's Pizza, Inc. agree to transfer meat and veggie pizzas to Big Al's Pizza Emporium? B. What is the maximum price that Big Al's Pizza Emporium should pay Big Al's Pizza, Inc. for the pizzas? C. If Big Al's Pizza Emporium purchases pizzas from Big Al's Pizza, Inc., what is the ideal transfer price? Why? D. Should Big Al's Pizza Emporium buy pizzas from Big Al's Pizza, Inc.? What qualitative factors should be considered in making this decision? E. What should the transfer price be if Big Al's Pizza, Inc. is at full capacity