Please Complete using excel. Thanks! :)

mschaffer schaffer - Project2020_B4_Schaffier_M - Excel Tell me what you want to do X Share Data Review View Help DS File Home Insert Page Layout Formulas X Cut Calibri 11 A Paste Format Printer B I U. . .A Clipboard Font Bad * A7 LE Copy === = = = Wrap Text Merge & Center - General $ % Number 1 Normal Conditional Formatas Neutral Formalling Table & * Insert Delete Format Calculation Check Cell Clear Sort & Find & Filler Select- Eciting Aignment Styles M N O 2 Confinement Products is a manufacturing company. It uses absorption costing for budgeting amount unit Budgeted sales: Q1 Sales 25,000 coses 02 Sales 30.500 coses Q3 Sales 27,750 coses Q4 Sales 12,750 cases Unit Selling Price 21.50 dollars 12 13 All sales are on account on credit). Collection pattern: Cash collected in the quarter of sale Cash collected in the quarter following sale 82% 18% 16 17 Bepinning FG inventory lin units) Desired ending FG inventoryext quarter's sales Desired ending FG inventory at year end 2,000 coses 20% 11,000 cases 18 20 Deginning RM inventory (in units) Quantity of DM needed per unit DM cost per pound Desired ending RM inventoryext quarter's RM needs Desired ending RM inventory at year end 21,000 pounds 14 pounds 0.28 dollars 15% 23,000 pounds Cash payments to suppliers: paid in the quarter of purchase paid in the quarter following purchase 18% of the purchases 82% of the purchases 30DL houts per unit Introduction Key Est 0.40 hours Budgets Budgeted IS Medy # 0 - * Type here to search 0 + 100% 6:02 PM 4/5/20209 99 9e Powi AA . Project2020_B4_Schaffier_M - Excel Tell me what you want to do Data Review View Help OS: File Home Insert Page Layout Formulas A X Cut Calibri 11 A Paste Format Printer B I U. . .A Clipboard Font mschaffer schaffer - X Share ulosur Insert Delete Format Sort & Find & Clear Filler Select- Eciting La Copy Bad === = = = Wrap Text Merge & Center - General $ % Number 1 Normal Conditional Formatas Neutral Formalling Table Good Check Cell & * Calculation Aignment Styles F G H I J K L M N O P 12 AB 28 paid in the quarter following purchase 82% of the purchases 30 DL hours per unit DL rate per hour 0.40 hours 20.00 dollars Manufacturing overhead Variable MOI Fixed MOH $ 5.00 per DLI $ 20,000.00 per quarter Noncash MOH Depreciation $ 17,000.00 per quarter Selling and administrative expenses Variable SA $ 1.10 per cose Fixed SA Advertising $ 7,200.00 per quarter Executive Salaries $ 34,000.00 per quarter Information Technology $ 8,500.00 per quarter Trade Association Dues $ 4,400.00 per quarter Depreciation $ 5,000.00 per quarter Confinement Products has an agreement with a local bank that would supply any amount of loan it needs to maintain the minimum cash balance. Interest is not compounded and paid at the end of each year. 51 57 Interest rate for bank loan Minimum cash balance Equipment purchases 4.10% per quarter 7,500.00 $ 91 $ 55 7,000.00 for 01 - for QZ uz Introduction Key Est Budgets Budgeted IS # 2 - + 100% * Type here to search A . * 6:02 PM 45/2020 10 Project2020_B4_Schaffier_M - Excel Tell me what you want to do Data Review View Help OS: File Home Insert Page Layout Formulas A X Cut Calibri 11 A Paste Format Printer BI U . A Clipboard Fonts mschaffer schaffer - X Share ulosur Insert Delete Format Sort & Find & Clear Filler Select- Eciting La Copy Bad === = = = Wrap Text Merge & Center - General $ %) Number 1 Normal Conditional Formats Neutral Formalling Table Good Check Cell Calculation * Aignment Styles $ 7,500.00 52 53 Minimum cash balance Equipment purchases 2 03 $ 7,000.00 for Q1 - for 02 $ 10,000.00 for 03 $ 28,000.00 for Q4 $ 4,000.00 per quarter 94 Cash dividends Confinement Products Co. Balance Sheet For the Year Ended Dec 31, 2019 $89,000 145,000 7,000 25,000 Assets Current assets: Cash Accounts receivable Raw materials inventory Finished goods inventory Total current assets plent and equipment Land Buildings and equipment Accumulated depreciation Plant and equipment, net Total assets $266,000 250,000 800,000 (312,000) 738,000 $1,004,000 $79,000 Liabilitics and Stockholders' Equity Current liabilities: Accounts payable raw materials) Stockholders' equity: Common stock, no par Introduction Key Est Budgets Budgeted IS Screenshot saved The screenshot was added to your OneDrive. OneDrive 80 $182,650 * Type here to search 0 9 . O e PS) Ow A . * A 6:02 PM 4/5/2020 10 Project2020_B4_Schaffier_M - Excel Tell me what you want to do Data Review View Help OS: File Home Insert Page Layout Formulas A X Cut Calibri 11 A Paste Format Printer BI U . A Clipboard Font mschaffer schaffer - X Share F ulosur Insert Delete Format Sort & Find & Clear Filler Select- Eciting La Copy Bad === = = = G00 Wrap Text Merge & Center - General $ %) Number Good Check Cell 1 Normal Conditional Formats Neutral Formalling Table Calculation * Aignment Styles E F G H I J K L M N O P - 12 AB 58 Cash dividends $ 4,000.00 per quarter Confinement Products Co. Balance Sheet For the Year Ended Dec 31, 2019 $89,000 145,000 1.000 25,000 Assets Current assets: Cash Accounts receivable Raw materials inventory Finished goods inventory Total current assets Plent and equipment Land Buildings and equipment Accumulated depreciation Plant and equipment, net Total assets $266,000 250,000 800,000 (312,000) 738,000 $1,004,000 $79,000 Liabilities and Stockholders' Equity Current liabilities: Accounts payable (raw materials) Stockholders' equily: Common stock, no par Retained earnings Total stockholders' cquity Total liabilities and stockholders' equity $182,650 142.350 925,000 $1,004,000 Screenshot saved The screenshot was added to your OneDrive. OneDrive Introduction Key Est Budgets Budgeted IS Medy : Type here to search 0 : 299. 9 e PS w E ] A . * A 6:03 PM 4/5/2020 10 Master Budget Excel Project Instructions - Word Table Tool mschaffer schaffer Hile Home Insert Design Layout References Mallings Review View Help Design Layout Tell me what you want to do A X Out Calibri (Body - 11 A M S E E TABLCD ABLCDAaBbc AaBbcc AaB ADbCD Abu AbbcOu AbbcOu AaBbceDe Nobcou Paste * Format Painter inter B I U XX A.2.A - = = = = 0 - - 1 Normal 1 No Spac... Heading 1 Heading 2 Tille Subtitle Subtle En.... Emphasis Intense ... Strony Quote Clipboerd Fort 6 Share Orind - Replace Rep Select LB Copy - Paragraph 15 Editing MGA 202 Spring 2020 Sales Budget and Schedule of Expected Cash Collections (up to 12 possible points) Production Budget (up to 8 possible points) Direct Materials budget and Schedule of Expected Cash Disbursements for Materials (up to 14 possible points) Direct Labor Budget (up to 8 possible points) Manufacturing Overhead Budget (up to 12 possible points) Ending Finished Goods Inventory Budget (up to 6 possible points) Selling and Administration Expenses Budget (up to 12 possible points) Cash Budget (up to 20 possible points) Budgeted Income Statement (up to 8 possible points) Requirements: "The budget and schedule are complete (as exampled by the Each budgeted value that is incorrect will incur a penalty al one point textbook. for a maximum loss of up to the amount of paints possible for that *The calculated balances are correct (accurate). particular budget section *The correct inputs are used. * The schedule includes lour quarterly and one annual columns. Note: Only the originating error will be penalized preints if proper referencing and formulas are used and it is easy Lu ruce to the source of the error. That is, flow through errors that are the result of incorrect calculations carlier will not be penalized it proper referencing and formulas ure used. Due Date (up to 100 point penalty) Requirements: late Penalty: F - + 100% Page of 9 : 3013 words Type here to search E . # 999 e Ps] o wg A m L 6:03 PM 4/5/2020 * 10 mschaffer schaffer - Project2020_B4_Schaffier_M - Excel Tell me what you want to do X Share Data Review View Help DS File Home Insert Page Layout Formulas X Cut Calibri 11 A Paste Format Printer B I U. . .A Clipboard Font Bad * A7 LE Copy === = = = Wrap Text Merge & Center - General $ % Number 1 Normal Conditional Formatas Neutral Formalling Table & * Insert Delete Format Calculation Check Cell Clear Sort & Find & Filler Select- Eciting Aignment Styles M N O 2 Confinement Products is a manufacturing company. It uses absorption costing for budgeting amount unit Budgeted sales: Q1 Sales 25,000 coses 02 Sales 30.500 coses Q3 Sales 27,750 coses Q4 Sales 12,750 cases Unit Selling Price 21.50 dollars 12 13 All sales are on account on credit). Collection pattern: Cash collected in the quarter of sale Cash collected in the quarter following sale 82% 18% 16 17 Bepinning FG inventory lin units) Desired ending FG inventoryext quarter's sales Desired ending FG inventory at year end 2,000 coses 20% 11,000 cases 18 20 Deginning RM inventory (in units) Quantity of DM needed per unit DM cost per pound Desired ending RM inventoryext quarter's RM needs Desired ending RM inventory at year end 21,000 pounds 14 pounds 0.28 dollars 15% 23,000 pounds Cash payments to suppliers: paid in the quarter of purchase paid in the quarter following purchase 18% of the purchases 82% of the purchases 30DL houts per unit Introduction Key Est 0.40 hours Budgets Budgeted IS Medy # 0 - * Type here to search 0 + 100% 6:02 PM 4/5/20209 99 9e Powi AA . Project2020_B4_Schaffier_M - Excel Tell me what you want to do Data Review View Help OS: File Home Insert Page Layout Formulas A X Cut Calibri 11 A Paste Format Printer B I U. . .A Clipboard Font mschaffer schaffer - X Share ulosur Insert Delete Format Sort & Find & Clear Filler Select- Eciting La Copy Bad === = = = Wrap Text Merge & Center - General $ % Number 1 Normal Conditional Formatas Neutral Formalling Table Good Check Cell & * Calculation Aignment Styles F G H I J K L M N O P 12 AB 28 paid in the quarter following purchase 82% of the purchases 30 DL hours per unit DL rate per hour 0.40 hours 20.00 dollars Manufacturing overhead Variable MOI Fixed MOH $ 5.00 per DLI $ 20,000.00 per quarter Noncash MOH Depreciation $ 17,000.00 per quarter Selling and administrative expenses Variable SA $ 1.10 per cose Fixed SA Advertising $ 7,200.00 per quarter Executive Salaries $ 34,000.00 per quarter Information Technology $ 8,500.00 per quarter Trade Association Dues $ 4,400.00 per quarter Depreciation $ 5,000.00 per quarter Confinement Products has an agreement with a local bank that would supply any amount of loan it needs to maintain the minimum cash balance. Interest is not compounded and paid at the end of each year. 51 57 Interest rate for bank loan Minimum cash balance Equipment purchases 4.10% per quarter 7,500.00 $ 91 $ 55 7,000.00 for 01 - for QZ uz Introduction Key Est Budgets Budgeted IS # 2 - + 100% * Type here to search A . * 6:02 PM 45/2020 10 Project2020_B4_Schaffier_M - Excel Tell me what you want to do Data Review View Help OS: File Home Insert Page Layout Formulas A X Cut Calibri 11 A Paste Format Printer BI U . A Clipboard Fonts mschaffer schaffer - X Share ulosur Insert Delete Format Sort & Find & Clear Filler Select- Eciting La Copy Bad === = = = Wrap Text Merge & Center - General $ %) Number 1 Normal Conditional Formats Neutral Formalling Table Good Check Cell Calculation * Aignment Styles $ 7,500.00 52 53 Minimum cash balance Equipment purchases 2 03 $ 7,000.00 for Q1 - for 02 $ 10,000.00 for 03 $ 28,000.00 for Q4 $ 4,000.00 per quarter 94 Cash dividends Confinement Products Co. Balance Sheet For the Year Ended Dec 31, 2019 $89,000 145,000 7,000 25,000 Assets Current assets: Cash Accounts receivable Raw materials inventory Finished goods inventory Total current assets plent and equipment Land Buildings and equipment Accumulated depreciation Plant and equipment, net Total assets $266,000 250,000 800,000 (312,000) 738,000 $1,004,000 $79,000 Liabilitics and Stockholders' Equity Current liabilities: Accounts payable raw materials) Stockholders' equity: Common stock, no par Introduction Key Est Budgets Budgeted IS Screenshot saved The screenshot was added to your OneDrive. OneDrive 80 $182,650 * Type here to search 0 9 . O e PS) Ow A . * A 6:02 PM 4/5/2020 10 Project2020_B4_Schaffier_M - Excel Tell me what you want to do Data Review View Help OS: File Home Insert Page Layout Formulas A X Cut Calibri 11 A Paste Format Printer BI U . A Clipboard Font mschaffer schaffer - X Share F ulosur Insert Delete Format Sort & Find & Clear Filler Select- Eciting La Copy Bad === = = = G00 Wrap Text Merge & Center - General $ %) Number Good Check Cell 1 Normal Conditional Formats Neutral Formalling Table Calculation * Aignment Styles E F G H I J K L M N O P - 12 AB 58 Cash dividends $ 4,000.00 per quarter Confinement Products Co. Balance Sheet For the Year Ended Dec 31, 2019 $89,000 145,000 1.000 25,000 Assets Current assets: Cash Accounts receivable Raw materials inventory Finished goods inventory Total current assets Plent and equipment Land Buildings and equipment Accumulated depreciation Plant and equipment, net Total assets $266,000 250,000 800,000 (312,000) 738,000 $1,004,000 $79,000 Liabilities and Stockholders' Equity Current liabilities: Accounts payable (raw materials) Stockholders' equily: Common stock, no par Retained earnings Total stockholders' cquity Total liabilities and stockholders' equity $182,650 142.350 925,000 $1,004,000 Screenshot saved The screenshot was added to your OneDrive. OneDrive Introduction Key Est Budgets Budgeted IS Medy : Type here to search 0 : 299. 9 e PS w E ] A . * A 6:03 PM 4/5/2020 10 Master Budget Excel Project Instructions - Word Table Tool mschaffer schaffer Hile Home Insert Design Layout References Mallings Review View Help Design Layout Tell me what you want to do A X Out Calibri (Body - 11 A M S E E TABLCD ABLCDAaBbc AaBbcc AaB ADbCD Abu AbbcOu AbbcOu AaBbceDe Nobcou Paste * Format Painter inter B I U XX A.2.A - = = = = 0 - - 1 Normal 1 No Spac... Heading 1 Heading 2 Tille Subtitle Subtle En.... Emphasis Intense ... Strony Quote Clipboerd Fort 6 Share Orind - Replace Rep Select LB Copy - Paragraph 15 Editing MGA 202 Spring 2020 Sales Budget and Schedule of Expected Cash Collections (up to 12 possible points) Production Budget (up to 8 possible points) Direct Materials budget and Schedule of Expected Cash Disbursements for Materials (up to 14 possible points) Direct Labor Budget (up to 8 possible points) Manufacturing Overhead Budget (up to 12 possible points) Ending Finished Goods Inventory Budget (up to 6 possible points) Selling and Administration Expenses Budget (up to 12 possible points) Cash Budget (up to 20 possible points) Budgeted Income Statement (up to 8 possible points) Requirements: "The budget and schedule are complete (as exampled by the Each budgeted value that is incorrect will incur a penalty al one point textbook. for a maximum loss of up to the amount of paints possible for that *The calculated balances are correct (accurate). particular budget section *The correct inputs are used. * The schedule includes lour quarterly and one annual columns. Note: Only the originating error will be penalized preints if proper referencing and formulas are used and it is easy Lu ruce to the source of the error. That is, flow through errors that are the result of incorrect calculations carlier will not be penalized it proper referencing and formulas ure used. Due Date (up to 100 point penalty) Requirements: late Penalty: F - + 100% Page of 9 : 3013 words Type here to search E . # 999 e Ps] o wg A m L 6:03 PM 4/5/2020 * 10