a budgetprob-spreadsheet-S22.xls [Compatibility Mode] SP Home Insert Page Layout Formulas Data Review View Share Arial v 10 9 ab Wras Text General SR . V V v v AP A Fill Peste BIU Merge & Center $ -% Insert Delete Format Conditional Format Cell Formatting as Table Styles Clear v Sort & Filter Find & Select Activate B9 Subscription Required to Edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription. x fx A B D E F F G G H . J K L M N O Scholastic Furniture, Inc. makes a table and chair set that is sold to public school systems throughout the Midwest. The 'Data" spreadsheet gives you the information regarding sales forecasts, materials cost and requirements, direct labor costs and requirements and variable and fixed manufacturing overhead and selling expenses. Use this information in completing the budgets on the Data spreadsheet 3 4 5 Additional Information: All spreadsheet entries should be a formula or cell location. All formulas should reference cell locations only. Do not enter any numeric data. Your spreadsheet will be graded by changing information given on the data spreadsheet to determine if your budgets are correctly updated. The month of July has been completed for the sales budget to serve as an example. B 7 The general format for the budgets has been developed for you. However, the use of underlines and double underlines similar to that used in the text (pages 380-388) should be followed for each budget. Specifically, underlines should be used before all sub-totals and totals and double underlines should be used in cells with totals. The underlines and double underlines should extend across the entire cell (use accounting format in cells). Use dollar signs in total cells of each budget when appropriate. Use a color (blue) to highlight the amount (total) in the last line of each budget the double underline in this cell should also be in color. Do not use color anywhere else. In some cases Inappropriate highlights or formats may be added. You should correct these when they appear. B 9 You should complete budgets for the months of July, August, and September in their entirety. In order to use cell locations or formulas only. you may find a need to complete the production budget for October and a portion of the production budget for November. You may also need to complete a portion of the material purchases budget for the month of October 10 11 Dala Instructions Sheels + Ready ### + 120% a budgetprob-spreadsheet-S22.xls [Compatibility Mode] Home Insert Page Layout Formulas Data Review View Share X Arial v 10 v AP A 9 ab Wras Text Accounting v . SR V v Fill Peste BIU B - A Merge & Center $ - % Insert Delete Format Conditional Format Cell Formatting as Table Styles Cicar v Sort & Filter Find & Select Activate Subscription Required to edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription B35 x fx =B33*834 A B C D D E F H ! ! J K M N 0 P $325.00 12,000 10,000 9,500 8,500 7.000 32 $3.50 4 $25.00 $4.00 $350,000 ****** 3 SCHOLASTIC FURNITURE, INC. 4 5 SALES DATA 8 Budgeted Selling Price per set 7. 8 July sales forecast (in sets) 9 August sales forecast (in sets) 10 September sales forecast (in sets) 11 October sales forecast in sets) 12 November sales forecast (in sets) 13 14 BUDGETED PRODUCTION COSTS PER UNIT (except FMOH) 15 Raw material used per set (of board forel) 16 Raw material cost per board foot 17 Direct labor hours per set 16 Direct latcr rate 19 Variable manufacturing overhead per DLH 20 Fixed manufacturing overhead per month 21 22 BUDGETED INVENTORY LEVELS 23 Ending material inventory (% of next months production needs) 24 Ending Finished Goods Inventory (% next months sales) 25 26 BUDGETED SELLING AND ADMINISTRATIVE COSTS 27 Variable willing expense persel 28 Fixed selling expense per month 29 30 31 32 33 Sales in sets) 34 Sales price per set 35 Sales revenue 38 37 38 Sales 39 Add: Desired ending inventory 40 Total requirements 41 Less: Projected beginning inventory 42 Planned production in sels 43 Dala Instructions Sheels + Ready 40% 80% $18.00 $200,0DD - AUGUST SEPTEMBER OCTOBER NOVEMBER Sales Budget JULY 12,000 $ 325.DD $ 3,900,000 1 Production Budget 117% a budgetprob-spreadsheet-S22.xls [Compatibility Mode] Home Insert Page Layout Formulas Data Review View Share X Arial v 10 v AP A 92 ab Wran Text Accounting v SR V V Fill Peste BIU B - YA Merge & Center $ % Insert Delete Format Conditional Format Cell Formatting as Table Styles Clear v Sort & Filter Find & Select Activate G H ! ! J K M N 0 P Subscription Required to Edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription. B35 + x fx =B33*834 A B C D D F F 42 Planned produchon in sets 43 44 Direct Materials Budget 45 Planned Production (sets) 48 Raw Material required persel (board feel) 47 Raw Material required for production (board feet) 48 Add: Desired ending inventory of Raw Material 49 Total Requirements 50 Less: Budgend beginning inventory of raw material 51 Planned Purchases of Raw Material (board feet) 52 Cost per board foot 53 Planned Purchases of Raw Material (dollars) 54 55 Direct Labor Budget 56 Planned Production (sets) 57 Direct Labor hours per set 58 Total Direct Labor hours required 59 Latorrale per hour 80 Budgeted Direct Labor Cost 61 62 Manufacturing Overhead Budget 63 Variable Manufacturing overhead 84 Fixed Manufacturing overhead 65 Total Budgeted Manufacturing Overhead 66 87 Selling and Administrative Budget 68 Variable Selling Expense 69 Fixed Selling Expense 70 Total Budgeted Selling Expenso 71 72 Income Statement - Contribution Margin Format 73 July August 74 75 78 77 78 79 80 81 R? Dala Instructions Sheels + Ready 117% a budgetprob-spreadsheet-S22.xls [Compatibility Mode] SP Home Insert Page Layout Formulas Data Review View Share Arial v 10 9 ab Wras Text General SR . V V v v AP A Fill Peste BIU Merge & Center $ -% Insert Delete Format Conditional Format Cell Formatting as Table Styles Clear v Sort & Filter Find & Select Activate B9 Subscription Required to Edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription. x fx A B D E F F G G H . J K L M N O Scholastic Furniture, Inc. makes a table and chair set that is sold to public school systems throughout the Midwest. The 'Data" spreadsheet gives you the information regarding sales forecasts, materials cost and requirements, direct labor costs and requirements and variable and fixed manufacturing overhead and selling expenses. Use this information in completing the budgets on the Data spreadsheet 3 4 5 Additional Information: All spreadsheet entries should be a formula or cell location. All formulas should reference cell locations only. Do not enter any numeric data. Your spreadsheet will be graded by changing information given on the data spreadsheet to determine if your budgets are correctly updated. The month of July has been completed for the sales budget to serve as an example. B 7 The general format for the budgets has been developed for you. However, the use of underlines and double underlines similar to that used in the text (pages 380-388) should be followed for each budget. Specifically, underlines should be used before all sub-totals and totals and double underlines should be used in cells with totals. The underlines and double underlines should extend across the entire cell (use accounting format in cells). Use dollar signs in total cells of each budget when appropriate. Use a color (blue) to highlight the amount (total) in the last line of each budget the double underline in this cell should also be in color. Do not use color anywhere else. In some cases Inappropriate highlights or formats may be added. You should correct these when they appear. B 9 You should complete budgets for the months of July, August, and September in their entirety. In order to use cell locations or formulas only. you may find a need to complete the production budget for October and a portion of the production budget for November. You may also need to complete a portion of the material purchases budget for the month of October 10 11 Dala Instructions Sheels + Ready ### + 120% a budgetprob-spreadsheet-S22.xls [Compatibility Mode] Home Insert Page Layout Formulas Data Review View Share X Arial v 10 v AP A 9 ab Wras Text Accounting v . SR V v Fill Peste BIU B - A Merge & Center $ - % Insert Delete Format Conditional Format Cell Formatting as Table Styles Cicar v Sort & Filter Find & Select Activate Subscription Required to edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription B35 x fx =B33*834 A B C D D E F H ! ! J K M N 0 P $325.00 12,000 10,000 9,500 8,500 7.000 32 $3.50 4 $25.00 $4.00 $350,000 ****** 3 SCHOLASTIC FURNITURE, INC. 4 5 SALES DATA 8 Budgeted Selling Price per set 7. 8 July sales forecast (in sets) 9 August sales forecast (in sets) 10 September sales forecast (in sets) 11 October sales forecast in sets) 12 November sales forecast (in sets) 13 14 BUDGETED PRODUCTION COSTS PER UNIT (except FMOH) 15 Raw material used per set (of board forel) 16 Raw material cost per board foot 17 Direct labor hours per set 16 Direct latcr rate 19 Variable manufacturing overhead per DLH 20 Fixed manufacturing overhead per month 21 22 BUDGETED INVENTORY LEVELS 23 Ending material inventory (% of next months production needs) 24 Ending Finished Goods Inventory (% next months sales) 25 26 BUDGETED SELLING AND ADMINISTRATIVE COSTS 27 Variable willing expense persel 28 Fixed selling expense per month 29 30 31 32 33 Sales in sets) 34 Sales price per set 35 Sales revenue 38 37 38 Sales 39 Add: Desired ending inventory 40 Total requirements 41 Less: Projected beginning inventory 42 Planned production in sels 43 Dala Instructions Sheels + Ready 40% 80% $18.00 $200,0DD - AUGUST SEPTEMBER OCTOBER NOVEMBER Sales Budget JULY 12,000 $ 325.DD $ 3,900,000 1 Production Budget 117% a budgetprob-spreadsheet-S22.xls [Compatibility Mode] Home Insert Page Layout Formulas Data Review View Share X Arial v 10 v AP A 92 ab Wran Text Accounting v SR V V Fill Peste BIU B - YA Merge & Center $ % Insert Delete Format Conditional Format Cell Formatting as Table Styles Clear v Sort & Filter Find & Select Activate G H ! ! J K M N 0 P Subscription Required to Edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription. B35 + x fx =B33*834 A B C D D F F 42 Planned produchon in sets 43 44 Direct Materials Budget 45 Planned Production (sets) 48 Raw Material required persel (board feel) 47 Raw Material required for production (board feet) 48 Add: Desired ending inventory of Raw Material 49 Total Requirements 50 Less: Budgend beginning inventory of raw material 51 Planned Purchases of Raw Material (board feet) 52 Cost per board foot 53 Planned Purchases of Raw Material (dollars) 54 55 Direct Labor Budget 56 Planned Production (sets) 57 Direct Labor hours per set 58 Total Direct Labor hours required 59 Latorrale per hour 80 Budgeted Direct Labor Cost 61 62 Manufacturing Overhead Budget 63 Variable Manufacturing overhead 84 Fixed Manufacturing overhead 65 Total Budgeted Manufacturing Overhead 66 87 Selling and Administrative Budget 68 Variable Selling Expense 69 Fixed Selling Expense 70 Total Budgeted Selling Expenso 71 72 Income Statement - Contribution Margin Format 73 July August 74 75 78 77 78 79 80 81 R? Dala Instructions Sheels + Ready 117%