Excel Assignment #2
Preparing a Contribution Margin Income Statement and Operating Leverage
Summer 2013
1. Assume that a company is budgeting to sell 2,500 units of a product at a selling price per unit of $32. The variable cost per unit is $26 and total fixed costs are $5,000.
REQUIRED
Prepare a contribution margin income statement and calculate operating leverage.
2. Suppose the company is unsure exactly how many units they will sell. As such, their marketing department has provided a worst case scenario where sales would be 1,500 units and a best case scenario where sales would be 2,700 units. Assume that the selling price per unit, variable cost per unit and fixed costs will remain constant (per part 1).
REQUIRED
…show more content…
Rules regarding the completion of the Excel Assignments:
NOTE: You MUST use Microsoft EXCEL and not any other spreadsheet program.
1) You MUST use the Excel Templates provided on the Accounting 1020 website for this quarter.
2) You MUST download the Excel Templates using YOUR account from the Accounting 1020 website. Each template is individually coded to prevent academic dishonesty. Any student found using the template from another student’s account AND the student that allows another student to use his/her account will also receive a zero on the assignment and be subject to further penalties for academic dishonesty.
3) You MUST sign the academic honesty pledge or you will automatically receive a zero--NO EXCEPTIONS!
4) You MUST use formulas and links whenever possible. If you simply type in your solutions rather than use formulas, you will receive a zero.
5) You MUST name your Excel files per the instruction sheet (LastNameFirstNameExcel2). Failure to name your Excel file correctly will result in a zero--NO EXCEPTIONS. Please make sure that there are no blank spaces in the file name (e.g., SmithJohnExcel2)
6) Your Instructor will inform you how to send your assignments electronically. Failure to follow YOUR INSTRUCTOR’S INSTRUCTIONS will result in a grade of zero for the assignment (note that each instructor may have his/her own required method of turning in the assignment).
7) To receive credit,
QUESTION 4: Kai decides to add color and keep his price the same. This will increase variable costs by $0.40 per issue. What will be the new unit volume (copies per issue) required to maintain $500 profits and cover the increased fixed and variable costs?
3. Variable cost per unit will be reduced from $25 to $18.80. A total of 1,000,000 units will still be sold. The reduction in variable costs per unit is a direct result of the increased fixed costs and the associated automation.
1- The total unit cost = Total Variable Cost + Production Fixed Expenses + Advertising Expense + Selling and Administrative Expense = 3.23 + 1.20 + 0.30 + 0.19 = 4.92.
In the worst case scenario, we assume there is a 5% fluctuation in unit sale price and unit variable
1. For financial accounting purposes, what is the total amount of product costs incurred to make 10,000 units?
Once you have worked through the problems, please post your responses to the Dropbox. Please refer to the course Syllabus for the due date.
8. REQUIRED TO TURN IN (in this order): Typed multi-step income statement, Typed statement of retained earnings, Typed classified balance sheet, Typed Perpetual Inventory cards, Handwritten worksheet for uncollectible accounts expense calculations, and Handwritten June Journal Entries, the additional adjusting entries and all necessary closing entries. Be sure your name is on all pages.
In our second assumption, instead of using the cost of goods per cases in 1986, we try to use the percentage it counts in the total expenses which is 50.4% and to find the sales needed to break-even. The detail of the calculation is shown in the answer for questions d. The result is that 95,635, a little bit higher than the estimated sales of 90,000.
= Unit Selling Price – Unit Variable Cost = $9.00 – ($1.25 + $0.35 + $1.00) = $6.40
According to this method, every unit of the product is assigned all direct, fixed, and variable costs. This method includes the cost of direct materials and labor as well as a portion of the overhead costs associated with it in the final costing of every unit of the product.
1. Tutti’s Sandwich Shop has the following information regarding costs at various levels of monthly sales. Help Tutti separate her costs into fixed costs and variable costs so that she can predict and evaluate costs at varying levels of guests served.
For providing information requested in preparing a summary for the CPP reporting requirements onT4 information slips. The outline will be utilized to validate the present payroll setup to guarantee that the T4s will be completed legitimately in future. Give data on the CPP related boxes that must be completed, including how they are calculated, for
As an example, if fixed costs are $100, price per unit is $10, and variable costs per unit are $6, then the break-even quantity is 25 ($100 ÷ [$10 − $6] = $100 ÷$4). When 25 units are produced and sold, each of these units will not only have covered its own marginal (variable) costs, but will have also have contributed enough in total to have covered all associated fixed costs. Beyond these 25 units, all fixed costs have been paid, and each unit contributes to profits by the excess of price over variable costs, or the contribution margin. If demand is estimated to be at least 25 units, then the company will not experience a loss. Profits will grow with each unit demanded above this 25-unit break-even level.
All the costs by a company can be broken into two categories, fixed costs and variable costs. Costs that are independent of output are called fixed costs. Fixed costs remain constant throughout the relevant range and are usually considered sunk for the relevant range. Buildings and machinery are included inputs that cannot be adjusted in the short term. They are only fixed in relation to the quantity of production for a certain time period. The cost of all inputs is variable, in the long run.