Complete a data block page and a variance analysis report using the data posted to Blackboard for the Alibaba, Inc. problem.

The first sheet in your workbook is the data block (rename it Data Block) and the analysis should be the second sheet in your workbook (rename it Analysis).

In the excel file add two additional sheets within your workbook . One will be titled Journal Entries and the other should be titled Conclusion. (See EXTRA CREDIT in the data problem posted to Bb):

REQUIRED ELEMENTS: (Must be used to receive any credit for this assignment):

Data block page including a standard cost card (actual cost card is optional).

Cell referencing from data block page to variance analysis report.

ABS function required (see ABS Function information below).

A Logic IF statement MUST be used to label the variance as Unfavorable, Favorable, or No Variance. Instructions for creating such a formula are given below.

You will receive a zero on the assignment if you do not include all of the above required components for this assignment.

REQUIRED ELEMENT: DATA BLOCK PAGE:

Your first step is to set up a Data Block page in sheet one of the excel workbook.

Standard Cost Card Format: When setting up your Data Block page, you may use the general format for a standard cost card shown below:

	Standard		Standard Price		Standard
Standard Costs:	Quantity		or Rate		Cost
Direct Materials	0.00	sq. ft.	$ 0.00	per sq. ft.	$ 0.00
Direct Labor	0.00	DLH	$ 0.00	per DLH	$ 0.00
Variable Overhead	0.00	DLH	$ 0.00	per DLH	$ 0.00
Fixed Overhead	0.00	DLH	$ 0.00	per DLH	$ 0.00
Total standard cost per unit					$ 0.00

In your spreadsheet, you will replace the zeros in the columns above with the correct standard quantity and cost amounts given in the problem or per formulas.

Remember to format your data block sheet so that numbers can easily be referenced in formulas. Wherever there is a total number to be calculated, use a formula rather than typing in totals. Let excel do the work for you.

Actual Cost (Card) Information: You will also need to input the information regarding actual production costs per unit in an actual cost card similar to the standard cost card and/or by listing the actual cost data needed. If you do include an actual cost card (I will help you set one up in the scheduled excel sessions) the direct materials quantity in the Actual Quantity column of the actual cost card refers to the number of sq. ft Used Per Unit in production NOT the number of sq. ft. purchased. The actual cost card mirrors the standard cost card, but instead of the standard cost per unit and standard amount of input per unit; it is the actual cost per unit and actual amount of input used per unit. Even if you are using an actual cost card, you will still need to include the following information in your data block page:

Total amount of material purchased

Total amount of material used

Total units produced

Total direct labor hours worked

Total denominator hours

Budgeted and Actual variable overhead cost

Budgeted and Actual fixed overhead cost

Decimals: Data Block Standard and Actual (if using one) Cost Cards: Two decimal places. Variance Analysis Report: Zero decimals places; Whole numbers only..

REQUIRED ELEMENT: CELL REFERENCING FROM DATA BLOCK TO VARIANCE REPORT:

Create an excel spreadsheet that computes the material price and quantity variances, the labor rate and efficiency variances, and the variable overhead spending and efficiency variances (and fixed overhead budget and volume variances if you are doing the extra credit part of the assignment) using cell references from the data in your data block page. Use the format shown below for your variance analysis report:

Alibaba, Inc.
Variance Analysis
For the Month Ended November 30, 2017
Material Variances:
Actual Quantity Purchased x Actual Price	$ xxxx *
Actual Quantity Purchased x Standard Price	xxxx
Material Price Variance	$ xx	Unfavorable**
Actual Quantity Used x Standard Price	$ xxxx
Standard Quantity x Standard Price	xxxx
Material Quantity Variance	$ xx	Favorable**
Labor Variances:
Actual Hours x Actual Rate	$ xxxx
Actual Hours x Standard Rate	xxxx
Labor Rate Variance	$ xx	Unfavorable**
Actual Hours x Standard Rate	$ xxxx
Standard Hours x Standard Rate	xxxx
Labor Efficiency Variance	$ xx	Favorable**

