Accounting assignment in excel.. lengthy and need help

Excel #2 Problem & Data (1st excel file): 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 Direct Labor Variable Manufacturing Overhead Fixed Manufacturing Overhead 3 sq. ft 2.0 hr. 2.0 hr. 2.0 hr. $3.80 per sq. ft $9.50 per hr. $0.58 per 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 1. 2. 3. 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? 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. 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?) REQUIREMENTS: Complete a data block page and a variance analysis report using the data posted to Blackboard for the Alibaba, Inc. problem. o 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): o Data block page including a standard cost card (actual cost card is optional). o Cell referencing from data block page to variance analysis report. o ABS function required (see ABS Function information below). o 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. o 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 Costs: Direct Materials Direct Labor Variable Overhead Fixed Overhead Total standard cost per unit Standard Quantity 0.00 sq. ft. 0.00 DLH 0.00 DLH 0.00 DLH Standard Price or Rate $ 0.00 per sq. ft. $ 0.00 per DLH $ 0.00 per DLH $ 0.00 per DLH Standard Cost $ 0.00 $ 0.00 $ 0.00 $ 0.00 $ 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. 2 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: 1 Total amount of material purchased 2 Total amount of material used 3 Total units produced 4 Total direct labor hours worked 5 Total denominator hours 6 Budgeted and Actual variable overhead cost 7 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 Actual Quantity Purchased x Standard Price Material Price Variance $ xxxx * xxxx $ xx Unfavorable** Actual Quantity Used x Standard Price Standard Quantity x Standard Price Material Quantity Variance $ xxxx xxxx $ xx Favorable** Labor Variances: Actual Hours x Actual Rate $ xxxx 3 Actual Hours x Standard Rate Labor Rate Variance xxxx $ xx Unfavorable** Actual Hours x Standard Rate Standard Hours x Standard Rate Labor Efficiency Variance $ xxxx xxxx $ xx Favorable** Variable Overhead Variances: Actual Hours x Actual Rate Actual Hours x Standard Rate Variable Overhead Spending (Rate) Variance $ xxxx xxxx $ xx Unfavorable** Actual Hours x Standard Rate Standard Hours x Standard Rate Variable Overhead Efficiency Variance $ xxxx xxxx $ xx Favorable** Fixed Overhead Variances: Actual Hours x Actual Rate Budgeted Fixed Overhead Fixed Overhead Budget Variance $ xxxx xxxx $ xx Favorable** Budgeted Fixed Overhead Standard Hours x Standard Rate Fixed Overhead Volume Variance $ xxxx xxxx $ 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: 4 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 Actual Quantity * Standard Price Material Price Variance B5 B6 =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. 5