Requirement 7.

Prepare the budgeted income statement for the quarter ending June 30 below using variable costing and the contribution margin format.

Please also solve Requirement 7 and 8.

A B C D E F G H J K 7 Save this file in EXCEL FORMAT as: P3Lastname Highest 8 Round all answers to whole numbers 9 Do not change rows or colums on this spreadsheet. Input areas are 10 NOTE: Formulas amd cell refenremces are REQUIRED for Requirements 1-7. Lowest other than 0 yellow. 11 12 Perfect Pots, Inc. makes large, outdoor decorative flower pots that have the look of clay but are made from a polyresin 13 material that is much more durable. The controller for the company is working on the budget for the coming fiscal year, which 14 begins April 1. 15 16 Requirement 1: 17 18 Multiply the highest digit from your student ID number above by 100 and enter in the box below 19 Sales in units for April are expected to be: 600 20 21 Sales are expected to increase by 10 % each month through August. Each pot sells for $150. 22 Complete the Sales Budget for April June and the quarter in total. 23 Sales Budget 25 Sales in units April Total 24 May June 600 660 726 1,986 150 $ 150 $ 99,000 108,900 150 $ 26 Price per unit 27 Total sales dollars 150 90,000 297,900 28 29 Requirment 2: 30 31 Multiply your lowest digit (other than 0) from your student ID number above by 10 and insert for the % below Page 32 (per above of the flower pots that are expected lower pots on hand at the end of March, 33 The company wants to have 34 the following month on hand at the end of each month.TI o be sold 35 36 Complete the Production Budget for April - June and the quarter in total. Hint: You will need to calculate July's sales 37 in units in order to calculate the desired ending 38 Production Budget inventory for June. April Total May June 39 Sales in units 600 660 726 1,986 40 Desired ending inventory 41 Total required 42 Beginning inventory 43 Total units to produce 132 145 160 160 732 805 886 2,146 80 132 145 80 652 673 741 2,066 44 45 Requirement 3: 46 47 Each flower pot requires 4 lbs. of polyresin. The company wants to always have 20 % 48 on hand at the end of each month. At the end of March, there was 200 pounds 49 $5 per pound. f the materials required for the following month f polyresin on hand. Polyresin sells for 50 Hint: The next three budgets are all manufacturing cost budgets, and use units o be produced, not sold. 51 52 53 Complete the direct materials purchases budget for April June and the quarter in total. Hint: You will need to calculate units to be 54 55 Direct Materials Purchases Budget 56 Units to be produced 57 Pounds of DM per unit produced produced for July in order to calculate the April May Total June 2,066 material needs for June. 52 673 741 4 4 4 58 Total needed for production 59 Desired ending inventory 60 Total material requirement 61 Beginning inventory 62 Total pounds to purchase 2,608 2,693 2,962 8.263 539 592 652 652 3,147 3,285 3,614 8,915 200 539 592 200 2,947 2,747 3,021 8,715 Sheet + Ready 100% D F G 62 Total pounds to purchase 63 Cost per pound 64 Total cost of DM 2,947 2,747 3.021 8,715 $ 5 S 5 S 15,107 $ 43,573 S 5 14,733 $ 13,733 S 65 66 67 68 69 Requirement 4: 70 71 Direct laborers will spend 1 hour on each pot. Direct labors are paid an average of $22 per hour. 72 Complete the Direct Labor Budget below: 73 74 Direct Labor Budget 75 Units to be produced 76 DL hours per unit April May June Total 652 673 741 2,066 1 77 Total DL hours 652 673 741 2,066 22 $ 78 DL rate per hour 79 Total cost of DL 22 $ 22 S $ 14.810S 22 14,344 16.291 45,446 80 81 Requirement 5: 82 83 Multiply your lowest digit (other than 0) from your student ID number above by 1000 and insert for other fixed costs below. 84 Each flower pot requires $3 of indirect materials (variable MOH). Fixed manufacturing overhead consists of depreciation 85 of $1,000 per month, utilities of $1,200 per month, the suprevisor's salary of $4,000 per month, and other fixed costs of 86 $2,000.00 per month. 87 88 Complete the Manufacturing Overhead Budget below 89 90 Manufacturing Overhead Budget 91 Variable Overhead 92 Units to be produced 93 Variable overhead per unit 94 Total variable overhead 95 Fixed Overhead April Total May June 652 573 741 2,066 3 S 3 S 3 S 1,956 $ 2.020 S 2,222 6.197 1.000 $ Depreciation 1,000 S S 96 1,000 3,000 Supervisor's salary 4,000 97 4,000 4.000 12,000 1,200 2,000 8,200 $ $ Factory utilities 3,600 98 1,200 1.200 S 2,00 Other 2,000 8.200 5 99 6,000 00 Total fixed overhead 101 Total Manufacturing Overhead 8,200 24,600 30,797 10,156 $ 10,220 10,422 S 02 103 Requirement 6: 104 Selling expenses are $2.00 per flower pot. Administrative costs are all fixed and consist of monthly depreciation of $500, biling and 105 accounting costs of $ 2,000 and other administrative costs of $800 106 107 Complete the Operating Expense Budget below 108 09 Operating Expense Budget 1 10 Variable Selling Expenses Hint: Use units to be sold, not produced. April May June Total 1 11 Units to be sold 600 660 726 1,986 1 12 Selling expenses per unit sold 1 13 Total variable selling expenses 2 $ 2 S 2 S 1,320 $ 2 1,200 1.452 3,972 | 14 Fixed Administrative Expenses 1 15 Depreciation 1 16Billing and accounting 500 S 500 $ 500 1,500 _ 2,000 2.000 2.000 6,000 Sheet1 Ready 100% - D B E G H 114 Fixed Administrative Expenses 115 Depreciation 116 Billing and accounting 1 17 Other administrative costs 118 Total fixed administrative 119 Total Operating Expenses 500 500 JUC 500 1,500 2.000 $ 2,000 2,000 6,000 800 $ 800 800 2,400 3,300 $ 4.500 $ 3,300 $ 3,300 $ 9,900 $ 4,752 $ 13,872 4,620 $ 120 121 122 123 124 125 126 127 128 129 130 131 132 Requirement 7. 133 134 Prepare the budgeted income statement for the quarter ending June 30 below using variable costing and the contribution 135 margin format. Perfect Pots, Inc. 136 137 Contribution Income Statement Quarter Ending June 30 Total 138 139 140 Sales Per Unit 141 142 Variable Costs: 143 Direct Materials 144 Direct labor 145 Variable Overhead 146 Variable Selling Expense 147 Total Variable Costs 148 149 Contribution Margin 150 151 Fixed Costs: 152 Manufacturing Overhead 153 Administrative Expenses 154 Total Fixed Costs 155 156 Operating Income Page 3 157 158 equirement) 159 Requirement 8. (Formulas encouraged, but not requiredfor thi 160 161 Suppose actual sales for the quarter totaled 1,900 units and $279,300. Total variable costs 162 were $79,800. Calculate the following: 163 164 Actual selling price per unit 165 166 Actual variable cost per unit sold Favorable/ 167 168 Unfavorable Amount Sheet1 El Ready 100% - A B E F G K 127 128 129 130 131 132 Requirement 7. 133 134 Prepare the budgeted income statement for the quarter ending June 30 below using variable costing and the contribution 135 margin format Perfect Pots, Inc. 136 Contribution Income Statement 137 138 Quarter Ending June 30 Total 139 Per Unit 140 Sales 141 142 Variable Costs: 143 Direct Materials 144 Direct labor 145 Variable Overhead 146 Variable Selling Expense 147 Total Variable Costs 148 149 Contribution Margin 150 151 Fixed Costs: 152 Manufacturing Overhead 153 Administrative Expenses 154 Total Fixed Costs 155 156 Operating Income Page 3 157 158 159 Requirement 8. (Formulas encouraged, but not require 160 161 Suppose actual sales for the quarter totaled 1,900 units and $279,300. Total variable costs 162 were $79,800. Calculate the following: 163 164 Actual selling price per unit 165 166 Actual variable cost per unit sold Favorable/ 167 168 Amount able 169 1) Volume Variance for Sales Revenue 170 171 2) Flexible Budget Variance for Sales Revenue 172 173 3) Volume Variance for Total Variable Costs 174 175 4) Flexible Budget Variance for Total Variable Costs 176 177 178 179 180 181 1821 Sheet1 100% Ready