Project 3 Fall 2019 (version 1) - Auto Recovered - Excel o X Comments Formulas Data Review View Help Spencer Brooks - Share :27 0 Tell me what you want to do X Paste LE === > E PWrap Text Merge & Center - $ 5 General $ -% AutoSave om 9 : File Home Insert Page Layout Calibri .11 - A A BIU - A Clipboard Font N1 X fie A C 1 Accounting 202: 2 Project 3 - Operating Budget Insert Delete Format Conditional Format as Formatting - Table - Styles Cell Styles - Sort & Find & Filter - Select Alignment Number Cells Editing D E F G H I J K 40 points L M N O P Q 1 Name: Spencer Brooks 9196 6 Enter the last 4 digits of your student ID number: 7 Save this file in EXCEL FORMAT as: P3Lastname (These digils will be used to calculale Lwu variables) 8 Round all answers to whole numbers except cost per unit in Requirement 8 (round to nearest cent). 9 Do not change rows or columns on this spreadsheet. Input areas are in yellow. 10 You must use Excel formulas and cell references in the soreadsheets to receive full credit. 12 Requirement 1: 13 Perfect Pots, Inc, makes large, outdoor decorative flower pots that have the look of clay but are made from a polyresin 11 material that is much more durable. The controller for the company is working on the budget for the coming fiscal year, which 15 begins April 1. Sales in units for April are expected to be: A as calculated below) 16 = 900 17 A Multiply the highest digit from your student ID number above by 100 (if less than 5, use 5) 18 19 Sales are expected to increase by 10% each month through August. Each pot sells for $80. 20 21 Complete the Sales Budget for April - June and the quarter in total. Sales Budget Sheet1 April May June Total Ready # E E - + 949 O Type here to search De Egy. N . 11/18/2019 4 Spencer Brooks - o X Comments Share E .:47 0 Paste LE Cell Styles - Insert Delete Format Sort & Find & Filter - Select Cells Editing AutoSave om 9 : Project 3 Fall 2019 (version 1) - Auto Recovered - Excel File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Calibri .11 - A A === > PWrap Text General BIU - A E Merge & Center - $ -% Conditional Format as Formatting - Table - Clipboard Font Alignment F Number Styles N30 X fix A B C 8 Round all answers to whole numbers except cost per unit in Requirement 8 (round to nearest cent). 9 Do not change rows or columns on this spreadsheet. Input areas are in yellow. 10 You must use Excel formulas and cell references in the soreadsheets to receive full credit. 11 12 Requirement 1: 13 Perfect Pots, Inc. makes large, outdoor decorative flower pots that have the look of clay but are made from a polyresin 14 malerial thal is much more durable. The controller for the company is working on the budget for the coming fiscal year, which 15 begins April 1. Sales in units for April are expected to be: A las calculated below) 16 17 A. Multiply the highest digit from your student ID number above by 100 (if less than 5, use 5) = 900 M N O P Q A 18 19 Sales are expected lu increase by 10% each month through August. Each pol sells for $80. 20 21 Complete the Sales Budget for April - June and the quarter in total. 22 23 Sales Budget 24 Sales in unils 25 Price per unit 26 Total sales S $ April 900 80 S 72,000 $ May June 990 1,089 80S 80S 79,200 $ 87,120$ Total 2,979 240 714,960 27 28 Requirement 2: 29 The company wants to have B % (as calculated below) of the flower pots thal are expected to be sold 30 the following month on hand at the end of each month. There were 80 flower pots on hand at the end of March. Sheet1 Ready Screenshot saved The screenshot was added to your OneDrive. OneDrive Type here to search Ale 9 O P ] BAIN * El 1718/2019 4 Project 3 Fall 2019 (version 1) - Auto Recovered - Excel Spencer Brooks - o X Comments Formulas Data Review View Help Tell me what you want to do Share X Paste LE AutoSave om 9 : File Home Insert Page Layout Calibri .11 - A A BIU - A Clipboard Font === > E PWrap Text Merge & Center - E .:47 0 General $ -% Conditional Format as Formatting - Table - Styles Cell Styles - Insert Delete Format Sort & Find & Filter - Select Alignment Number Cells Editing N48 L M N O P Q 27 28 Requirement 2: 29 The company wants to have B % (as calculated below) of the flower pots that are expected to be sold 30 the following month on hand at the end of each month. There were 80 flower pots on hand at the end of March. 31 B. Multiply your lowest digit (other than 0) from your student ID number above by 10 and inserl in the in the box provided: 34 35 Complete the production Budget for April - June and the quarter in total. 36 June 37 Production Budget 38 Sales in unils 39 Desired ending inventory 10 Total required 41 Reginning inventory 42 Total units to produce April 900 99 999 80 919 May 990 109 1,099 1,089 120 1,209 109 1,100 Hint: You will need to calculate July's sales Total in units in order to calculate the desired ending 2,979 inventory for June. 328 3,307 288 3,019 1,000 44 Hint: The next three budgets are all manufacturing cost budgets, and use units to be produced, not sold. 45 46 Requirement 3: 4/ The standard quantity of polyresin in 4 lbs. per pot. The standard price for polyresin in $4 per Ib. 48 The company wants to always have 20% of the materials required for the following month 49 on hand at the end of each month. At the end of March, there was 200 pounds of polyresin on hand. (April's beginning inventory) Sheet1 O Ready # E E - + 949 O Type here to search 0 Le 1999 . 11/18/2019 4 Project 3 Fall 2019 (version 1) - Auto Recovered - Excel Spencer Brooks - o X Comments Formulas Data Review View Help Tell me what you want to do Share X Paste LE AutoSave om 9 : File Home Insert Page Layout Calibri .11 - A A BIU - A Clipboard Font === > E PWrap Text Merge & Center - E .:47 0 General $ -% Conditional Format as Formatting - Table - Styles Cell Styles - Insert Delete Format Sort & Find & Filter - Select Alignment Number Cells Editing N67 M N O P Q A 45 46 Requirement 3: 47 The standard quantity of polyresin in 4 lbs. per pot. The standard price for polyresin in $4 per lb. 18 The company wants to always have 20% of the materials required for the following month 49 on hand at the end of each month. At the end of March, there was 200 pounds of polyresin on hand. (April's beginning inventory) 51 Complele the direct materials purchases budget for April - June and the quarter in total. June 1,100 Hint: You will need to calculate units to be Total produced for July in order to calculate the 3,019 material needs for June. 919 1.000 54 Direct Materials Purchases Budget 55 Units to be produced 56 Pounds of DM per unit produced 57 Total needed for production 58 Desired ending inventory 59 Total material requirement 60 Beginning inventory 61 Total pounds to purchase 62 Cost per pound 63 Total cost of DM 3,676 800 4,476 200 4,276 4,000 880 4,880 800 4.080 4,000 968 5,368 880 4,488 4S 17,952 $ 12,076 2,618 14,724 1,880 12,844 12 $ 17,101 $ 16,320 $ 51,376 66 Requirement 4: 67 The standard labor rate is $18 per hour and the standard time for each pot is 1 hour Sheet1 Ready # E E -- + 949 P] R0. * E28 . 11/18/20194 O Type here to search De Egy. N Project 3 Fall 2019 (version 1) - Auto Recovered - Excel Spencer Brooks - o X Comments Formulas Data Review View Help Tell me what you want to do Share AutoSave om 9 : File Home Insert Page Layout Calibri .11 - A A BIU - B A Clipboard Font === > PWrap Text Merge & Center - E .:47 0 Paste LS General $ -% E - 10 Conditional Format as Formatting - Table - Styles Cell Styles - Insert Delete Format mat . Sort & Find & Filter - Select Alignment Number Cells Editing F100 L M N O P Q 79 Requirement 5: 80 Each flower pot requires $3 of indirect materials (variable MOH). Fixed manufacturing overhead consists of depreciation 81 of $1,000 per month, utilities of $600 per month, the suprevisor's salary of $4,000 per month, and other fixed costs of $1,500 82 per month. 83 94 Complete the Manufacturing Overhad Budget below: 85 86 Manufacturing Overhead Budget April May June Total 87 Variable Overhead 88 Units to be produced 89 Variable overhead per unit 90 Total variable overhead 91 Fixed Overhead 92 Depreciation 93 Supervisor's salary 94 Factory utilities 95 Other 96 Total fixed overhead 97 Total Manufacturing Overhead 98 99 Requirement 6: 100 Selling costs are $2.00 per flower pot. Administrative costs are all fiked and consist of monthly depreciation of $500, billing and Sheet1 Ready # EE - + 949 Type here to search * 11/18/20194 Spencer Brooks - o X Comments Share E .:47 0 Paste LE- B Cell Styles - Insert Delete Format Sort & Find & Filter - Select Cells Editing L M N O P Q AutoSave om 9 : Project 3 Fall 2019 (version 1) - Auto Recovered - Excel File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Calibri .11 - A A === > PWrap Text General BIU - A E Merge & Center - $ -% Conditional Format as Formatting - Table - Clipboard Font Alignment Number Styles F134 X fi A B C D E F G H I 113 Other administrative costs 114 Total fixed administrative 115 Total Operating Expenses 116 117 118 Requirement 7: 119 Calculate manufacturing cost per unit at your projected volume of production: 120 121 DM Cusl per unit 122 DL Cost per unit 123 Variable MOH per unit 124 Total Fixed MOH 125 / Units to be produced 126 Fixed MOH per unil 127 Total Manufacturing Cost per Unit Used to determine COGS below 128 129 130 131 Requirement 8: 132 Prepare the budgeted income statement for the quarter ending June 30 below: 133 134 135 Perfect Pots, Inc. Sheet1 6 Ready Type here to search 2 e # EE - + 949 09+ . 11/18/20194 Project 3 Fall 2019 (version 1) - Auto Recovered - Excel Spencer Brooks - o X Comments Formulas Data Review View Help Tell me what you want to do Share AutoSave om 9 : File Home Insert Page Layout Calibri .11 - A A BIU - A Clipboard Font === > E PWrap Text Merge & Center - E .:47 0 Paste LE General $ -% Conditional Format as Formatting - Table - Styles Cell Styles - Insert Delete Format Sort & Find & Filter - Select Alignment Number Cells Editing L M N O P Q F151 A B C D E 130 131 Requirement 8: 132 Prepare the budgeted income statement for the quarter ending June 30 below: 133 134 135 Perfect Pots, Inc. 136 Budgeled Income Slalement 137 Quarter Ending June 30 138 139 Sales 140 141 Cost ol Gulds Sold Hint: COGS is based on units sold, not produced. 142 Use the manufacturing cost per unit calculated 143 Gross Margin above and the units to be sold. 144 145 Operating Expenses 146 Selling 147 Administrative 148 Total operating expenses 119 150 Operating Income 151 152 Sheet1 Ready # EE - Type here to search 2 e 11/18/20194 Project 3 Fall 2019 (version 1) - Auto Recovered - Excel o X Comments Formulas Data Review View Help Spencer Brooks - Share :27 0 Tell me what you want to do X Paste LE === > E PWrap Text Merge & Center - $ 5 General $ -% AutoSave om 9 : File Home Insert Page Layout Calibri .11 - A A BIU - A Clipboard Font N1 X fie A C 1 Accounting 202: 2 Project 3 - Operating Budget Insert Delete Format Conditional Format as Formatting - Table - Styles Cell Styles - Sort & Find & Filter - Select Alignment Number Cells Editing D E F G H I J K 40 points L M N O P Q 1 Name: Spencer Brooks 9196 6 Enter the last 4 digits of your student ID number: 7 Save this file in EXCEL FORMAT as: P3Lastname (These digils will be used to calculale Lwu variables) 8 Round all answers to whole numbers except cost per unit in Requirement 8 (round to nearest cent). 9 Do not change rows or columns on this spreadsheet. Input areas are in yellow. 10 You must use Excel formulas and cell references in the soreadsheets to receive full credit. 12 Requirement 1: 13 Perfect Pots, Inc, makes large, outdoor decorative flower pots that have the look of clay but are made from a polyresin 11 material that is much more durable. The controller for the company is working on the budget for the coming fiscal year, which 15 begins April 1. Sales in units for April are expected to be: A as calculated below) 16 = 900 17 A Multiply the highest digit from your student ID number above by 100 (if less than 5, use 5) 18 19 Sales are expected to increase by 10% each month through August. Each pot sells for $80. 20 21 Complete the Sales Budget for April - June and the quarter in total. Sales Budget Sheet1 April May June Total Ready # E E - + 949 O Type here to search De Egy. N . 11/18/2019 4 Spencer Brooks - o X Comments Share E .:47 0 Paste LE Cell Styles - Insert Delete Format Sort & Find & Filter - Select Cells Editing AutoSave om 9 : Project 3 Fall 2019 (version 1) - Auto Recovered - Excel File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Calibri .11 - A A === > PWrap Text General BIU - A E Merge & Center - $ -% Conditional Format as Formatting - Table - Clipboard Font Alignment F Number Styles N30 X fix A B C 8 Round all answers to whole numbers except cost per unit in Requirement 8 (round to nearest cent). 9 Do not change rows or columns on this spreadsheet. Input areas are in yellow. 10 You must use Excel formulas and cell references in the soreadsheets to receive full credit. 11 12 Requirement 1: 13 Perfect Pots, Inc. makes large, outdoor decorative flower pots that have the look of clay but are made from a polyresin 14 malerial thal is much more durable. The controller for the company is working on the budget for the coming fiscal year, which 15 begins April 1. Sales in units for April are expected to be: A las calculated below) 16 17 A. Multiply the highest digit from your student ID number above by 100 (if less than 5, use 5) = 900 M N O P Q A 18 19 Sales are expected lu increase by 10% each month through August. Each pol sells for $80. 20 21 Complete the Sales Budget for April - June and the quarter in total. 22 23 Sales Budget 24 Sales in unils 25 Price per unit 26 Total sales S $ April 900 80 S 72,000 $ May June 990 1,089 80S 80S 79,200 $ 87,120$ Total 2,979 240 714,960 27 28 Requirement 2: 29 The company wants to have B % (as calculated below) of the flower pots thal are expected to be sold 30 the following month on hand at the end of each month. There were 80 flower pots on hand at the end of March. Sheet1 Ready Screenshot saved The screenshot was added to your OneDrive. OneDrive Type here to search Ale 9 O P ] BAIN * El 1718/2019 4 Project 3 Fall 2019 (version 1) - Auto Recovered - Excel Spencer Brooks - o X Comments Formulas Data Review View Help Tell me what you want to do Share X Paste LE AutoSave om 9 : File Home Insert Page Layout Calibri .11 - A A BIU - A Clipboard Font === > E PWrap Text Merge & Center - E .:47 0 General $ -% Conditional Format as Formatting - Table - Styles Cell Styles - Insert Delete Format Sort & Find & Filter - Select Alignment Number Cells Editing N48 L M N O P Q 27 28 Requirement 2: 29 The company wants to have B % (as calculated below) of the flower pots that are expected to be sold 30 the following month on hand at the end of each month. There were 80 flower pots on hand at the end of March. 31 B. Multiply your lowest digit (other than 0) from your student ID number above by 10 and inserl in the in the box provided: 34 35 Complete the production Budget for April - June and the quarter in total. 36 June 37 Production Budget 38 Sales in unils 39 Desired ending inventory 10 Total required 41 Reginning inventory 42 Total units to produce April 900 99 999 80 919 May 990 109 1,099 1,089 120 1,209 109 1,100 Hint: You will need to calculate July's sales Total in units in order to calculate the desired ending 2,979 inventory for June. 328 3,307 288 3,019 1,000 44 Hint: The next three budgets are all manufacturing cost budgets, and use units to be produced, not sold. 45 46 Requirement 3: 4/ The standard quantity of polyresin in 4 lbs. per pot. The standard price for polyresin in $4 per Ib. 48 The company wants to always have 20% of the materials required for the following month 49 on hand at the end of each month. At the end of March, there was 200 pounds of polyresin on hand. (April's beginning inventory) Sheet1 O Ready # E E - + 949 O Type here to search 0 Le 1999 . 11/18/2019 4 Project 3 Fall 2019 (version 1) - Auto Recovered - Excel Spencer Brooks - o X Comments Formulas Data Review View Help Tell me what you want to do Share X Paste LE AutoSave om 9 : File Home Insert Page Layout Calibri .11 - A A BIU - A Clipboard Font === > E PWrap Text Merge & Center - E .:47 0 General $ -% Conditional Format as Formatting - Table - Styles Cell Styles - Insert Delete Format Sort & Find & Filter - Select Alignment Number Cells Editing N67 M N O P Q A 45 46 Requirement 3: 47 The standard quantity of polyresin in 4 lbs. per pot. The standard price for polyresin in $4 per lb. 18 The company wants to always have 20% of the materials required for the following month 49 on hand at the end of each month. At the end of March, there was 200 pounds of polyresin on hand. (April's beginning inventory) 51 Complele the direct materials purchases budget for April - June and the quarter in total. June 1,100 Hint: You will need to calculate units to be Total produced for July in order to calculate the 3,019 material needs for June. 919 1.000 54 Direct Materials Purchases Budget 55 Units to be produced 56 Pounds of DM per unit produced 57 Total needed for production 58 Desired ending inventory 59 Total material requirement 60 Beginning inventory 61 Total pounds to purchase 62 Cost per pound 63 Total cost of DM 3,676 800 4,476 200 4,276 4,000 880 4,880 800 4.080 4,000 968 5,368 880 4,488 4S 17,952 $ 12,076 2,618 14,724 1,880 12,844 12 $ 17,101 $ 16,320 $ 51,376 66 Requirement 4: 67 The standard labor rate is $18 per hour and the standard time for each pot is 1 hour Sheet1 Ready # E E -- + 949 P] R0. * E28 . 11/18/20194 O Type here to search De Egy. N Project 3 Fall 2019 (version 1) - Auto Recovered - Excel Spencer Brooks - o X Comments Formulas Data Review View Help Tell me what you want to do Share AutoSave om 9 : File Home Insert Page Layout Calibri .11 - A A BIU - B A Clipboard Font === > PWrap Text Merge & Center - E .:47 0 Paste LS General $ -% E - 10 Conditional Format as Formatting - Table - Styles Cell Styles - Insert Delete Format mat . Sort & Find & Filter - Select Alignment Number Cells Editing F100 L M N O P Q 79 Requirement 5: 80 Each flower pot requires $3 of indirect materials (variable MOH). Fixed manufacturing overhead consists of depreciation 81 of $1,000 per month, utilities of $600 per month, the suprevisor's salary of $4,000 per month, and other fixed costs of $1,500 82 per month. 83 94 Complete the Manufacturing Overhad Budget below: 85 86 Manufacturing Overhead Budget April May June Total 87 Variable Overhead 88 Units to be produced 89 Variable overhead per unit 90 Total variable overhead 91 Fixed Overhead 92 Depreciation 93 Supervisor's salary 94 Factory utilities 95 Other 96 Total fixed overhead 97 Total Manufacturing Overhead 98 99 Requirement 6: 100 Selling costs are $2.00 per flower pot. Administrative costs are all fiked and consist of monthly depreciation of $500, billing and Sheet1 Ready # EE - + 949 Type here to search * 11/18/20194 Spencer Brooks - o X Comments Share E .:47 0 Paste LE- B Cell Styles - Insert Delete Format Sort & Find & Filter - Select Cells Editing L M N O P Q AutoSave om 9 : Project 3 Fall 2019 (version 1) - Auto Recovered - Excel File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Calibri .11 - A A === > PWrap Text General BIU - A E Merge & Center - $ -% Conditional Format as Formatting - Table - Clipboard Font Alignment Number Styles F134 X fi A B C D E F G H I 113 Other administrative costs 114 Total fixed administrative 115 Total Operating Expenses 116 117 118 Requirement 7: 119 Calculate manufacturing cost per unit at your projected volume of production: 120 121 DM Cusl per unit 122 DL Cost per unit 123 Variable MOH per unit 124 Total Fixed MOH 125 / Units to be produced 126 Fixed MOH per unil 127 Total Manufacturing Cost per Unit Used to determine COGS below 128 129 130 131 Requirement 8: 132 Prepare the budgeted income statement for the quarter ending June 30 below: 133 134 135 Perfect Pots, Inc. Sheet1 6 Ready Type here to search 2 e # EE - + 949 09+ . 11/18/20194 Project 3 Fall 2019 (version 1) - Auto Recovered - Excel Spencer Brooks - o X Comments Formulas Data Review View Help Tell me what you want to do Share AutoSave om 9 : File Home Insert Page Layout Calibri .11 - A A BIU - A Clipboard Font === > E PWrap Text Merge & Center - E .:47 0 Paste LE General $ -% Conditional Format as Formatting - Table - Styles Cell Styles - Insert Delete Format Sort & Find & Filter - Select Alignment Number Cells Editing L M N O P Q F151 A B C D E 130 131 Requirement 8: 132 Prepare the budgeted income statement for the quarter ending June 30 below: 133 134 135 Perfect Pots, Inc. 136 Budgeled Income Slalement 137 Quarter Ending June 30 138 139 Sales 140 141 Cost ol Gulds Sold Hint: COGS is based on units sold, not produced. 142 Use the manufacturing cost per unit calculated 143 Gross Margin above and the units to be sold. 144 145 Operating Expenses 146 Selling 147 Administrative 148 Total operating expenses 119 150 Operating Income 151 152 Sheet1 Ready # EE - Type here to search 2 e 11/18/20194