how to fill out materials req. budget and beyond

Data Window Help OD 100% 62 Wed 2:17 PM Q E Excel File File Edit View Insert Format Tools . AutoSave OF OF SU Home Insert Draw Page Layout Formulas Data ACC202- Excel Budgeting Project Master MWF (1) View Tell me Review Your disk is almost full Save space by optimizing storage. Close 1 Manage... TO Arial v 10 * ' ab Wrap Text X LG General PL/ ST LESHA Delete Format Paste BIU v A Merge & Center Insert S% Y Conditional Format Cell Formatting as Table Styles Sort Filter Find & Select Analyze Data Yes No F I JK L M MN a RST U V W Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left aft? C2 fx . D E F G H I ACC 202 BUDGETING PROJECT 2 STUDENT NAME: 3 Sales Projections in Units January 17,568 February 33,739 March 31,361 7 April 51,765 May 64,972 4 5 10 Projected Sales Price/Unit $ 146.00 11 12 Monthly Projected Selling & Administrative Expenses 13 Variable Cost/Unit S1.00 14 Fixed Costs $5,468 15 16 Production: 17 Desired Ending Inventory 57.4% 18 Beginning Inventory (new business) 0 19 20 Materials 21 Desired Ending Inventory 1.2% 22 Number of Materials per Unit 13.0 -- Projected Cost Material Unit $23.00 $ 24 Beginning Inventory (new business) 0 26 26 Direct Labor 27 Time per Unit (in hours) 2.00 Data Sheet Sales Budget Operating Budget | Production Budget Ready 22 Materials Rex. Buciget Direct Labor Budget Manu OH Budget Product Cost per Unit Projected Income Statement + + 100% CIH 27 A O 21 Data Window Help O 100% 62 Wed 2:18 PM E Excel File File Edit View Insert Format Tools . AutoSave OF OF SU Home Insert Draw Page Layout Formulas Data ACC202- Excel Budgeting Project Master MWF (1) View Tell me Review Your disk is almost full Save space by optimizing storage. Close 1 Manage... TO Arial v 10 * ' ab Wrap Text X LG General PL/ ST LEH Paste BIU. v A Merge & Center Insert S% Y Delete Format Conditional Format Cell Formatting as Table Styles Sort & Filter Find & Select Analyze Data Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left aft? Yes No C2 D TE F I JK L M MN a R S T T U V W W 0 1.2% 13.0 B Woo Logomory 18 Beginning Inventory (new business) 18 20 Materials 21 Desired Ending Inventory 22 Number of Materials per Unit 22 Projected Cost/Material Unit 24 Beginning Inventory (new business) 20 26 Direct Labor 27 Time per Unit (in hours) 28 Cost per Hour 29 30 Manufacturing Overhead 31 Variable Cost/Unit $23.00 0 0 2.00 $23.00 $5.00 Fixed Costs $7,024 92 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 . Data Sheet Sales Budget Operating Budget Production Budget Materials Rex. Buciget Direct Labor Budget Manu OH Budget Product Cost per Unit Projected Income D Ready Tune CIH 27 A O la Data Window Help O 100% 62 Wed 2:18 PM E Excel File File Edit View Insert Format Tools . AutoSave OF OF A SU Home Insert Draw Page Layout Formulas Data ACC202- Excel Budgeting Project Master MWF (1) View Tell me Review Your disk is almost full Save space by optimizing storage. Close 1 Manage... TO Arial v 14 ~ A A Ov ab Wrap Text X LG Accounting ST EXHA Delete Format Paste BIU. v A Merge & Center S% Insert Find & Conditional Format Cell Formatting as Table Styles Sort Filter Select Analyze Data Yes No Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left aft? x fx =B4*B5 B6 B E F G H . L M N a R S 2 SALES BUDGET January February March April Projected Sales in Units 17.568.00 33,739.00 31,361.00 51,765.00 Selling Price por Unit 146.00 146.00 146,00 146.00 5 Anticipated Sales Dollars 2,564,928.00 4,925,894.00 4,578,706.00 7,557,690.00 10 11 12 13 14 16 16 17 18 19 20 Data Sheet Sales Budget Operating Budget Production Budget Materials Rex. Buciget Direct Labor Budget Manu OH Budget Product Cost per Unit Projected Income Statement + Ready + 100% GUIR 27 A O 21 Data Window Help OD 100% (42) 62 Wed 2:18 PM E Excel File File Edit View Insert Format Tools . AutoSave OF OF A SU Home Insert Draw Page Layout Formulas Data ACC202- Excel Budgeting Project Master MWF (1) View Tell me Review Your disk is almost full Save space by optimizing storage. Close 1 Manage... TO Arial v 16 X [G ~ A A ab Wrap Text General E B Paste v A BIU Merge & Center S% Insert Delete Y Format Conditional Format Cell Formatting as Table Styles Sort Filter Find & Select Analyze Data Yes No Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left aft? Dii fx A E F H I J K L K M N 0 P Q RS 5 SELLING & ADMINISTRATIVE EXPENSE BUDGET January February March Sale Units 17,568.00 33,739.00 31,361.00 Variable S&A Rate/Unit $1.00 $1.00 $1.00 E Anticipated Variable Costs 8 Fixed S&A Costs Total Anticipated Selling & 10 Administrative Costs $ $17,568.00 33,739.00 31,361.00 $5,468 $5.468 $5.468 23,036.00 39,207.00 36,829.00 11 12 13 14 16 16 17 18 19 -- 20 Data Sheet Sales Budget Operating Budget Production Budget Materials Req. Budget Direct Labor Budget Manu OH Budget Product Cost per Unit Projected Income ED Ready TU CIH 27 A O la Data Window Help O 100% 62 Wed 2:18 PM E Excel File File Edit View Insert Format Tools . AutoSave OF OF A SU Home Insert Draw Page Layout Formulas Data ACC202- Excel Budgeting Project Master MWF (1) View Tell me Review Your disk is almost full Save space by optimizing storage. Close 1 Manage.. TO Arial v 16 X LA ~ A A ab Wrap Text Accounting PL/ ST LEH Delete Format Paste BIU. v A Merge & Center v & S% Insert Conditional Format Cell Formatting as Table Styles Sort Filter Find & Select Analyze Data Yes No G 1 J K L M N 0 P Q R S TU Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left aft? C7 fx =B5 B D E F F G H PRODUCTION BUDGET January February March April Sales in Units 17,568.00 33,739.00 | 31,361.00 51,765.00 Desired Ending Inventory 19,357.05 17,992.72 | 29,699.09 37.276.34 Total Units Needed 36,925.05 51,731.72 61,060.09 89,041.34 Beginning Inventory 19,357.05 17,992.72 | 29,699.09 Total Production in Units 36,925.05 32,374.67 43,067.37 59,342.24 Sales + El - Total Needed-BI-Peaduction 10 11 12 13 14 15 16 17 18 18 20 Data Sheet Sales Budget Operating Budget Production Budget Materials Req. Budget Direct Labor Budget Manu OH Budget Product Cost per Unit Projected Income Ready Tu GUH 27 A O la Data Window Help O 100% 62 Wed 2:18 PM E Excel File File Edit View Insert Format Tools . AutoSave OF OF A SU Home Insert Draw Page Layout Formulas Data ACC202- Excel Budgeting Project Master MWF (1) View Tell me Review Your disk is almost full Save space by optimizing storage. Close 1 Manage... TO Arial v 16 X LG ~ A A ab Wrap Text Accounting 12 LEH Paste BIU. v A Merge & Center S% Insert Delete Format Conditional Format Cell Formatting as Table Styles Sort Filter Find & Select Analyze Data Yes No C K L M N 0 P a R S S T Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left aft? B5 fx =B3*84 B E F G H I J MATERIALS REQUIREMENTS BUDGET January February March April Units to be produced 36,925.05 Material/Unit 13.00 Total Production Need 480,025.65 Desired Ending Inventory Total Materials Needed Beginning Inventory 8 Total Materials Needed to be 9 Purchased 4 Cost per Unit of Material 10 Total Material Cost 11 12 Material/unit 13 14 Cost per Unit of Material Cost of Material per Product 16 13.00 $23.00 16 17 18 19 Data Sheet Sales Budget Operating Budget Production Budget Materials Req. Budget Direct Labor Budget Manu OH Budget Product Cost per Unit Projected Income LLLLS SLLADA Ready GUH 27 A O 21 Data Window Help O 100% 62 Wed 2:18 PM E Excel File File Edit View Insert Format Tools . AutoSave OF OF A SU Home Insert Draw Page Layout Formulas Data ACC202- Excel Budgeting Project Master MWF (1) View Tell me Review Your disk is almost full Save space by optimizing storage Close 1 Manage... TO Arial v 16 X [G ~ A A ab Wrap Text Accounting LXVHL Paste B IUB v A Merge & Center S% Insert Delete Format Conditional Format Cell Formatting as Table Styles Sort Filter Find & Select Analyze Data Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left aft? Yes No C11 B D E F H 1 J K LM N o P Q R 8 | U DIRECT LABOR BUDGET January February March Number of Units Produced 5 Direct Labor Hours/Unit Total Direct Labor Hours E Needed Cost/Direct Labor Hour 7 Total Direct Labor Cost 10 11 Direct Labor Hours/Unit 12 Cost/Direct Labor Hour Cost of Direct Labor per Product 13 14 15 16 17 18 16 Data Sheet Sales Budget Operating Budget Production Budget Materials Req: Budget Direct Labor Budget Manu OH Budget Product Cost per Unit Projected Income LA LLISTA D Ready CH 27 . A 469 O la Data Window Help O 100% 62 Wed 2:18 PM E Excel File File Edit View Insert Format Tools . AutoSave OF OF A SU Home Insert Draw Page Layout Formulas Data ACC202- Excel Budgeting Project Master MWF (1) View Tell me Review Your disk is almost full Save space by optimizing storage. Close 1 Manage... TO Arial v 16 ~ A A ab Wrap Text X LG Accounting ST LXVII Paste BIU. v A Merge & Center S% Insert Delete Format Conditional Format Cell Formatting as Table Styles Sort Filter Find & Select Analyze Data Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left aft? Yes No B5 A B D F E F G F H I J K L M N o Q QR S T U MANUFACTURING OVERHEAD BUDGET January February March Production in Units Variable OH Rate/Unit Anticipated VOH Costs Fixed OH Costs 9 Total Anticipated OH Costs 10 11 Predetermined OH Rate 12 Total Estimated OH Costs (for the quarter) Total Estimated DLHs (for the quarter) 13 per DLH 14 15 MOH per Unit: 16 Predetermined MOH rate 17 DLHs per Unit 18 MOH per Unit of Product 19 20 21 22 Data Sheet Sales Budget Operating Budget Production Budget Materials Req. Budget Direct Labor Budget Manu OH Budget Product Cost per Unit Projected Income LLLLSLLADAS D TU Ready CH 27 A O Data Window Help OD 100% (42) 62 Wed 2:18 PM E Excel File File Edit View Insert Format Tools . AutoSave OF OF SU Home Insert Page Layout Formulas Data ACC202- Excel Budgeting Project Master MWF (1) View Tell me Draw Review Your disk is almost full Save space by optimizing storage Close 1 Manage... TO Arial v 16 X LA ~ A A ab Wrap Text Accounting LEH Delete Format Paste BIU B v A Merge & Center & S% Insert Conditional Format Cell Formatting as Table Styles Sort Filter Find & Select Analyze Data Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? Yes No B4 C D E F G H I J K L M N 0 Q R S T LIV A PRODUCT COST PER UNIT Amount From Material Buda Item Direct Materials Direct Labor Manufacturing Overhead Total Unit Cost From DL Budget From Manu OH Budg 7 9 10 11 12 13 14 15 16 17 18 18 20 Data Sheet Sales Budget Operating Budget Production Budget Materials Req. Budget Direct Labor Budget Manu OH Budget Product Cost per Unit Projected Income 3 Ready T IWUM GUH 27 A W Data Window Help OD 100% 67 Wed 2:18 PM E Excel File File Edit View Insert Format Tools . AutoSave OF OF A SU Home Insert Draw Page Layout Formulas Data ACC202- Excel Budgeting Project Master MWF (1) View Tell me Review Your disk is almost full Save space by optimizing storage. Close 1 Manage.. TUT Arial v 16 X LG ~ A A ab Wrap Text Accounting ST LXVII Paste BIU. v A S% Insert Merge & Center & Delete Format Conditional Format Cell Formatting as Table Styles Sort Filter Find & Select Analyze Data Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left aft? Yes No B4 D F F C G H T J | | M N 0 PO RI R ST T 2 PROJECTED INCOME STATEMENT January February March 2 Sales In Units Sales Revenue Cost of Goods Sold: Gross Profit (Margin) Selling and Administrative (Operating) Expenses 8 Operating Income 10 11 12 13 14 1B Data Sheet Sales Budget Operating Budget Production Budget Materials Req: Budget Direct Labor Budget Manu OH Budget Product Cost per Unit Projected Income LALARM T U | Ready GUIN 27 A O 21