Excel Assignment #1 Winter 2007



Excel Assignment #1ACCT 20200 Summer 2013Part 1: Preparing a Contribution Margin Income Statement 1. Assume that a company is budgeting to sell 1,845 units of a product at a selling price per unit of $36. The variable cost per unit is $24 and total fixed costs are $4,800. REQUIREDPrepare a contribution margin income statement.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,445 units and a best case scenario where sales would be 2,100 units. Assume that the selling price per unit, variable cost per unit and fixed costs will remain constant (per part 1).REQUIREDPrepare a contribution margin income statement for both the worst case scenario (sales of 1,445 units) and the best case scenario (sales of 2,100 units).3. Suppose the company believes that 1,845 units is the most likely volume of sales. However, it is unsure at what selling price per unit it will be able to charge. The marketing department has provided a high estimate of $42 per unit and a low estimate of $29 per unit. Assume that variable costs per unit and fixed costs will remain constant (per part 1).REQUIREDPrepare a contribution margin income statement for both the high ($42 per unit) and low ($29 per unit) estimate of the selling price.4. Suppose the company believes that 1,845 units is the most likely volume of sales and that $36 is the most likely selling price per unit. However, the production department is unsure as to the exact variable cost per unit. The production department has provided a high cost per unit of $33 and a low cost per unit of $20. Assume that fixed costs will remain constant (per part 1).REQUIREDPrepare a contribution margin income statement for both the high variable cost per unit ($33) and the low cost per unit ($20).5. Suppose the company believes that 1,845 units is the most likely volume of sales, that $36 is the most likely selling price per unit, and that $24 is the most likely variable cost per unit. However, the accounting department is unsure as to the exact value of fixed costs. They have provided a high estimate of fixed costs to be $6,600 and a low estimate of fixed costs to be $2,900.REQUIREDPrepare a contribution margin income statement for both the high estimate of fixed costs ($6,600) and for the low estimate of fixed costs ($2,900).6. Based on the contribution margins you have created in parts 1-5, answer the following questions (your comparison is relative to part 1 data):When sales volume decreases,What is the impact on net income?What is the impact on the contribution margin?When sales volume increases,What is the impact on net income?What is the impact on the contribution margin?When the sales price per unit increases,What is the impact on net income?What is the impact on the contribution margin?When the sales price per unit decreases,What is the impact on net income?What is the impact on the contribution margin?When the variable cost per unit increases,What is the impact on net income?What is the impact on the contribution margin?When the variable cost per unit decreases,What is the impact on net income?What is the impact on the contribution margin?When fixed costs increase,What is the impact on net income?What is the impact on the contribution margin?When fixed costs decrease,What is the impact on net income?What is the impact on the contribution margin?Excel Assignment #1ACCT 20200 Summer 2013Part 2: Mixed CostsTeresa Company has had trouble estimating its total costs. The company knows that some of its costs are variable and some are fixed, but no idea how to separate out the variable and fixed components. They have asked you to evaluate their costs so that they can estimate them for the upcoming budget. They expect that their costs are related to Sales.MonthSalesTotal CostJanuary$3,359,390 $2,511,102 February$2,568,078 $2,475,480 March$3,126,349 $2,352,690 April$2,477,597 $2,112,343 May$3,644,510 $2,792,431 June$3,537,486 $2,655,898 July$2,960,222 $2,468,292 August$3,024,270 $2,530,523 September$3,017,485 $2,227,392 October$2,967,485 $2,262,935 November$3,701,534 $2,553,913 December$2,640,893 $2,037,639 What is the mixed cost (y-values)?What is the activity (x-values)?What is the high month?What is the low month?Using the high/low method, determine the variable rate (slope).[Include the calculation of the change in y, change in x, and calculation of slope]Using the high/low method, determine the fixed cost portion of the mixed cost (intercept).[Formula should include link to slope calculated in part 5 and high(low) data in the original data. Use b=y-mx where, y and x are the high or low amounts from the given data and m is the slope calculated in part 5]Using the excel regression formula (=slope), determine the variable rate (slope) Using the excel regression formula (=intercept), determine the fixed cost portion of the mixed cost (intercept). If the company expects Sales to be $3,250,000, estimate the Total Costs using both the results from the high-low method (part 5 &6) and regression (part 7&8).[Determine Total Cost using high/low method (linking to parts 5&6) and Total Cost using regression (linking to parts 7&8)]If Sales are $3,250,000, prepare a contribution margin income statement using both the high-low method and regression variable cost rates and fixed costs.Excel Rules:You must use the Excel Templates provided on the ACCT 20200 website (). Note that each template is individualized for each student with an ID code for each student. It will be assumed a Honor Code VIOLATION if you use a template with someone else’s ID Code.You must use Microsoft EXCEL and not any other spreadsheet program. Failure to use MS Excel will result in a zero grade.You must work individually on this assignment. Working with another student is a violation of the Notre Dame Honor code and will be enforced when discovered.You must use links and formulas where ever possible. Failure to exploit Excel will result in a zero grade. There are only a couple of instances where typing numbers may be necessary.You must sign your name to the honor code statement (the first sheet of the Excel template). Failure to sign your name (by typing your name) will result in a zero grade.You must name your Excel file as: last name first name Excel 1. Failure to name your file in this exact syntax will result in a zero grade.You must save your file as either an xls or xlsx file so that it can be read by your instructor. Failure to turn in a file that is readable by your instructor will result in a zero grade.You must follow your instructor’s instructions on how to turn in the assignment. Failure to follow your instructor’s instructions will result in a zero grade. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download