Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do = = ). General Calibri B I 11 . Font - A A . .A. Wrap Text Merge & Center. U $ . % * 3 Alignment Number Clipboard R14 4. A . B C D G H nter the last 4 digits of your student ID number 4 Name/s: 7 Save this file in EXCEL FORMAT as: P2 Lastname 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 o 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: 18 Multiply the highest digit from your student ID number shove by 100 and enter in the box below 19 Sales in units for April are expected to be: Page 1 21 Sales are expected to increase by 10% each month through August Bach ocells for 150 22 Complete the Sales Budget for April - June and the quarter in total. 23 Sales Budget April 25 Sales in units 900 990 1,089 2.979 26 Price per unit $ 150 $ 150 $ 150 $ 150 27 Total sales dollars S 135,000 $ 148,500 $ 163,350 $ 446,850 July 1.197.90 150.00 179,685 $ $ august 1,317.69 150.00 197,654 29 Requirment 2: 31 Multiply your lowest digit (other than o) from your student ID number above by 10 and insert for the % below: 33 The company wants to have (per above) of the flower pots that are expected to be sold 34 the following month on hand at the end of each month. There were 80 flower pots on hand at the end of March 36 Complete the Production Budget for April - June and the quarter in total. Total 2.979 38 Production Budget 39 Sales in units 40 Desired ending inventory 41 Total required 42 Beginning inventory Sheet1 April 900 1 98 1,098 O Hint: You will need to calculate July's sales in units in order to calculate the desired ending inventory for June. July 1198 263.54 1.461.44 990 218 1,208 198 240 File Home insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Calibri 1 = = = 2 Wrap Text General A A .0.A. B V 2 Merge & Center - $ . % 53 Clipboard Font Agent 29 Requirment 2: 31 Multiply your lowest digit (other than 0) from your student ID number above by 10 and insert for the below: 20 33 The company wants to have (per above) of the flower pots that are expected to be sold 34 the following month on hand at the end of each month. There were BO flower pots on hand at the end of March 35 36 Complete the Production Budget for April-June and the quarter in total Hint: You will need to calculate July's sales in units in order to calculate the desired ending 38 Production Budget April May June Total inventory for June. July 39 Sales in units 9009901.0892 .979 1198 40 Desired ending inventory 218 240 253.54 41 Total required 1.098 1,208 1.329 3219 1.461.44 42 Beginning inventory 198 240 43 Total units to produce 1.018 1,010 1.222 198 45 Requirement 3: 17 Each flower pot requires 4 lbs. of polyresin. The company wants to always have 20% of the materials required for the following month 8 on hand at the end of each month. At the end of March, there was 200 pounds of polyresin on hand. Polyresin sells for 9 55 per pound Hint: The next three budgets are all manufacturing cost budgets, and use units to be produced, not sold. 3 Complete the direct materials purchases budget for April - June and the quarter in total. Hint: You will need to calculate units to be 5 Direct Materials Purchases Budget April May June Total produced for July in order to calculate the 6 Units to be produced 1,018 1,010 1.111 3,139 material needs for June. July 7 Pounds of DM per unit produced 1,222 Total needed for production 4,072 4,039 4,443 12.554 Desired ending inventory 808 889 977 977 4,887.43 Total material requirement 4,880 4,928 5,421 13,532 808 200 Beginning inventory 200 Total pounds to purchase 4.680 4,120 4532 13,332 S5 $ Cost per pound 5 $ 5 S 23.399 Total cost of DM $ 20.600 $ 22.660 $ 66.659 Sheet1 ady Clipboard R14 69 Requirement 4: 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 Page 2 April 74 Direct Labor Budget 75 Units to be produced 76 DL hours per unit 77 Total DL hours 78 DL rate per hour 79 Total cost of DL 1.0 1,018 1.0 3.139 1.010 22 22.396 22 $ $ 1.111 22 $ 24,437 $ 69,049 81 Requirement 5: 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. BB Complete the Manufacturing Overhead Budget below: Total 1 ,111 3,139 3S 3,332 $ 9,416 1,000 $ 4,000 90 Manufacturing Overhead Budget April May 91 Variable Overhead 92 Units to be produced 1,018 1,010 93 Variable overhead per unit $ 3$ 3$ 94 Total variable overhead $ 3,054 $ 3,029 $ 95 Fixed Overhead 96 Depreciation 1,000 $ 1,000 $ 97 Supervisor's salary 4.000 4.000 98 Factory utilities 1,200 1,200 99 Other 2,000 2,000 100 Total fixed overhead $ 8,200 $ 8,200 $ 101 Total Manufacturing Overhead $ 11,254 $ 11,229 $ 102 103 Requirement 6: 104 Selling expenses are $2.00 per flower pot. Administrative costs are all fix 105 accounting costs of $ 2,000 and other administrative costs of $800. 106 sheets of 1,200 2,000 8,200 $ 11,532 $ 1,000 4,000 1,200 2,000 8,200 17,616 consist of monthly depreciation of $500, biling and Ready Font Alignment Clipboard B130 Nurnber 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 April May June Total Hint: Use units to be sold, not produced. 110 Variable Selling Expenses 111 Units to be sold 900 990 1,089 2,979 112 Selling expenses per unit sold $ 2 % 2 % 2 % 113 Total variable selling expenses $ 1,800 $ 1,980 $ 2,178 $ 5,958 114 Fixed Administrative Expenses 115 Depreciation $ 500 $ 500 S 500 S 500 116 Billing and accounting 2,000 2,000 2,000 2,000 117) Other administrative costs 800 800 800 118 Total fixed administrative $ 3,300 $ 3,300 $ 3,300 $ 3,300 119 Total Operating Expenses $ 5,100 $ 5,280 $ 5,478 $ 9,258 120 800 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 Perfect Pots, Inc. Contribution Income Statement 138 Quarter Ending June 30 139 140 Sales 137 Page 3 141 Sheet1 ) Alignment Number G143 . E 130 131 132 Requirement 7. using variable costing and the contribution 134 Prepare the budgeted income statement for the quarter ending June 30 below 135 margin format. 136 Perfect Pots, Inc. 137 Contribution Income Statement 138 Quarter Ending June 30 Total Per Unit 140 Sales $ 446,850 $ 139 141 $ 142 Variable Costs: 143 Direct Materials 144 Direct labor 145 Variable Overhead 146 Variable Selling Expense 147 Total Variable Costs 66,659 69,049 9,416 5,958 151,082 148 149 Contribution Margin 150 151 Fixed Costs: 152 Manufacturing Overhead 153 Administrative Expenses 154 Total Fixed Costs 8,200 3,300 11,500 155 156 Operating Income $ 284,268 157 158 159 Requirement 8. (Formulas encouraged, but not required for this requirement) 160 161 Suppose actual sales for the quarter totaled 1,900 units and $279,800 Total variable 162 were $79,800. Calculate the following: Page 3 164 Actual selling price per unit 165 166 Actual variable cost per unit sold 167 Favorable/ Amount Unfavorable 168 691) Activity Variance for Sales Revenue Sheet1 Ready Clipboard - Merge & Center Font 143 - X Alignment fi -6 Variable Selling Expense -7 Total Variable Costs $ 5,958 151,082 39 Contribution Margin 51 Fixed Costs: 52 Manufacturing Overhead 53 Administrative Expenses 54 Total Fixed Costs 8,200 3,300 11,500 56 Operating Income $ 284,268 58 59 Requirement 8. (Formulas encouraged, but not required for this requirement) 61 Suppose actual sales for the quarter totaled 1,900 units and $279,300 Total variable costs 162 were $79,800. Calculate the following: - Page 3 164 Actual selling price per unit 165 166 Actual variable cost per unit sold 767 768 Favorable/ Amount Unfavorable 1691) Activity Variance for Sales Revenue 170 1712) Revenue Variance for Sales Revenue 172 1733) Activity Variance for Total Variable Costs 174 1754) Spending Variance for Total Variable Costs 176