please show solutions in excel
B D H 1 L M E F G You have recently been hired as a new managerial accountant for Organic Life Manufacturing Inc.. The company manufactures and distributes to retailers across North America a patented nutrition drink in four tasty flavours. You have been asked by the company Controller, Emily Seguin, to complete the following planning and control requirements while 1 she is away at an industry conference next week. 2 Required: 3 Based on the sales assumptions provided by Emily in the Assumptions worksheet, as well as the remaining assumptions provided to you in Budgeting Assignment Instructions you have been asked to 4 complete the 2022 master budget which will allow further cost-volume-profit analysis upon Emily's return. 5 ASSUMPTIONS 6 Organic Life Manufacturing Inc. Balance Sheet As at December 31, 2021 9 Assets Liabilities & Shareholders Equity 7 8 10 11 12 13 $ $ Current Liabilities Accounts Payable Income taxes payable Bank loan payable 14 38,000 64,000 Cost per unit 5,100 $0.03 13,350 $8.90 120,450 15 170,000 grams 1,500 cases 32,100 6,200 15,000 53,300 16 17 Current Assets Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory Total current assets Land, buildings and equipment Land Buildings and equipment Less: Accumulated Depreciation Total land, buildings and equipment Total assets 18 19 100,000 $ 20 21 860,000 180.000 Shareholders' Equity Common shares Retained Earnings Total shareholders' equity Total liabilities and shareholders' equity 680,000 780.000 $ 900.450 225,000 622,150 847,150 900,450 22 23 $ 24 25 Quarter 26 2022 2023 27 First Second Third Fourth First Second 28 A4 X fx complete the 2022 master budget which will allow further cost-volume-profit analysis upon Emily's return. A B C D E F G H K M 26 2022 2023 27 First Second Third Fourth First Second 28 29 30 Estimated Sales Units (cases of 24 Organic Life cans) Selling Price per unit 25,260 22,000 23,000 24,000 25,000 26,000 31 32 33 34 35 Collection of credit sales collected in the quarter of sale collected in the following quarter Accounts Receivable Dec. 31, 2021 will be collected in the first quarter of 2022 36 37 38 39 Desired Minimum Inventories Finished Goods Direct Materials of next quarter's sales in units of next quarter's Direct Materials (in grams) to meet production (in units) 40 41 42 43 44 45 Manufacturing Costs: Variable Costs Direct material Direct Labour Variable Manufacturing Overhead per gram 46 grams hours of Direct Labour hours per hour 47 48 49 50 per quarter 51 Fixed Costs Factory Rent Factory Insurance Supervisory Salaries Depreciation 52 53 54 55 Payment assumptions Direct Material Purchases 56 quarter of purchase A4 X fic complete the 2022 master budget which will allow further cost-volume-profit analysis upon Emily's return. A B D E F G H 1 K 51 52 Factory Insurance Supervisory Salaries Depreciation 53 54 55 56 Payment assumptions Direct Material Purchases quarter of purchase Direct Material Purchases quarter following the quarter of purchase Accounts Payable Dec. 31, 2021 will be paid in the first quarter of 2022 57 58 59 60 61 62 Direct Labour Variable Manufacturing Overhead Fixed Manufacturing Overhead Variable Selling & Administrative Fixed Selling & Administrative month expensed / incurred month expensed / incurred month expensed / incurred month expensed / incurred varies - see below 63 64 65 66 67 Selling & Administrative Costs: Variable: Sales Commissions 68 per unit sold 69 70 Expense per Quarter Cash Disbursements per Quarter Second Third 71 First Fourth 72 73 Fixed : Advertising Executive salaries Insurance Property taxes Depreciation 74 75 76 77 78 79 Cash & Financing: 80 Minimum Opening Cash Balance each quarter before interest payments on bank line of credit 81 Clipboard Font Alignment Number Styles A4 fic complete the 2022 master budget which will allow further cost-volume-profit analysis upon Emily's return. A B D F G H K 75 76 Property taxes Depreciation 77 78 Cash & Financing: 79 80 Minimum Opening Cash Balance each quarter before interest payments on bank line of credit 81 82 83 84 Financing Maximum Revolving Bank Line of Credit Line of Credit borrowings Line of Credit repayments annual interest rate Quarterly interest rate 85 assumed to occur, when necessary, at the beginning of the quarter assumed to occur, when possible, at the end of the quarter paid at the end of quarter based on the line of credit balance at the beginning of the quarter 86 87 88 Quarter of Payment Second Third 89 First Fourth 90 91 Other Purchases, Expenses and Payments Equipment Purchases Cash Dividends Income tax instalments/payments Income tax expense (refund) 92 93 94 95 of Quarterly Net Income before income tax expense 96 97 98 99 100 101 Schedule 1-Budgeting Assumptions - Selling Price per unit $ 20.30 Collection of credit sales collected in the quarter of sale Writing an Executive Summary - Oxfam Policy & Practice collected in the following quarter Accounts Receivable Dec. 31, 2021 will be collected in the first quarter of 2022 60% Desired Minimum Inventories Finished Goods Direct Materials 25% of next quarter's sales in units 20% of next quarter's Direct Materials (in grams) to meet production in Manufacturing Costs: Variable Costs Direct material Direct Labour Variable Manufacturing Overhead 80 grams $ 0.03 per gram 0.20 hours $ 20.00 per hour 2.50 of Direct Labour hours $ Fixed Costs Factory Rent Factory Insurance Supervisory Salaries Depreciation per quarter $ 13,375 $ 2.000 S 22.000 $ 10,000 Payment assumptions Direct Material Purchases 30% quarter of purchase Direct Material Purchases 70% quarter following the quarter of purchase Accounts Payable Dec. 31, 2021 will be paid in the first quarter of 2022 Direct Labour Variable Manufacturing Overhead Fixed Manufacturing Overhead month expensed / incurred month expensed / incurred month expensed / incurred Direct Material Purchases 30% quarter of purchase Direct Material Purchases 70% quarter following the quarter of purchase Accounts Payable Dec. 31, 2021 will be paid in the first quarter of 2022 Direct Labour Variable Manufacturing Overhead Fixed Manufacturing Overhead Variable Selling & Administrative Fixed Selling & Administrative month expensed / incurred month expensed / incurred month expensed / incurred month expensed / incurred Writing an Executive Summary - Oxfam Policy & Practice Selling & Administrative Costs: Variable: Sales Commissions $ 1.20 per unit sold Expense per Fixed : Advertising Executive salaries Insurance Property taxes Depreciation Quarter $22.000 60,000 8.000 First $22.000 60.000 32.000 0 Cash Disbursements per Quarter Second Third $22,000 $22.000 60.000 60,000 0 0 0 9,100 Fourth $22.000 60,000 0 4,550 9,100 2,000 Cash & Financing: Minimum Opening Cash Balance each quarter $ 20,000 before interest payments on bank line of credit Financing: Maximum Revolving Bank Line of Credit Line of Credit borrowings Line of Credit repayments annual interest rate Quarterly interest rate $200.000 assumed to occur, when necessary, at the beginning of the quarter assumed to occur, when possible, at the end of the quarter 6% paid at the end of quarter based on the line of credit 1 1.50% Quarter of Payment First Second Third Fourth $80.000 SO SO so 12,000 12,000 12.000 30,000 30,000 30,000 30.000 25% of Quarterly Net Income before income tax expense Other Purchases, Expenses and Payments Equipment Purchases Cash Dividends Income tax instalments/payments Income tax expense (refund) 12.000 prime
