All these are in proper sequence. please solve it.

I Cannot increase the Font Size more, So can you please manage to answer.

Please see below for your group project variables. A B C D E F G H 1 K L M N O P. Q R Section Group # 31 2 15001 1600 6% 150 170 120000 12 60 42 1890000 12900 13800 13500 14400 15480 165601 162001 172801 AutoSum , Calibri - 11 ab Wrap Text General Normal Bad & Cut E Copy Format Painter AT O HE Fill - Paste A- BIU- 4.0 C. % Merge & Center .00 Neutral Insert Delete Format Conditional Format as Good Formatting Table - Styles Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells J44 X fir R S T U X Y z AA AB AC AD AE AF AG - Fine Office Company c Additional details continued: Fine Office Company will p$60,000 in dividends in Q4 Currently, the cash balance in the bank is $15,000. Fine Office Company wants to maintain a minimum cash balance of $10,000 in the bank for each quarter. Budgeted sales volumes are: P100 Q1 Q2 03 04 P200 01 Q3 04 c Selling and Administration expenses for the budgeted year are as follows; Variable Cost: Delivery costs are based on 0.3 per sales unit. Commissions are based on 0.1 % of sales value. CVP I/S $ 36001 140000 20000 90001 70000 A B C D E F G . K L M N P 1 ACCT-5012 - CASE STUDY Group members 2 3 Fine Office Company Marking Rubric Don't use marking cells 4 Tab Max. Points Group Points 5 Fine Office Company makes office furniture for offices. They are in the process of prer Sales U 6 a Master Budget including Operating budget, Cash Statement, Income Statement Production 10 7 and Balance Sheet for 2021. The yearly budget is broken into quarters. The year end is Dir. Materials 13 12 8 31st December 2021. Your group has been requested to compile a master budget for thDir. Labour 9 fiscal year 2021. Manufacturing OH 18 10 Package is to include the following budgets; Cost of Ending Finished 12 0 11 1. Sales budget for each quarter and for the year COGS 14 O 12 2. Production budget for each quarter and for the year Selling & Admin 17 0 13 3. Purchasing Budget for each quarter and for the year 1/S 18 0 14 4. Direct labour budget for each quarter and for the year 16 0 15 5. Manufacturing overhead budget for each quarter and for the year Cash 32 0 16 6. Selling and Administration budget Cash Worksheets 14 0 17 7. Work sheets for Collections and Disbursements B/S 24 0 18 8. Budgeted Income Statement Total 200 0 19 9. CVP Income Statement 20 10. Budgeted Cash Statement 21 11. Budgeted Balance Sheet 22 Additional details: 23. Fine Office Company produces two products P100 and P200 24. Sales price per P100 is | 25 Sales price per P200 is B 26 . There are 800 units from P100 in finished goods inventory at the end of 2020 with a value of $ 3,60,000 and 500 units from P200 at the end 27 of 2020 valy 3,00,000. At the end of each quarter, Fine Office Company requires ending inventory to be equc of the following quarter's budgeted sales in units. 28 The required ending inventory for Dec 31, 2021 are 600 units for P100 and 400 units for P200 29. Each P100 unit uses D sq. ft. of steel during the manufacturing process. The cost of steel for 2021 is estima 8 per sq. ft. 30 Each P200 unit uses sq. ft. of steel during the manufacturing process. 31 . Fine Office Company currently ha 30,000 sq. ft. of steel in the beginning inventory. At 32 the end of each quarter, Fine Office Company wants to have sq. ft. of ending 33 inventory 34. Each product requires machine hours ar H direct labour hrs to produce. 35 Direct Labour costs $ per direct labour hour. 36 Fine Office Company allocates manufacturing overhead costs based on the estimated machine 37 hours. Estimated manufacturing overhead cost for 2021 are and are all variable. 38 . For each quarter, it is estimated that 40% of sales will be cash and 60 39 % will be credit sales. Of the credit sales, 80% pay in the quarter of the sale and 20% pay in the 40 following quarter. Credit sales from Q4 2020 were $13,00,000 41. Direct labour costs and manufacturing overhead costs are paid for in cash in the quarter they 42 occurred. 43. Assume operating expenses occur evenly throughout the year and are all paid in cash. 44. For each quarter, 70% of material purchases are paid for in cash in the quarter of 45 the purchase and 30% are paid in the following quarter. Purchases of materials from 46 Q4 2020 were $15,00,000 Instructions Sales Budget Production Budget Direct materials Budget Direct Labour budget Manufacturing (... Ready Fixed Costs: Accounting & professional services Administrative & Sales Salaries Advertising Computer costs Depreciation Office Supplies Printing Insurance Property taxes Rent Utilities Total Fixed Costs 5000 3000 40001 20001 40000 3400 300000 . Fine Office Company will purchase a new machine on 700000 and will make two equal payments. The first payment will be in Q2 and the second in 04. Assume the machine was purchased at the beginning of the year. Taxation 30% on taxable income and paid at the end of Q4 each year. Balance sheet information as at 31st December 2020 is as follows; PPE ####### Accumulated Depreciation ####### Common Stock Retained Earnings ### For Cost of goods sold (COGS); Add total costs of production + Beginning Finished goods - Ending Finished goods Inventory. Interest 9000 on loans is paid in total at the end of the year and is a fixed cost. + W 1 + 63% AutoSum Calibri 11 ab Wrap Text General Normal Bad % Cut e Copy Format Painter AT O Fill - Paste BIU A = = . O,% Merge & Center Insert Delete Format Neutral .00 Conditional Format as Good Formatting Table- Styles Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells L10 fic A B D E F G H 1 J K M N O Q 1 2 Don't use marking cells Sales Budget 3 Max. Points Group Points Quarter 1 2 3 4 Year 4 P100 Expected 5 Sales units 1 Selling Price 6 1 1 Total Sales P100 7 P200 Expected 8 Sales units 1 Selling Price 9 1 Total Sales P200 10 1 Total Sales 11 1 12 7 0 13 14 IT Calibri AutoSum % Cut Es Copy 11 A A ab Wrap Text General ET Bad DX AT O H Fill - Paste BIU. A = = - % .00 .000 Merge & Center - Normal Conditional Format as Good Formatting - Table Styles Insert Delete Format Neutral Format Painter Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells M17 X fix A B D E F G H K L M N o 0 R S TA 2 3 Don't use marking cells Production Budget 4 Max. Points Group Points Quarter 1 2 3 4 Year 5 Expected Sales Units P100 6 2 Add desired ending of Inventory P100 7 2 Total needs P100 8 2 Less beginning of Inventory P100 9 2 P100 Required Production: Units 10 2 Expected Sales Units P200 11 10 0 Add desired ending of Inventory P200 12 Total needs P200 13 Less beginning of Inventory P200 14 P200 Required Production: Units 15 16 17 Instructions Sales Budget Production Budget Direct materials Budget Direct Labour budget Manufacturing (... # : & Cut Calibri - 12 A A ab Wrap Text General Normal Bad AY O E Copy 1 Insert Delete Format Paste B -A- IU - .00 % , Merge & Center 60 Neutral Format Painter AutoSum- Fill- Sort & Find & Clear - Filter - Select Editing Conditional Format as Good Formatting Table Styles Clipboard Font Alignment Number Cells K3 fic Don't use marking cells B D E F G H 1 J K L M N o Q R 1 2 Don't use rharking cells Max. Points Group Points 4 Year 1 1 1 1 1 1 1 1 1 1 1 3 4 Direct Materials Budget 5 Quarter 1 3 6 P100 Production units 7 Materials per unit 8 P100 Total material needs/Used 9 P200 Production units 10 Materials per unit 11 P200 Total material needs/Used 12 Total Material needs for P100 and P200 13 Add desired ending of Inventory 14 Total needs 15 Less beginning of Inventory 16 Materials to be purchased: Units 17 Unit price 18 Materials to be purchased: $ 19 Test: 20 21 Cost of Ending Inventory of Direct materials 31st december 2021: 22 23 24 25 26 27 28 29 Instructions Sales Budget Production Budget Direct materials Budget 1 1 13 0 Direct Labour budget Manufacturing (... + 1 MT Calibri 12 AutoSum - ab Wrap Text General Bad AT O H * Cut 9 Copy Format Painter Fill - Paste B I U O,% Merge & Center .0 .00 .00 0 Normal + Conditional Format as Good Formatting - Table - Styles Neutral Insert Delete Format Clear - Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells K3 X Don't use marking cells A B D E F G H 1 J K L M N O Q R 1 2 Don't use rharking cells Max. Points Group Points Year 1 1 1 1 1 5 0 3 4 Direct Labour Budget 5 Quarter 1 2 3 4 6 Production Units 7 Labour per unit 8 Total labour needs 9 Labour Rates Paid 10 Total Direct Labour cost: $ 11 Test 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Instructions Sales Budget Production Budget Direct materials Budget Direct Labour budget Manufacturing + & Cut Calibri - 12 - ab Wrap Text General Normal Bad Y O E Copy 1 Insert Delete Format Paste B -A- IU - .00 2. % Merge & Center 60 Neutral AutoSum - Fill - Sort & Find & Clear - Filter - Select Editing Conditional Format as Good Formatting Table Styles Format Painter Clipboard Font Alignment Number Cells K3 fic Don't use marking cells A B D E F G H 1 J K M N. o Q R S 1 2 Don't use rharking cells Max. Points Group Points Year 3 3 3 3 3 3 4 Manufacturing Overhead Budget 5 1 2 3 4 6 Budeted Production units 7 Machine hours per unit 8 Total Budeted Machine hours 9 10 Budgeted Manufacturing Overhead 11 Test: 12 13 Predetermined Overhead Rate: Estimated Manufacturing Overhead 14 Machine Hours 15 16 17 18 19 $ per machine hour 20 21 22 23 24 25 26 3 18 0 27 28 29 Production Budget Direct materials Budget Direct Labour budget Manufacturing Overhead Budget Cost of En ... + File Home Insert Page Layout Formulas Data Review View Help Acrobat Tell me what you want to do & Share AutoSum , & Cut Eg Copy Autosum Calibri 12 ab Wrap Text General Normal Bad ND RA Fill - Paste B I U A- Merge & Center C. % ) .0 .00 .00 >.0 Neutral Insert Delete Format Format Painter Conditional Format as Good Formatting Table Styles Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells K10 fx Don't use marking cells 1 j M N o Q R S T U V WA Max. Points Group Points 2 2 2 2 2 2 0 12 Don't use rharking cells A B D E F G 1 Ending Finished Goods Inventory Budget P100 2 Cost Element Quantity Cost Total 3 Direct Materials 4 + Direct Labour 5 + Manufacturing Overhead 6 = Product Cost Per Unit 7 X Ending Inventory in Units 8 Ending Finished Goods Inventory 9 10 Ending Finished Goods Inventory Budget P200 11 Cost Element Quantity Cost Total 12 Direct Materials 13 + Direct Labour 14 + Manufacturing Overhead 15 = Product Cost Per Unit 16 X Ending Inventory in Units 17 Ending Finished Goods Inventory 18 19 20 21 22 23 24 25 26 27 28 29 Direct Labour budget Manufacturing Overhead Budget Cost of Ending Finished Invento Cost of Goods Sold SE ... + File Home Insert Page Layout Formulas Data Review View Help Acrobat Tell me what you want to do Calibri 12 - ale Wrap Text General Bad 1 HA * Cut E Copy Format Painter Share AutoSum Y O Fill - Sort & Find & Clear Filter - Select Editing Paste B I U - A - O, % Merge & Center 4.0 .00 Normal Conditional Format as Good Formatting Table Styles .00 >.0 Neutral Insert Delete Format Clipboard Font Alignment Number Cells K10 fic Don't use marking cells E H L M N O Q R S T U V - F $ Max. Points Group Points 2 2 2 B D 1 Cost of Goods Sold 2 Direct materials Used 3 Direct labour costs 4 Manufacturing Overhead Costs 5 Total Costs of Manufacture: 6 Beginning Finished Goods Inventory 7 Less: Ending Finished Goods Inventory 8 Cost of Goods Sold 9 2 2 2 2 14 0 Don't use rharking cells 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Direct Labour budget Manufacturing Overhead Budget Cost of Ending Finished Invento Cost of Goods Sold SE ... + File Home Insert Page Layout Formulas Data Review View Help Acrobat Tell me what you want to do Share AutoSum * Cut Es Copy Calibri 11 ab Wrap Text General Normal Bad Ay O Paste BIU- A- = = Merge & Center C, % 0.00 Neutral Insert Delete Format Conditional Format as Good Formatting Table - Styles Format Painter Fill - Clear - Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells 018 X fic N O P Q R S T U V w A D E F G . 1 K M 2. 3 Don't use marking cells 4 Selling and Administrative Expense Budget Max. Points Group Points 5 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year 6 Budgeted Sales in units: 7 Budgeted Sales value: 8 Variable Cost: 9 Delivery costs 1 10 Commissions 1 11 Total Variable Costs: 1 12 Fixed Costs: 13 Accounting & professional services 1 14 Administrative and sales salaries 1 15 Advertising 1 16 Computer costs 1 17 Depreciation 18 Office Supplies 1 19 Printing 20 Insurance 1 21 Property taxes 22 Rent 1 23 Utilities 1 24 Total Fixed Costs: 25 Total costs: 1 26 Test: 27 Cash Total per quarter: $ 1 28 Note: Deduct Depreciation. Test: 29 17 0 30 Manufacturing Overhead Budget Cost of Ending Finished Invento Cost of Goods Sold Selling & Admin. budget 1 1 1 1 non File Home Insert Page Layout Formulas Data Review View Help Acrobat Tell me what you want to do 2 Share & Cut Copy Calibri 11 ab Wrap Text General Normal Bad 1 A Insert Delete Format Paste BIU- A- - % Merge & Center - 4.0 ,00 .000 Neutral Format Painter AutoSum Fill - Sort & Find & Clear Filter - Select Editing Conditional Format as Good Formatting Table Styles Clipboard Font Alignment Number Cells A15 X fie B C D E F G H 1 J K M N 0 Q RA A 4. Income Statement for year ended 31st December 2021 5 Max. Points Group Points $ 2 2 2 NNNNNN 18 0 6 Sales 7 Cost of goods sold 8 Gross profit 9 Selling and Administration expenses 10 Operating Income 11 Interest expense 12 Net Income before taxation 13 Taxation 14 Net Income After taxation 15 16 17 18 19 Alternatively: 20 21 Cost of Goods Sold 22 Direct materials Used 23 Direct labour costs 24 Manufacturing Overhead Costs 25 Total Costs of Manufacture: 26 Beginning Finished Goods Inventory 27 Less: Ending Finished Goods Inventory 28 Cost of Goods Sold 29 30 COGS using the unit cost method: 31 Rounding difference: 32 ... Selling & Admin. budget $ Income Statement CVP Income Statement Cash Budget Worksheets Balance Sheet + 7 Ready D 100% File Home Insert Page Layout Formulas Data Review View Help Acrobat Tell me what you want to do Share AutoSum & Cut Ee Copy Calibri - 12 - ab Wrap Text General Normal Bad 47 O 1 Insert Delete Format Fill - Paste B. -A- I U - = .00 O % Merge & Center .00 Neutral Format Painter Conditional Format as Good Formatting Table Styles Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells K7 fic Don't use marking cells B D E F G H | J K L M N O P Q R Don't use charking cells Max. Points Group Points 1 1 1 1 1 1 1 1 1 1 1 4 5 6 7 8 CVP Income Statement for year ended 31st December 2021 9 $ $ 10 Sales 11 Less: Variable Costs 12 Direct materials 13 Direct Labour 14 Manufacturing Variable Overhead costs 15 Selling & Administration variable costs 16 Adjustment for Finished Inventory 17 Beginning Finished Goods Inventory 18 Less: Ending Finished Goods Inventory 19 Total Contribution Margins 20 Less: Fixed costs 21 Selling & Administration fixed costs 22 Interest costs 23 Income Tax 24 Net Income after taxation 25 26 Check result with Income Statement: 27 28 29 30 31 32 33 34 25 Selling & Admin. budget Income Statement CVP Income Statement 1 1 1 1 1 16 Cash Budget Worksheets Balance Sheet + Ready EU 900 File Home Insert Page Layout Formulas Data Review View Help Acrobat Tell me what you want to do Share AutoSum * Cut Copy Autosum Calibri 11 ab Wrap Text General Normal Bad AY O RA Fill - Paste B I U C. % ) Merge & Center A .0 .00 Format Painter .00 >.0 Neutral Insert Delete Format Conditional Format as Good Formatting Table Styles Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells A22 fie Financing A B C D E F G H 1 J K L M N O Q R S T U v w X Y 1 Don't use marking cells Max. Points Group Points 4 Year 21 2 2 2 21 2 2 2 2 2 2 3 4 CASH BUDGET 5 for the year ending 31st December 2021 6 1 2 3 7 Beginning cash balance 8 Add: Receipts 9 Collections from Customers 10 Total receipts: 11 Total available cash: 12 Less: Disbursements 13 Direct Materials 14 Direct Labour 15 Manufacturing Overhead 16 Selling and Administrative expenses 17 Purchase of a Machine 18 Income Tax expense 19 Dividends 20 Total disbursements: Excess (deficiency) of available cash over 21 cash disbursements 22 Financing 23 Add: Borrowings 24 Less: Repayments (including interest) 25 Ending cash balance 26 27 Notes: 28 Minimum cash balance: $10,000 29 Interest on loans to be paid in Quarter 4 30 Taxation amount taken from the Income Statement 31 32 33 34 35 36 Selling & Admin. budget Income Statement 2 2 2 2 2 2 32 01 CVP Income Statement Cash Budget Worksheets Balance Sheet + 1 Readu 010 + Slidle AutoSum Calibri 11 A A III ab Wrap Text General Normal Bad AY % Cut E Copy Format Painter 1 Insert Delete Format Fill - Paste BLU- A = = . C.% Merge & Center .00 Neutral Conditional Format as Good Formatting Table- Styles Clear Sort & Find & Filter - Select - Editing Clipboard Font Alignment Number Cells N26 X fic A B D E F G H | J M N o Q K Don't use marking cells Max. Points Group Points mo Receipts by Quarter Qtr 2 Qtr 3 2022 Qtr 1 Qtr 4 1 1 1 1 1 1 2. 3 Schedule of receipts from customers 4 5 Cash Sales Credit Sales Qtr 1 6 Accounts Receivable - 31st Dec 2020 7 First Quarter 8 Second Quarter 9 Third Quarter 10 Fourth Quarter 11 Total Payments:- 12 13 Cash Sales % 14 Credit Sales % 15 Collection of Credit sale in the quarter of the sale 16 Collection of Credit sale in the following quarter 17 18 Schedule of expected payments for Direct materials 19 20 Purchases Qtr 1 21 Accounts Payable - 31st Dec 2020 22 First Quarter 23 Second Quarter 24 Third Quarter 25 Fourth Quarter 26 Total Payments:- 27 28 Purchases paid in cash in the same quarter 29 Purchases paid in the following quarter 20 Selling & Admin. budget Income Statement CVP Income Statement Payments by Quarter Qtr 2 Qtr 3 2022 Qtr 1 Qtr 4 1 1 1 1 1 1 1 1 14 n Cash Budget Worksheets Balance Sheet + 1 nond File Home Insert Page Layout Formulas Data Review view Help Acrobat Tell me what you want to do Share AutoSum Calibri - 11 - ab Wrap Text General Normal Bad O RA ND * Cut 9 Copy Format Painter Fill - Paste A- BIU- C% Insert Delete Format Merge & Center - .0 .00 .00.0 Neutral Conditional Format as Good Formatting Table Styles Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells P27 fr D E F G . 1 j K M Z o P. R S T U Max. Points Group Points 1 1 1 1 1 1 1 1 A B 4 Balance Sheet at 31st December 2021 5 Assets $ $ 6 Current Assets 7 Cash 8 Accounts receivable 9 Finished goods inventory 10 Raw materials inventory 11 Total Current Assets: 12 13 Property, Plant & equipment 14 Less: Accumulated depreciation 15 Total assets: 16 17 Liabilities and Shareholders Equity 18 Liabilities 19 Accounts payable 20 Bank Loan 21 Shareholders Equity 22 Common Stock 23 Retained Earnings 24 Total Shareholders Equity: 25 Total Liabilities and Shareholders Equity 26 27 28 Statement of Retained Earnings as at 31st December 2021 29 Opening balance 30 Net Income 31 32 Less Dividends 33 Closing balance 34 25 Income Statement CVP Income Statement 1 1 1 1 1 I 1 1 1 1 1 Cash Budget Worksheets Balance Sheet + Ready 90% File Home Insert Page Layout Formulas Data Review View Help Acrobat Tell me what you want to do Share AutoSum Calibri - 11 ab Wrap Text General Normal Bad 1 470 & Cut E Copy Format Painter Fill - Paste = % Merge & Center 60 ,00 .000 Neutral Insert Delete Format Conditional Format as Good Formatting Table Styles Clear - Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells P27 for D E F G H 1 J K L N 0 P Q R S T U V w X Y 1 1 1 1 1 1 B 24 Total Shareholders Equity: 25 Total Liabilities and Shareholders Equity 26 27 28 Statement of Retained Earnings as at 31st December 2021 29 Opening balance 30 Net Income 31 32 Less Dividends 33 Closing balance 34 35 36 Recreated from information provided: 37 Balance Sheet at 31st December 2020 38 Assets $ $ 39 Current Assets 40 Cash 41 Accounts receivable 42 Finished goods inventory 43 Raw materials inventory 44 Total Current Assets: 45 46 Property, Plant & equipment 47 Less: Accumulated depreciation 48 Total assets: 49 50 Liabilities and Shareholders Equity 51 Liabilities 52 Accounts payable 53 Shareholders Equity 54 Common Stock 55 Retained Earnings 56 Total Shareholders Equity: 57 Total Liabilities and Shareholders Equity 58 59 60 / Income Statement CVP Income Statement 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 24 01 Cash Budget Worksheets Balance Sheet + Ready 1 + 80% Please see below for your group project variables. A B C D E F G H 1 K L M N O P. Q R Section Group # 31 2 15001 1600 6% 150 170 120000 12 60 42 1890000 12900 13800 13500 14400 15480 165601 162001 172801 AutoSum , Calibri - 11 ab Wrap Text General Normal Bad & Cut E Copy Format Painter AT O HE Fill - Paste A- BIU- 4.0 C. % Merge & Center .00 Neutral Insert Delete Format Conditional Format as Good Formatting Table - Styles Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells J44 X fir R S T U X Y z AA AB AC AD AE AF AG - Fine Office Company c Additional details continued: Fine Office Company will p$60,000 in dividends in Q4 Currently, the cash balance in the bank is $15,000. Fine Office Company wants to maintain a minimum cash balance of $10,000 in the bank for each quarter. Budgeted sales volumes are: P100 Q1 Q2 03 04 P200 01 Q3 04 c Selling and Administration expenses for the budgeted year are as follows; Variable Cost: Delivery costs are based on 0.3 per sales unit. Commissions are based on 0.1 % of sales value. CVP I/S $ 36001 140000 20000 90001 70000 A B C D E F G . K L M N P 1 ACCT-5012 - CASE STUDY Group members 2 3 Fine Office Company Marking Rubric Don't use marking cells 4 Tab Max. Points Group Points 5 Fine Office Company makes office furniture for offices. They are in the process of prer Sales U 6 a Master Budget including Operating budget, Cash Statement, Income Statement Production 10 7 and Balance Sheet for 2021. The yearly budget is broken into quarters. The year end is Dir. Materials 13 12 8 31st December 2021. Your group has been requested to compile a master budget for thDir. Labour 9 fiscal year 2021. Manufacturing OH 18 10 Package is to include the following budgets; Cost of Ending Finished 12 0 11 1. Sales budget for each quarter and for the year COGS 14 O 12 2. Production budget for each quarter and for the year Selling & Admin 17 0 13 3. Purchasing Budget for each quarter and for the year 1/S 18 0 14 4. Direct labour budget for each quarter and for the year 16 0 15 5. Manufacturing overhead budget for each quarter and for the year Cash 32 0 16 6. Selling and Administration budget Cash Worksheets 14 0 17 7. Work sheets for Collections and Disbursements B/S 24 0 18 8. Budgeted Income Statement Total 200 0 19 9. CVP Income Statement 20 10. Budgeted Cash Statement 21 11. Budgeted Balance Sheet 22 Additional details: 23. Fine Office Company produces two products P100 and P200 24. Sales price per P100 is | 25 Sales price per P200 is B 26 . There are 800 units from P100 in finished goods inventory at the end of 2020 with a value of $ 3,60,000 and 500 units from P200 at the end 27 of 2020 valy 3,00,000. At the end of each quarter, Fine Office Company requires ending inventory to be equc of the following quarter's budgeted sales in units. 28 The required ending inventory for Dec 31, 2021 are 600 units for P100 and 400 units for P200 29. Each P100 unit uses D sq. ft. of steel during the manufacturing process. The cost of steel for 2021 is estima 8 per sq. ft. 30 Each P200 unit uses sq. ft. of steel during the manufacturing process. 31 . Fine Office Company currently ha 30,000 sq. ft. of steel in the beginning inventory. At 32 the end of each quarter, Fine Office Company wants to have sq. ft. of ending 33 inventory 34. Each product requires machine hours ar H direct labour hrs to produce. 35 Direct Labour costs $ per direct labour hour. 36 Fine Office Company allocates manufacturing overhead costs based on the estimated machine 37 hours. Estimated manufacturing overhead cost for 2021 are and are all variable. 38 . For each quarter, it is estimated that 40% of sales will be cash and 60 39 % will be credit sales. Of the credit sales, 80% pay in the quarter of the sale and 20% pay in the 40 following quarter. Credit sales from Q4 2020 were $13,00,000 41. Direct labour costs and manufacturing overhead costs are paid for in cash in the quarter they 42 occurred. 43. Assume operating expenses occur evenly throughout the year and are all paid in cash. 44. For each quarter, 70% of material purchases are paid for in cash in the quarter of 45 the purchase and 30% are paid in the following quarter. Purchases of materials from 46 Q4 2020 were $15,00,000 Instructions Sales Budget Production Budget Direct materials Budget Direct Labour budget Manufacturing (... Ready Fixed Costs: Accounting & professional services Administrative & Sales Salaries Advertising Computer costs Depreciation Office Supplies Printing Insurance Property taxes Rent Utilities Total Fixed Costs 5000 3000 40001 20001 40000 3400 300000 . Fine Office Company will purchase a new machine on 700000 and will make two equal payments. The first payment will be in Q2 and the second in 04. Assume the machine was purchased at the beginning of the year. Taxation 30% on taxable income and paid at the end of Q4 each year. Balance sheet information as at 31st December 2020 is as follows; PPE ####### Accumulated Depreciation ####### Common Stock Retained Earnings ### For Cost of goods sold (COGS); Add total costs of production + Beginning Finished goods - Ending Finished goods Inventory. Interest 9000 on loans is paid in total at the end of the year and is a fixed cost. + W 1 + 63% AutoSum Calibri 11 ab Wrap Text General Normal Bad % Cut e Copy Format Painter AT O Fill - Paste BIU A = = . O,% Merge & Center Insert Delete Format Neutral .00 Conditional Format as Good Formatting Table- Styles Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells L10 fic A B D E F G H 1 J K M N O Q 1 2 Don't use marking cells Sales Budget 3 Max. Points Group Points Quarter 1 2 3 4 Year 4 P100 Expected 5 Sales units 1 Selling Price 6 1 1 Total Sales P100 7 P200 Expected 8 Sales units 1 Selling Price 9 1 Total Sales P200 10 1 Total Sales 11 1 12 7 0 13 14 IT Calibri AutoSum % Cut Es Copy 11 A A ab Wrap Text General ET Bad DX AT O H Fill - Paste BIU. A = = - % .00 .000 Merge & Center - Normal Conditional Format as Good Formatting - Table Styles Insert Delete Format Neutral Format Painter Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells M17 X fix A B D E F G H K L M N o 0 R S TA 2 3 Don't use marking cells Production Budget 4 Max. Points Group Points Quarter 1 2 3 4 Year 5 Expected Sales Units P100 6 2 Add desired ending of Inventory P100 7 2 Total needs P100 8 2 Less beginning of Inventory P100 9 2 P100 Required Production: Units 10 2 Expected Sales Units P200 11 10 0 Add desired ending of Inventory P200 12 Total needs P200 13 Less beginning of Inventory P200 14 P200 Required Production: Units 15 16 17 Instructions Sales Budget Production Budget Direct materials Budget Direct Labour budget Manufacturing (... # : & Cut Calibri - 12 A A ab Wrap Text General Normal Bad AY O E Copy 1 Insert Delete Format Paste B -A- IU - .00 % , Merge & Center 60 Neutral Format Painter AutoSum- Fill- Sort & Find & Clear - Filter - Select Editing Conditional Format as Good Formatting Table Styles Clipboard Font Alignment Number Cells K3 fic Don't use marking cells B D E F G H 1 J K L M N o Q R 1 2 Don't use rharking cells Max. Points Group Points 4 Year 1 1 1 1 1 1 1 1 1 1 1 3 4 Direct Materials Budget 5 Quarter 1 3 6 P100 Production units 7 Materials per unit 8 P100 Total material needs/Used 9 P200 Production units 10 Materials per unit 11 P200 Total material needs/Used 12 Total Material needs for P100 and P200 13 Add desired ending of Inventory 14 Total needs 15 Less beginning of Inventory 16 Materials to be purchased: Units 17 Unit price 18 Materials to be purchased: $ 19 Test: 20 21 Cost of Ending Inventory of Direct materials 31st december 2021: 22 23 24 25 26 27 28 29 Instructions Sales Budget Production Budget Direct materials Budget 1 1 13 0 Direct Labour budget Manufacturing (... + 1 MT Calibri 12 AutoSum - ab Wrap Text General Bad AT O H * Cut 9 Copy Format Painter Fill - Paste B I U O,% Merge & Center .0 .00 .00 0 Normal + Conditional Format as Good Formatting - Table - Styles Neutral Insert Delete Format Clear - Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells K3 X Don't use marking cells A B D E F G H 1 J K L M N O Q R 1 2 Don't use rharking cells Max. Points Group Points Year 1 1 1 1 1 5 0 3 4 Direct Labour Budget 5 Quarter 1 2 3 4 6 Production Units 7 Labour per unit 8 Total labour needs 9 Labour Rates Paid 10 Total Direct Labour cost: $ 11 Test 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Instructions Sales Budget Production Budget Direct materials Budget Direct Labour budget Manufacturing + & Cut Calibri - 12 - ab Wrap Text General Normal Bad Y O E Copy 1 Insert Delete Format Paste B -A- IU - .00 2. % Merge & Center 60 Neutral AutoSum - Fill - Sort & Find & Clear - Filter - Select Editing Conditional Format as Good Formatting Table Styles Format Painter Clipboard Font Alignment Number Cells K3 fic Don't use marking cells A B D E F G H 1 J K M N. o Q R S 1 2 Don't use rharking cells Max. Points Group Points Year 3 3 3 3 3 3 4 Manufacturing Overhead Budget 5 1 2 3 4 6 Budeted Production units 7 Machine hours per unit 8 Total Budeted Machine hours 9 10 Budgeted Manufacturing Overhead 11 Test: 12 13 Predetermined Overhead Rate: Estimated Manufacturing Overhead 14 Machine Hours 15 16 17 18 19 $ per machine hour 20 21 22 23 24 25 26 3 18 0 27 28 29 Production Budget Direct materials Budget Direct Labour budget Manufacturing Overhead Budget Cost of En ... + File Home Insert Page Layout Formulas Data Review View Help Acrobat Tell me what you want to do & Share AutoSum , & Cut Eg Copy Autosum Calibri 12 ab Wrap Text General Normal Bad ND RA Fill - Paste B I U A- Merge & Center C. % ) .0 .00 .00 >.0 Neutral Insert Delete Format Format Painter Conditional Format as Good Formatting Table Styles Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells K10 fx Don't use marking cells 1 j M N o Q R S T U V WA Max. Points Group Points 2 2 2 2 2 2 0 12 Don't use rharking cells A B D E F G 1 Ending Finished Goods Inventory Budget P100 2 Cost Element Quantity Cost Total 3 Direct Materials 4 + Direct Labour 5 + Manufacturing Overhead 6 = Product Cost Per Unit 7 X Ending Inventory in Units 8 Ending Finished Goods Inventory 9 10 Ending Finished Goods Inventory Budget P200 11 Cost Element Quantity Cost Total 12 Direct Materials 13 + Direct Labour 14 + Manufacturing Overhead 15 = Product Cost Per Unit 16 X Ending Inventory in Units 17 Ending Finished Goods Inventory 18 19 20 21 22 23 24 25 26 27 28 29 Direct Labour budget Manufacturing Overhead Budget Cost of Ending Finished Invento Cost of Goods Sold SE ... + File Home Insert Page Layout Formulas Data Review View Help Acrobat Tell me what you want to do Calibri 12 - ale Wrap Text General Bad 1 HA * Cut E Copy Format Painter Share AutoSum Y O Fill - Sort & Find & Clear Filter - Select Editing Paste B I U - A - O, % Merge & Center 4.0 .00 Normal Conditional Format as Good Formatting Table Styles .00 >.0 Neutral Insert Delete Format Clipboard Font Alignment Number Cells K10 fic Don't use marking cells E H L M N O Q R S T U V - F $ Max. Points Group Points 2 2 2 B D 1 Cost of Goods Sold 2 Direct materials Used 3 Direct labour costs 4 Manufacturing Overhead Costs 5 Total Costs of Manufacture: 6 Beginning Finished Goods Inventory 7 Less: Ending Finished Goods Inventory 8 Cost of Goods Sold 9 2 2 2 2 14 0 Don't use rharking cells 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Direct Labour budget Manufacturing Overhead Budget Cost of Ending Finished Invento Cost of Goods Sold SE ... + File Home Insert Page Layout Formulas Data Review View Help Acrobat Tell me what you want to do Share AutoSum * Cut Es Copy Calibri 11 ab Wrap Text General Normal Bad Ay O Paste BIU- A- = = Merge & Center C, % 0.00 Neutral Insert Delete Format Conditional Format as Good Formatting Table - Styles Format Painter Fill - Clear - Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells 018 X fic N O P Q R S T U V w A D E F G . 1 K M 2. 3 Don't use marking cells 4 Selling and Administrative Expense Budget Max. Points Group Points 5 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year 6 Budgeted Sales in units: 7 Budgeted Sales value: 8 Variable Cost: 9 Delivery costs 1 10 Commissions 1 11 Total Variable Costs: 1 12 Fixed Costs: 13 Accounting & professional services 1 14 Administrative and sales salaries 1 15 Advertising 1 16 Computer costs 1 17 Depreciation 18 Office Supplies 1 19 Printing 20 Insurance 1 21 Property taxes 22 Rent 1 23 Utilities 1 24 Total Fixed Costs: 25 Total costs: 1 26 Test: 27 Cash Total per quarter: $ 1 28 Note: Deduct Depreciation. Test: 29 17 0 30 Manufacturing Overhead Budget Cost of Ending Finished Invento Cost of Goods Sold Selling & Admin. budget 1 1 1 1 non File Home Insert Page Layout Formulas Data Review View Help Acrobat Tell me what you want to do 2 Share & Cut Copy Calibri 11 ab Wrap Text General Normal Bad 1 A Insert Delete Format Paste BIU- A- - % Merge & Center - 4.0 ,00 .000 Neutral Format Painter AutoSum Fill - Sort & Find & Clear Filter - Select Editing Conditional Format as Good Formatting Table Styles Clipboard Font Alignment Number Cells A15 X fie B C D E F G H 1 J K M N 0 Q RA A 4. Income Statement for year ended 31st December 2021 5 Max. Points Group Points $ 2 2 2 NNNNNN 18 0 6 Sales 7 Cost of goods sold 8 Gross profit 9 Selling and Administration expenses 10 Operating Income 11 Interest expense 12 Net Income before taxation 13 Taxation 14 Net Income After taxation 15 16 17 18 19 Alternatively: 20 21 Cost of Goods Sold 22 Direct materials Used 23 Direct labour costs 24 Manufacturing Overhead Costs 25 Total Costs of Manufacture: 26 Beginning Finished Goods Inventory 27 Less: Ending Finished Goods Inventory 28 Cost of Goods Sold 29 30 COGS using the unit cost method: 31 Rounding difference: 32 ... Selling & Admin. budget $ Income Statement CVP Income Statement Cash Budget Worksheets Balance Sheet + 7 Ready D 100% File Home Insert Page Layout Formulas Data Review View Help Acrobat Tell me what you want to do Share AutoSum & Cut Ee Copy Calibri - 12 - ab Wrap Text General Normal Bad 47 O 1 Insert Delete Format Fill - Paste B. -A- I U - = .00 O % Merge & Center .00 Neutral Format Painter Conditional Format as Good Formatting Table Styles Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells K7 fic Don't use marking cells B D E F G H | J K L M N O P Q R Don't use charking cells Max. Points Group Points 1 1 1 1 1 1 1 1 1 1 1 4 5 6 7 8 CVP Income Statement for year ended 31st December 2021 9 $ $ 10 Sales 11 Less: Variable Costs 12 Direct materials 13 Direct Labour 14 Manufacturing Variable Overhead costs 15 Selling & Administration variable costs 16 Adjustment for Finished Inventory 17 Beginning Finished Goods Inventory 18 Less: Ending Finished Goods Inventory 19 Total Contribution Margins 20 Less: Fixed costs 21 Selling & Administration fixed costs 22 Interest costs 23 Income Tax 24 Net Income after taxation 25 26 Check result with Income Statement: 27 28 29 30 31 32 33 34 25 Selling & Admin. budget Income Statement CVP Income Statement 1 1 1 1 1 16 Cash Budget Worksheets Balance Sheet + Ready EU 900 File Home Insert Page Layout Formulas Data Review View Help Acrobat Tell me what you want to do Share AutoSum * Cut Copy Autosum Calibri 11 ab Wrap Text General Normal Bad AY O RA Fill - Paste B I U C. % ) Merge & Center A .0 .00 Format Painter .00 >.0 Neutral Insert Delete Format Conditional Format as Good Formatting Table Styles Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells A22 fie Financing A B C D E F G H 1 J K L M N O Q R S T U v w X Y 1 Don't use marking cells Max. Points Group Points 4 Year 21 2 2 2 21 2 2 2 2 2 2 3 4 CASH BUDGET 5 for the year ending 31st December 2021 6 1 2 3 7 Beginning cash balance 8 Add: Receipts 9 Collections from Customers 10 Total receipts: 11 Total available cash: 12 Less: Disbursements 13 Direct Materials 14 Direct Labour 15 Manufacturing Overhead 16 Selling and Administrative expenses 17 Purchase of a Machine 18 Income Tax expense 19 Dividends 20 Total disbursements: Excess (deficiency) of available cash over 21 cash disbursements 22 Financing 23 Add: Borrowings 24 Less: Repayments (including interest) 25 Ending cash balance 26 27 Notes: 28 Minimum cash balance: $10,000 29 Interest on loans to be paid in Quarter 4 30 Taxation amount taken from the Income Statement 31 32 33 34 35 36 Selling & Admin. budget Income Statement 2 2 2 2 2 2 32 01 CVP Income Statement Cash Budget Worksheets Balance Sheet + 1 Readu 010 + Slidle AutoSum Calibri 11 A A III ab Wrap Text General Normal Bad AY % Cut E Copy Format Painter 1 Insert Delete Format Fill - Paste BLU- A = = . C.% Merge & Center .00 Neutral Conditional Format as Good Formatting Table- Styles Clear Sort & Find & Filter - Select - Editing Clipboard Font Alignment Number Cells N26 X fic A B D E F G H | J M N o Q K Don't use marking cells Max. Points Group Points mo Receipts by Quarter Qtr 2 Qtr 3 2022 Qtr 1 Qtr 4 1 1 1 1 1 1 2. 3 Schedule of receipts from customers 4 5 Cash Sales Credit Sales Qtr 1 6 Accounts Receivable - 31st Dec 2020 7 First Quarter 8 Second Quarter 9 Third Quarter 10 Fourth Quarter 11 Total Payments:- 12 13 Cash Sales % 14 Credit Sales % 15 Collection of Credit sale in the quarter of the sale 16 Collection of Credit sale in the following quarter 17 18 Schedule of expected payments for Direct materials 19 20 Purchases Qtr 1 21 Accounts Payable - 31st Dec 2020 22 First Quarter 23 Second Quarter 24 Third Quarter 25 Fourth Quarter 26 Total Payments:- 27 28 Purchases paid in cash in the same quarter 29 Purchases paid in the following quarter 20 Selling & Admin. budget Income Statement CVP Income Statement Payments by Quarter Qtr 2 Qtr 3 2022 Qtr 1 Qtr 4 1 1 1 1 1 1 1 1 14 n Cash Budget Worksheets Balance Sheet + 1 nond File Home Insert Page Layout Formulas Data Review view Help Acrobat Tell me what you want to do Share AutoSum Calibri - 11 - ab Wrap Text General Normal Bad O RA ND * Cut 9 Copy Format Painter Fill - Paste A- BIU- C% Insert Delete Format Merge & Center - .0 .00 .00.0 Neutral Conditional Format as Good Formatting Table Styles Clear Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells P27 fr D E F G . 1 j K M Z o P. R S T U Max. Points Group Points 1 1 1 1 1 1 1 1 A B 4 Balance Sheet at 31st December 2021 5 Assets $ $ 6 Current Assets 7 Cash 8 Accounts receivable 9 Finished goods inventory 10 Raw materials inventory 11 Total Current Assets: 12 13 Property, Plant & equipment 14 Less: Accumulated depreciation 15 Total assets: 16 17 Liabilities and Shareholders Equity 18 Liabilities 19 Accounts payable 20 Bank Loan 21 Shareholders Equity 22 Common Stock 23 Retained Earnings 24 Total Shareholders Equity: 25 Total Liabilities and Shareholders Equity 26 27 28 Statement of Retained Earnings as at 31st December 2021 29 Opening balance 30 Net Income 31 32 Less Dividends 33 Closing balance 34 25 Income Statement CVP Income Statement 1 1 1 1 1 I 1 1 1 1 1 Cash Budget Worksheets Balance Sheet + Ready 90% File Home Insert Page Layout Formulas Data Review View Help Acrobat Tell me what you want to do Share AutoSum Calibri - 11 ab Wrap Text General Normal Bad 1 470 & Cut E Copy Format Painter Fill - Paste = % Merge & Center 60 ,00 .000 Neutral Insert Delete Format Conditional Format as Good Formatting Table Styles Clear - Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells P27 for D E F G H 1 J K L N 0 P Q R S T U V w X Y 1 1 1 1 1 1 B 24 Total Shareholders Equity: 25 Total Liabilities and Shareholders Equity 26 27 28 Statement of Retained Earnings as at 31st December 2021 29 Opening balance 30 Net Income 31 32 Less Dividends 33 Closing balance 34 35 36 Recreated from information provided: 37 Balance Sheet at 31st December 2020 38 Assets $ $ 39 Current Assets 40 Cash 41 Accounts receivable 42 Finished goods inventory 43 Raw materials inventory 44 Total Current Assets: 45 46 Property, Plant & equipment 47 Less: Accumulated depreciation 48 Total assets: 49 50 Liabilities and Shareholders Equity 51 Liabilities 52 Accounts payable 53 Shareholders Equity 54 Common Stock 55 Retained Earnings 56 Total Shareholders Equity: 57 Total Liabilities and Shareholders Equity 58 59 60 / Income Statement CVP Income Statement 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 24 01 Cash Budget Worksheets Balance Sheet + Ready 1 + 80%