2 Project 3 - Operating Budget Enter the last 4 digits of your student ID number: 4 Name/s: Spencer Brooks 9196 6 7 Save this file in EXCEL FORMAT as: P3Lastname 8 Round all answers to whole numbers 9 Do not change rows or colums on this spreadsheet. Input areas are in yellow 10 NOTE: Formulas amd cell refenremces are REQUIRED for Requirements 1-7 Highest Lowest other than 0 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 16 Requirement 1: 17 18 Multiply the highest digit from your student ID number above by 100 and enter in te box below 19 Sales in units for April are expected to be: 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 24 25 Sales in units 26 Price per unit 27 Total sales dollars 28 900 April Sales Budget May Total June 900 150 $ 990 1,089 2,979 150 150 S 150 S S 135,000 S 148,500 S 163,350 $ 446,850 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: 32 33 The company wants to have 34 the following month on hand at the end of each month. There were 80 flower pots on hand at the end of March 35 36 Complete the Production Budget for April June and the quarter in tota 37 38 Production Budget 39 Sales in units 40 Desired ending inventory 41 Total required 42 Beginning inventory 43 Total units to produce aae flower pots that are expected to be sold 10 Hint: You will need to calculate July's sales in units in order to calculate the desired ending May Tota inventory for June. 2,979 April June 900 1,089 990 July sales 80 919 45 Requirement 3: 46 47 | Each flower pot requires 4 lbs. of polyresin. The company wants to always have 20% of the materials required for the following month 48 on hand at the end of each month. At the end of March, there was 200 pounds of polyresin on hand. Polyresin sells for 49 $5 per pound 50 51 52 53 Complete the direct materials purchases budget for April-June and the quarter in total 54 55 Direct Materials Purchases Budget 56 Units to be produced 57 Pounds of DM per unit produced 58 Total needed for production 59 Desired ending inventory 60 Total material requirement 61 Beginning inventory 62 Total pounds to purchase 63 Cost per pound 64 Total cost of DM 65 Hint: The next three budgets are all manufacturing cost budgets, and use units to be produced,not sold Hint: You will need to calculate units to be ril Tota produced for July in order to calculate the May June 919 material needs for June 200 Requirement 4: 1 Direct laborers will spend 1 hour on each pot. Direct labors are paid an average of $22 per hour. Complete the Direct Labor Budget below: Total April Direct Labor Budget Units to be produced DL hours per unit Total DL hours DL rate per hour Total cost of DL May une 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 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 96Depreciation 97 Supervisor's salary 98 Factory utilities 99 Other 100 Total fixed overhead 101 Total Manufacturing Overhead 102 per month. Total June May April 102 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 109 Operating Expense Budget 110 Variable Selling Expenses 111 Units to be sold 112 Selling expenses per unit sold 113 Total variable selling expenses 114 Fixed Administrative Expenses 115 Depreciation 116 Billing and accounting 117 Other administrative costs 118 Total fixed administrative 119 Total Operating Expenses 120 121 May Total Hint: Use units to be sold, not produced. April June 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. 136 137 138 139 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 53 Administrative Expenses 154 Total Fixed Costs 155 156 Operating Income 157 158 Perfect Pots, Inc Contribution Income Statement Quarter Ending June 30 Per Unit Total ade 1S7 158 159 Requirement 8. (Formulas encouraged, but not required for this requireme 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 167 168 169 1) Volume Variance for Sales Revenue 170 1712) Flexible Budget Variance for Sales Revenue 172 173 3) Volume Variance for Total Variable Costs 174 1754) Flexible Budget Variance for Total Variable Costs 176 ade 3 Favorable/ mount Unfavorable 178 179