Term project part one, all instructions and sheets needed are attached

ACC330 Cost Accounting Term Project Overview: An Integrative Case - Luke Corporation The term project is the completion of an integrative case problem that relies on the Cost accounting principles you have learned during the first two units of the course. The Case requires extensive calculations in Microsoft Excel. You will submit a portion of your project in Unit 3 and the final portion of your project in Unit 5. All parts of your project will be submitted in Connect. The scenario for this project is located in Connect. Important Note: Be sure to allow yourself sufficient time to complete each part of the case, as some parts will require more than an hour to complete. Project Milestones and Schedule of Delivery Below is a table of project milestones that you will deliver throughout this term. These milestones will help you to manage your time effectively. ACC330 Course Project Summary of Requirements Project Part Milestones Part 1: Determine the minimum selling price & the break-even analysis volume Unit 3 ** Note this portion of the project is very time intensive. Please plan your time accordingly. Part 2 : Determine change in Net Profit or Loss if Product is Dropped Unit 5 Instructions Part 1 For Part 1 of your course project you will determine the (Part 1 a) special order minimum price and the (Part 2 b) break-even volume for Bubbs bubble gum product. This portion of your project is due in Unit 3. Please note you will need to calculate the special order minimum price to determine the break-even volume. The scenario is located in the Course Project Part 1 section in Connect. Part 1 a - Special Order Minimum price For part of this project you will compute the minimum special order price for the Bubbs bubble gum product. 1. 2. Log-in to Connect and access the Term Project Part 1 under Unit 3. Carefully read through the Luke Corporation scenario on the Part 1 (a). You may want to print the case background information. 3. Using MS Excel, perform a regression analysis on the Bubbs product cost data. Note - you will need to manually input this information into Excel. 4. From the results of your analysis, determine the minimum special order price that Luke Corporation can offer to Bunk. Note that the special order is not subject to any corporate allocation costs. Save your regression calculations in an Excel spreadsheet. Make sure to show ALL your work. 90% of the points for this assignment are based on your calculations. Only 10% is based on getting the correct answer. 5. When you have completed Part 1a, go to Connect and enter your special order price in question 1and your calculations in question 2. Name the file containing your output from your regression analysis using the following file naming convention: Course Number, Project Part, your Last Name and your Course Section. For example: ACC330_Project_Part1a_Jones_AO.xml Part 1 b - Break-even Sales volume For part 1 b of this project you will compute the break-even volume for the Bubbs bubble gum product. Please note that you will need the calculation from part 1a to complete this section. 1. 2. Log-in to Connect and access the Term Project part 1 b under Unit 3. Carefully read through the Integrative Case scenario on the Part 1 b. You may want to print the case background information. This is the same case information that was used in Part1 a of this project. 3. Using the High-Low method, estimate the variable corporate overhead cost as a percentage of Revenue for Bubbs gum. 4. From the results of your estimation, determine the number of cases of gum that Luke Corporation must sell in order to break-even on the product. Note that the minimum special order price and the fixed costs that you calculated in your regression analysis on Part 1(a) of the project will be needed to compute this. You should use the following formula: Profit = (Product Price x Q) - (Variable product costs x Q) - (Variable corporate Overhead cost % x Product price x Q) - Fixed production costs Where Q = the Break-even quantity Write your calculations in a Microsoft Excel spreadsheet. Make sure to show ALL your work. 90% of the points for this assignment are based on your calculations. Only 10% is based on getting the correct answer. You will not receive partial credit if you do not turn-in the calculations. 5. When you have completed Part 1b, go to Connect and enter your break-even volume solution inquestion 3 and submit your calculations in question 4. Name your calculations containing your output from your regression analysis file using the following file naming convention: name your file with you last name and section number for your course. For example: ACC330_Project_Part1b_Jones_AO.xml Term Project Part 1 (a) - Special order Minimum price Luke Corporation produces a variety of products, each within their own division. Last year, the managers at Luke developed and began marketing a new chewing gum, Bubbs, to sell in vending machines. The product, which sells for $5.25 per case, has not had the market success that managers expected and the company is considering dropping Bubbs. The product-line income statement for the past twelve months follows: Revenue Costs $14,682,150 Manufacturing costs $ 14,440,395 Allocated corporate costs (@5%) 734,108 15,174,503 Product-line margin Allowance for tax (@20%) $ (492,353) 98,470 Product-line profit $ (393,883) All products at Luke receive an allocation of corporate overhead costs, which is computed as 5 percent of product revenue. The 5 percent rate is computed based on the most recent year's corporate cost as a percentage of revenue. Data on corporate costs and revenues for the past two years follow: Most recent year Previous year Corporate Revenue $106,750,000 $ 76,200,000 Corporate Overhead Costs $5,337,500 $4,221,000 Roy O. Andre, the product manager for Bubbs, is concerned about whether the product will be dropped by the company and has employed you as a financial consultant to help with some analysis. In addition to the information given above, Mr. Andre provides you with the following data on product costs for Bubbs: Month 1 2 3 4 5 6 7 8 9 10 11 12 Cases 207,000 217,200 214,800 228,000 224,400 237,000 220,200 247,200 238,800 252,600 250,200 259,200 Production Costs $1,139,828 1,161,328 1,169,981 1,185,523 1,187,827 1,208,673 1,183,699 1,226,774 1,225,226 1,237,325 1,241,760 1,272,451 Required: (a Bunk Stores has requested a quote for a special order of Bubbs. This order ) would not be subject to any corporate allocation (and would not affect corporate costs). What is the minimum price Mr. Andre can offer Bunk without reducing profit any further? (**NOTE - This problem requires that you perfomr a regression analysis in MS Excel offline. Round your answer to 2 decimal places, and omit the "$" sign in your response.) Minimum price $ per case Upload Your Regression Analysis Here Please upload the results of your Regression Analysis here. Term project Part 1 (b) - Break-even Volume Luke Corporation produces a variety of products, each within their own division. Last year, the managers at Luke developed and began marketing a new chewing gum, Bubbs, to sell in vending machines. The product, which sells for $5.25 per case, has not had the market success that managers expected and the company is considering dropping Bubbs. The product-line income statement for the past twelve months follows: Revenue Costs $14,682,150 Manufacturing costs $ 14,440,395 Allocated corporate costs (@5%) 734,108 15,174,503 Product-line margin Allowance for tax (@20%) $ (492,353) 98,470 Product-line profit $ (393,883) All products at Luke receive an allocation of corporate overhead costs, which is computed as 5 percent of product revenue. The 5 percent rate is computed based on the most recent year's corporate cost as a percentage of revenue. Data on corporate costs and revenues for the past two years follow: Most recent year Previous year Corporate Revenue $106,750,000 $ 76,200,000 Corporate Overhead Costs $5,337,500 $4,221,000 Roy O. Andre, the product manager for Bubbs, is concerned about whether the product will be dropped by the company and has employed you as a financial consultant to help with some analysis. In addition to the information given above, Mr. Andre provides you with the following data on product costs for Bubbs: Month 1 2 3 4 5 6 7 8 9 10 11 12 Cases 207,000 217,200 214,800 228,000 224,400 237,000 220,200 247,200 238,800 252,600 250,200 259,200 Production Costs $1,139,828 1,161,328 1,169,981 1,185,523 1,187,827 1,208,673 1,183,699 1,226,774 1,225,226 1,237,325 1,241,760 1,272,451 Required: (b How many cases of Bubbs does Luke have to sell in order to break even on the ) product? (Round your variable costs to 2 decimal places, fixed costs to nearest whole dollar amount, other intermediate calculations to 3 decimal places and final answer to nearest whole number.) Number of cases rev: 07_14_2011 Upload Your Break-even Calculations Here Please upload your break-even calculations here. You may use either Word or Excel for these calculations. Make sure to show ALL your work. Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Overhead costs $ 9,891 $ 9,244 $ 13,200 $ 10,555 $ 9,054 $ 10,662 $ 12,883 $ 10,345 $ 11,217 $ 13,269 $ 10,830 $ 12,607 $ 10,871 $ 12,816 $ 8,464 Repair Hours 248 248 480 284 200 380 568 344 448 544 340 412 384 404 212 Part Costs $ 1,065 1,452 3,500 1,568 1,544 1,222 2,986 1,841 1,654 2,100 1,245 2,700 2,200 3,110 752