ExcelCase-2 (1) - Excel Jake Innes JI File Home Insert Page Layout Formulas Data Review View Automate Help Share & Cut Calibri 11 VAA 29 Wrap Text General AutoSum AY LL [) Copy O Fill Paste BIULA TE E Merge & Center $ ~ % 9 68 98 Conditional Format as Insert Delete Format Sort & Find & Analyze Format Painter Formatting " Table Clear ~ Filter ~ Select ~ Data Clipboard Font Alignment Number Styles Cells Editing Analysis N21 X v fx D E G H J K L M N O P Q R S T U V w X Y Z AA AB AC AD AE AF AG AH Al Wayne Corporation Master Budget Project The company is preparing its budget for the coming year, 2022. The first step is to plan for the first quarter of that coming year. 2 The following information has been gathered from their managers. jacob innes 4 Sales Information gp9290 Period Units November, 2021 102,000 Actual December, 2021 91,000 Actual January, 2022 100,000 Planned February, 2022 101,000 Planned Grading guidelines are on the instructions 10 March, 2022 103,000 Planned 11 April, 2022 tab. 112,000 Planned 12 May, 2022 123,000 Planned 13 Unit selling price 30.70 14 15 Finished Goods Inventory Planning 16 The company likes to keep 15% of the next quarter's unit sales in finished goods ending inventory. 17 18 Accounts Receivable & Collections 19 Sales on Account 100% 20 Collections Activity 21 Month of Sale 80% 22 Month after Sale 20% 3 Balance at 12/31/21 $ 185,000.00 24 25 Materials Inventory Costs & Planning 26 Direct Materials Amount Used per Unit Cost 27 Ingredient 2 16 1.18 1b 8 The company likes to keep 12% of the material needed for the next month's production in raw materials ending inventory. 29 30 Accounts Payable & Disbursements 31 Purchases on Account 100% 32 Payment Activity $3 Month of Purchase 40% 34 Month after Purchase 60% 35 Balance at 12/31/21 S 120,000 36 37 Direct Labor & Costs Instructions Facts Student Solution Ready * Accessibility: Investigate 100% Type here to search USD/JPY -0.62% ~ 259 PM I 7/11/2023ExcelCase-2 (1) - Excel Jake Innes JI File Home Insert Page Layout Formulas Data Review View Automate Help Share & Cut Calibri 11 VA A 29 Wrap Text General AutoSum LL [) Copy AY O Fill Paste Format Painter BIUM A EE EEEE Merge & Center $ ~ % 9 68 98 Conditional Format as Insert Delete Format Sort & Find & Analyze Formatting ~ Table Clear Filter ~ Select ~ Data Clipboard Font Alignment Number Styles Cells Editing Analysis N21 X v fx B D E G H I J K L M N O P Q R S T U V w X Y Z AA AB AC AD AE AF AG AH 37 Direct Labor & Costs 38 Time per Unit Production 30 minutes Pay Rate/Hour $ 20.00 40 41 Manufacturing Overhead Costs 42 Variable costs per direct labor hour 43 Indirect materials $ 0.25 44 Indirect labor 0.35 45 Utilities 0.45 46 Maintenance 0.25 7 Fixed costs per month 48 Salaries S 42,000 49 Depreciation 16,80 50 Property taxes 2,675 51 Insurance 1,200 52 Janitorial 1,300 53 54 Selling and Administrative Costs 55 Variable costs per unit sold S 1,20 56 Fixed costs per month 57 Advertising 15,000 58 Insurance 14,000 59 Salaries 72,000 60 Depreciation 25,000 61 Other fixed costs 3.000 62 63 Income Taxes 64 Accrued on Monthly Net Income 45% rounded to nearest dollar 65 Amounts Accrued Q4 2021 paid January 2022 200,000 66 67 Cash and Financing Matters 68 Cash Balance, 12/31/2021 145,000 69 2022 Minimum Balance Required 795,000 70 71 Monthly Dividends 1.80 per share 72 Outstanding Shares 5,000 73 74 Line of Credit 75 Limit S 1.200.000 Instructions Facts Student Solution + Ready * Accessibility: Investigate 100% Type here to search USD/JPY -0.62% ~ 259 PM I 7/11/2023AN - GH ExcelCase-2 (1) - Excel Jake Innes JI Share File Home Insert Page Layout Formulas Data Review View Automate Help & Cut AutoSum Calibri 11 VAA 29 Wrap Text General AY O LL [) Copy Fill Paste EE EE Merge & Center $ ~ % 9 68 98 Conditional Format as Insert Delete Format Sort & Find & Analyze Format Painter BIULA Formatting ~ Table Clear Filter ~ Select ~ Data Cells Clipboard Font Alignment Number Styles Editing Analysis N21 1 X v fx B C D E G H I J K L M N O P Q R S T U V w X Y Z AA AB AC AD AE AF AG AH 61 Other fixed costs 3,000 62 63 Income Taxes 64 Accrued on Monthly Net Income 45% rounded to nearest dollar 65 Amounts Accrued Q4 2021 paid January 2022 $ 200,000 66 67 Cash and Financing Matters 68 Cash Balance, 12/31/2021 145,000 69 2022 Minimum Balance Required 795,000 70 71 Monthly Dividends S 1.80 per share 72 Outstanding Shares 5,000 73 74 Line of Credit 75 Limit 1,200,000 76 Borrowing Increment Required 1,000 77 Annual Interest Rate 10% 78 Borrowings occur on the First of Month 79 Repayments occur on the Last of Month 80 Interest accrues on the loan balance from the date of borrowing. Interest accrued is paid first with each repayment. 81 82 Additional Item 83 Fixed Asset Purchase S 361,000 84 January 85 86 87 88 89 90 Instructions Facts Student Solution 100% Ready * Accessibility: Investigate 2:39 PM Type here to search 840F Mostly sunny 7/11/2023