Excel #2 Problem & Data (1st excel file):

Check Figures: Materials Quantity Variance:  $6,650 U

                          Direct Labor Efficiency Variance:  $9,880 U

 

Garden City Pots, Inc. manufactures large flower pots for city and urban settings.  It sells its large flower pots to cities, municipalities, and large businesses to be used it their landscaping designs.  Garden City Pots has developed the following per unit standard costs for 2016 for each large flower pot:

Standard Costs:                    

Direct Material                                    15 lbs. resin                 $5.00 per lb.   

Direct Labor                                        2.0 hr.                          $13.00 per hr.

Variable Manufacturing Overhead     2.0 hr.                          $?per hr.

Fixed Manufacturing Overhead         2.0 hr.                          $?per hr.         

 

2016 Budgeted Data:

Budgeted production, 2,000 large garden pots

Denominator Hours, 4,000 DLH.  (Garden City Pots, Inc. applies overhead on the basis of direct labor hours.)

Budgeted variable overhead, $24,800.

Budgeted fixed overhead,  $23,600.

 

2016 Actual Results:            

Direct material purchases were 30,230 lbs. of resin which cost $157,196.

Direct material used was 29,830 lbs. of resin.

Direct labor costs $54,720 for 4,560 direct labor hours actually worked.

Total variable manufacturing overhead was $28,272.

Total fixed manufacturing overhead was $23,484.

Actual production was 1,900 large flower pots.

 

REQUIRED:

1.         Calculate the following variances:

            a. materials price and quantity variances.

b.  labor rate and efficiency variances.

c.  variable overhead spending(rate) 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 “Causes”.  For the material price and quantity variances and labor price and efficiency variancesin the Analysis Report, state 3 reasons that could explain why each of these variances occurred.  Use complete sentences and detailed explanations that is specific to your variance results.  Do NOT use a “bullet point” format.  If you just use a bullet point format without any explanation referencing the specific problem and variances you calculated, you will not receive ANY extra credit for this part of the assignment.

 

Excel #2 What IF Problem & Data (2ndexcel file):

WHAT IF ANALYSIS:

Check Figure: MPV: $6.000 Favorable

                   VOH EV:  $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 Year Ended December 31, 2017.”

 

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

1.      Due to a supplier going out of business, Garden City Pots, Inc. was able to

purchase material (resin) at a significant discount.  They purchased 30,000 lbs. of resin at a total cost of $144,000.

            2. Direct material (resin) used in production was 32,010 lbs. of resin

            3.  Factory management has replaced its aging machinery that was prone to breakdowns

            with new computerized equipment. The new equipment has contributed to an overall

improvement in theefficiency of its production workers.  However, Garden City Pots, Inc.also negotiated a new 2 year contract with the union shop which increased the actuallabor wage rate.  Total direct labor hours for 2017 are 4,400 hours at an overall higher cost of $59,400. 

4.  Total actual variableoverhead for 2017 is $26,400. 

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

manufacturing equipment.  Total fixed overhead for 2017 is $23,980.

6.  Due to an increase in demand, total production for 2017 has increased to 2,200

unitsin 2017.

 

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.

 

 

 

 

 

    • 7 years ago
    excel - Garden City Pots
    NOT RATED

    Purchase the answer to view it

    blurred-text
    • attachment
      accounting_-_quiz_-_30_oct.xlsx
    • attachment
      accounting_-_quiz-2-30_oct.xlsx