Using the information provided in the case (ignoring the "Required" section of the case) complete the requirements below within the "Danshui Plant No Blank Template" Excel file located within the Harvard Business Review folder of the Doc Sharing section of our course website.
Please note: For this assignment all green cells are required to be populated entirely through the use of formulas and cell references; there should NOT be any numbers or amounts typed directly into any green cell. The only things that may be typed into the green cells of this worksheet are mathematical functions and parentheses, if necessary, for purposes of calculation via formula (it is also acceptable to manually type in a cell reference, i.e. - typing B1 as opposed to clicking on cell B1). Even when using a formula it is unacceptable to manually type a number into the formula for purposes of calculation (it is not acceptable to type =1+5, for example; the 1 and the 5 are hard-coded value inputs). All yellow cells are required to be populated entirely through the use of hard-coded, manual input (you must directly type the values that should be present within any yellow cell).
Required:
1. Using BUDGETED data create a mini contribution margin income statement illustrating the break-even point of sales for Danshui Plant No.
2. Use the information derived in your contribution margin income statement to also calculate the number units that must be sold at breakeven. ("Prep" worksheet)
2. Calculate the static budget variance for each line-item of the Danshui Plant No. 2 contribution margin income statement, depicting favorable variances in yellow text and unfavorable variances in red text. Calculate the total dollar value that will be depicted within the flexible budget column of the flexible budget for each income statement line-item. ("Prep" worksheet)
3. Calculate the expected sales price and cost per unit of product. Calculate the actual sales price and cost per unit of product. ("Prep" worksheet)
4. Prepare a complete flexible budget, including calculations of the "Flexible Budget Variance" and "Sales-Volume Variance" for each contribution margin income statement line-item; depict favorable variances in yellow text and unfavorable variances in red text. ("Flexible" worksheet)
5. "Flash memory" materials and "assembly and packaging" labor costs appear to warrant further investigation based on the significance of their flexible budget variances. Assuming a standard of one flash memory card and one direct labor hour per finished good unit, compute the price and efficiency variances for both "flash memory" materials and "assembly and packaging" labor costs. Within the orange-shaded cells use an "If then" formula that utilizes the positive or negative numerical representation of the calculated price, efficiency, and total flexible budget variances in dollars to indicate whether each of those aforementioned variances are favorable or unfavorable. ("Variances" worksheet)
6. Within the "response" worksheet provide specific reasons as to why Danshui Plant No. 2 was unprofitable at a level of 180,000 iPhone 4s produced, even though the breakeven analysis would indicate they should have been profitable at that level of production. Focus your attention on the reasons why the "flash memory" materials and "assembly and packaging" labor price and efficiency variances ended up as they did.