Variable Overhead Variances:
Actual Hours x Actual Rate	$ xxxx
Actual Hours x Standard Rate	xxxx
Variable Overhead Spending (Rate) Variance	$ xx	Unfavorable**
Actual Hours x Standard Rate	$ xxxx
Standard Hours x Standard Rate	xxxx
Variable Overhead Efficiency Variance	$ xx	Favorable**
Fixed Overhead Variances:
Actual Hours x Actual Rate	$ xxxx
Budgeted Fixed Overhead	xxxx
Fixed Overhead Budget Variance	$ xx	Favorable**
Budgeted Fixed Overhead	$ xxxx
Standard Hours x Standard Rate	xxxx
Fixed Overhead Volume Variance	$ xx	Unfavorable**

**Label each variance as Favorable, Unfavorable, or No Variance using a Logic IF statement (see below section on how to create a Logic IF statement).

Type in the descriptions as they are presented above in the first column (column A in excel) then input the formula (by cell references from the data block page) in the second column (column B in excel).

*Where xxxx is the end result of the formula (using cell references from your data block page) for calculating Actual Quantity Purchased times Actual Price. (For example: =+'Data Block'!B28*'Data Block'!D17.) In other words, actual quantity purchased 30,230 lbs (cell referenced from Data Block!B28) times $5.20 per lb. (cell referenced from Data Block!D17) = $157,196 the end result in the above cell $xxxx.

**Determined by using a Logical IF Statement. See instructions below and on Page 4 for the Logic IF Statement.

Put all four variances on one excel page. Format all numbers in the Variance Analysis report with 0 decimal places. Use Accounting Format. Use borders for the single underlines and the double underlines.

REQUIRED ELEMENT: ABS FUNCTION:

You will also need to use the ABS (absolute value function) in your formula when computing your variance for materials, labor, variable overhead, and fixed overhead. To do this you will need to type in =ABS(Xy-Xz) for your formula in the cell which contains the resulting variance calculation, where Xy and Xz are the cell references in your formula. ABS will convert any number calculated by your formula to its absolute value. For example:

Actual Quantity * Actual Price B5

Actual Quantity * Standard Price B6

Material Price Variance =ABS(B5-B6)

This means, since we are changing the variance to an absolute value, you will not be able to use this cell in your Logical IF statement; instead, you will need to put in the formula. See the instructions below for the Logical IF statement. You must use the ABS function in your statement or you will receive a zero for the entire assignment.

REQUIRED ELEMENT: LOGICAL IF STATEMENT INSTRUCTIONS:

Remember: DO NOT JUST TYPE Unfavorable or Favorable, or No Variance into the cell. You must use a Logic IF statement or you will receive a zero for the entire assignment.

To properly label your variance as Favorable, Unfavorable, or No Variance, an if-then formula should be used to do the work for you.

The general formula for an if-then statement is: = IF(condition, true, false)

You must tell Excel what the condition is, what to do if that condition is true, and what to do if that condition is false. Here, the condition is the variance being greater, less than, or equal to zero. We have three conditions to test for so we will have an if-then statement buried within an if-then statement. For example, you can tell Excel that if the actual quantity times the standard price minus the standard quantity times the standard price is less than zero (box 1), then it should print the comment Favorable (box 2) If the value calculated is greater than zero, the comment Unfavorable (box 3) should be printed. Finally, if there is no difference (equal to zero), then the comment should be No Variance (box 3) You MUST have all three possible outcomes in your Logic IF statement. This means that you will be inserting a Logic IF statement within a Logic IF statement (box 3) so you can account for two additional possible outcomes.

An easy way to have Excel help you create this formula is to use the formula wizard. Click on the fxbutton beside the editing toolbar. Then use the arrow key to locate more formula options (SUM should be the default). Choose IF. You will be presented with a box that asks for the necessary data. You can also modify the formula to include multiple results for multiple criteria, which is what we are doing, since we have three conditions and not just two. An example of what the IF formula might look like is:

=IF(C23-C24<0,"Favorable",IF(C23-C24=0,"No Variance", "Unfavorable"))

Check Figures: Materials Quantity Variance: $30,780 F

Variable Overhead Spending(Rate) Variance: $2,976 U

Alibaba, Inc. manufactures custom embroidered jackets. To control costs the company uses a standard cost system. The company has developed the following standards to produce one jacket:

Standard Costs:

Direct Material 3 sq. ft $3.80 per sq. ft .

Direct Labor 2.0 hr. $9.50 per hr.

Variable Manufacturing Overhead 2.0 hr. $0.58 per hr.

Fixed Manufacturing Overhead 2.0 hr. $2.25 per hr.

2017 Budgeted Data for November:

Budgeted production, 14,250 jackets

Denominator Hours, 28,500 DLH. (Alibaba, Inc. applies overhead on the basis of direct labor hours.)

Budgeted variable overhead, $16,530

Budgeted fixed overhead, $64,125.

2017 Actual Results for November:

Direct material purchases were 33,000 sq. ft. for a total cost of $128,700.

Direct material used was 32,400 sq. ft.

Direct labor costs was $240,560 for 24,800 direct labor hours actually worked.

Total variable manufacturing overhead was $17,360.

Total fixed manufacturing overhead was $56,565.

Actual production was 13,500 jackets.

REQUIRED:

1. Calculate the following variances:

a. materials price and quantity variances.

b. labor rate and efficiency variances.

c. variable overhead rate(spending) and efficiency variances.

d. fixed overhead budget and volume variances.

2. EXTRA CREDIT: (Worth 5 pts.) Add a separate sheet within your excel workbook and change the name of the sheet to Journal Entries. On this new sheet, prepare journal entries for:

a. material price and quantity variances.

b. labor rate and efficiency variances.

3. EXTRA CREDIT: (Worth 6 pts.) Add a separate sheet within your excel workbook and change the name of the sheet to Conclusion. Alibaba, Inc.s management intentionally purchased superior quality materials for November production. Answer the following questions:

1. How did this decision affect the other cost variances? Do you think the higher quality material purchased was the cause for these other variances? How?

2. What do you think may have caused some of the other variances if it was not due to the purchase of higher quality material? Give several examples.

3. Overall, do you think this was a wise decision? Explain. Support your position with an analysis of the data. (Hint: Summarize the variances. Is there a net unfavorable or favorable variance?)

Excel #2 What IF Problem & Data (2^nd excel file):

WHAT IF ANALYSIS:

Check Figure: MPV: $5,250 Unfavorable

LRV: $0 No Variance

The What If part of the assignment will help you determine whether you have correctly used cell referencing in your spreadsheets. Change your date in the heading to: For the Month Ended December 31, 2017.

In the actual costs portion of your data block page make the following changes (The budgeted cost and standard data has NOT changed from the previous month.):

Overall, Alibaba, Inc. managers were satisfied with results of operations and sales in the previous month (November) and decided to continue purchasing the higher quality material for Decembers production of the embroidered jackets. However, due to submitting the order for material later than normal, the supplier had to rush the order out and thus charged a higher shipping fee to make sure it arrived by the first of December. Alibaba, Inc. purchased 35,000 sq. ft. of material at a total cost of $138,250.

2. Direct material used in production was 34,300 sq. ft.

3. The companys long-term strategy is to replace aging machinery with more efficient

robots. This will not only increase productivity but also reduce overall labor costs. Finding skilled workers has been a major problem for the company over the last decade. In light of that strategy, in early December, factory management had two of their aging machines that were prone to breakdowns replaced with two robots. The machine breakdowns had caused some additional scrappage of material at the beginning of the month. However, the increased efficiency of the two machines resulted in the layoff of one of the production workers. Total direct labor hours for December 2017 were 24,780 hours at an overall cost of $235,410.

4. The overall cost of power decreased in December due to the installation of more

efficient machines. Total actual variable overhead for December 2017 is $14,125.

5. Insurance and depreciation costs have increased due to purchasing the new

manufacturing equipment. Total fixed overhead for December 2017 is $64,125.

6. Customers were very pleased with the higher quality product; which resulted in an

increase in demand. Total production for December 2017 has increased to 14,000 units. Demand is expected to continue to increase in the foreseeable future.

Your spreadsheet should automatically recalculate using the new data. You should not have to change any of the cell references or formulas for any of the variances. You are only changing the data block page.

1. EXTRA CREDIT: (Worth 4 pts.) Add a separate sheet within your excel workbook and change the name of the sheet to Conclusion. Answer the following questions:

1. Do you still agree with your original conclusion? Why or why not? (Hint: Summarize the variances again. Compare and contrast with your original variance summary from the previous month.)

2. Are there any other issues you think should be addressed by Alibabas management? For example: The purchase of the new robotics machinery which caused the layoff of one of the production workers. If you were one of Alibabas managers, what suggestions would you propose to help mitigate the effect on potential worker layoffs (to the company and to the laid off workers)?