Pac GIZMOS, INC. Gizmos, Inc. incorporated and will begin operations on January 1, 2022. Its primary business is the manufacture and sale of gadgets. Because cash resources are limited, the company anticipates the need to have access to capital during the first year of operations and seeks to establish a line of credit with a local bank. The bank requires a complete operating and cash budget and pro-forma financial statements for 2022 as part of the loan application. The following information and data are to be used in preparing the budget. 1) Gizmos, Inc. is a closely-held corporation. The original owners will invest an initial $250,000 (assume the initial cash funding occurs on January 1, 2022) to establish the corporation and are the only shareholders of the company. 2) Production equipment totaling $48,000 will be purchased on January 2, 2022 and put into immediate use. The equipment has an expected useful life of five years, with no salvage value. It will be depreciated using straight-line depreciation method. 3) The sole product is the Standard Gizmo. Each unit requires four pounds of raw material. The cost of the raw material is $0.50 per pound. It takes six minutes of direct labor to produce one gadget. Direct labor employees are paid $18.00 per hour. 4) Monthly fixed and variable overhead and selling & administrative (S&A) expenses are estimated below. Fixed manufacturing overhead is allocated on the basis of direct labor hours. VARIABLE Manufacturing overhead (including depreciation) 30% of direct labor costs $20,000 Selling & Administrative Expenses $0.40 per unit sold $ 6,000 5) Gizmos will sell for $8.05 each. January sales are expected to be 8,000 units. Demand is expected to increase by 500 units per month until a level of 12,000 units per month is reached. 6) Gizmos, Inc.'s inventory policy establishes the following required monthly ending inventory levels: FIXED . Finished Goods: Maintain an ending inventory equal to one-half of the following month's expected sales. Raw Materials: Maintain an ending inventory quantity equal to three-fourths of the materials needed for the next month's production Work-in-Process: Assume the month-end inventory is always zero. . 7) All sales are made to established credit customers with credit terms of net 90 days. No sales discounts are offered for early payment. Twenty percent (20%) of payments for credit sales are expected to be received in the first month subsequent to the month of sale. Another sixty percent (60%) of payments are expected to be received in the second month after the sale. The remaining twenty percent (20%) of payments are expected to be received in the third month subsequent to the sale. (As an example, if January sales are $10,000, the company expects to receive payments of $2,000, $6,000 and $2,000 in February, March and April, respectively.) 8) Gizmos, Inc. has a credit arrangement with its raw materials vendor and purchases all materials on account with a basic invoice term of net 60 days. The company receives NO cash discounts for early payment of invoices. Gizmos pays 60% of its raw materials purchases in the month of purchase. The remaining 40% is paid in the first month subsequent to the purchase. 9) All other operating expenses will be paid in the month incurred. 10) The income tax rate is 24%. Estimated tax payments are made to the US Treasury on the last day of each quarter. Each quarterly payment is $5,000. Any balance due at year-end is recorded as a liability on the balance sheet. If taxes have been overpaid, the estimated refund is recorded as a receivable on the balance sheet. 11) The company requires a minimum ending monthly cash balance of $2,000 which must be reflected in the cash budget. NOTE: This assignment has been modified such that Gizmos, Inc. will have sufficient cash funding at the end of each month. NO BORROWING will be required. You do not have to build a financing section in your budget. We will do a separate problem (easier!) to practice financing decisions. Page ASSIGNMENT DETAILS: Create the 2022 monthly master budgets for Gizmos, Inc. in Microsoft Excel. The elements of the master budget Include both the operating budget and the financial budget: . . . . . . Operating budget-all schedules by month and in total for the year Sales Revenue budget Production budget Ending inventor budget Direct materials budget Direct labor costs budget Manufacturing overhead budget Cost of goods sold budget Selling and administrative expenses budget Pro-forma income statement for the year ended December 31, 2022 Financial budget Capital expenditures, by month and in total for the year. Cash budget, by month and in total for the year. Pro-forma balance sheet at December 31, 2022 Pro-forma statement of owners' equity for the year ended December 31, 2022 Pro-forma statement of cash flows for the year ended December 31, 2022 1. Prepare individual monthly budgets but monthly financial statements are not required. 2. I created a workbook template as a suggested starting point. You may modify the workbook in any way to accommodate your assignment (or you can create your own unique worksheet). Refer to the handout on working with spreadsheets for additional Information on spreadsheet design and functions you may find useful. Label the budget carefully for units and dollars. Use the dollar or comma format with two decimal places for dollars ($1,500.00 or 1,500.00) and use no decimals for units (9,000), 3. The workbook template has columns for January and February 2022 only to accommodate the calculations required for December 2022. Do not complete budgeting for these months. Use the 2022 columns only as a source of data for sales, inventory and production quantities. 4. The cash budget must reflect the actual amounts of cash overhead costs. G H 1 3 27 W CY NIL INDUSTRI 8000 500 12000 8.05 $ 8 INPUTS 9 First Month Sales Quantity (units) 10 Monthly Sales Increment: 11 Maximum Sales Quantity per Month 12 Sales Price per gadget 13 14 Credit Sales percent of Total Sales: 15 Credit Sales Collection - Month of Sale (percent): 16 Credit Sales Collection Month Following Sale (percent): 17 Credit Sales Collection - Second Month Following Sale (percent); 98 Credit Sales Collection - Third Month Following Sale (percent): 10 20 Finished Goods Ending Inventory (percent of Following Month's Sales Quantity): 21 Materials Ending Inventory (percent of fraction of Following Month's Materials Usage): 22 Finished Goods Beginning inventory (January 2 Materials Beginning Inventory (anuary 100% 0% 20% 60% 20% SON 75% 0% 05 4 S 0.50 100% 50% 40% 0.1 $ Direct Materials Quantity per padget(pounds) Direct Materials Price per Pound: 27 23 Credit Purchases percent of Total Materials Purchases: 2 Credit Purchases Payments Month of Purchase (percent): 30 Credit Purchases Payments - Month following Purchase (percent); 31 22 Direct Labor Hours per gadget: 33 Direct Labor Wage Rate per Hour Variable Overhead Rate (percent of Direct Labor Cost) Foed Overhead per Month 37 Variable Selling and Administrative Rate (per unit): 30 FSelling and Administrative Expense 30 40 Capital Budget Expenditures 41 Usta Coc ed Residual Value of Coal Asie Depreciation pense per month 18.00 30% $ 20,000.00 $ 0.40 $ 6,000.00 $ 48,000.00 5 0 $ 800.00 + o come Quarticoloxament 24% $ 5,000.00 So beginnings Balea 51 Minimum Cash Balance $25,000.00 $ 2,000.00 Summary and Index Inputs 2022 Operating Budget 2022 Financial Rets Pac GIZMOS, INC. Gizmos, Inc. incorporated and will begin operations on January 1, 2022. Its primary business is the manufacture and sale of gadgets. Because cash resources are limited, the company anticipates the need to have access to capital during the first year of operations and seeks to establish a line of credit with a local bank. The bank requires a complete operating and cash budget and pro-forma financial statements for 2022 as part of the loan application. The following information and data are to be used in preparing the budget. 1) Gizmos, Inc. is a closely-held corporation. The original owners will invest an initial $250,000 (assume the initial cash funding occurs on January 1, 2022) to establish the corporation and are the only shareholders of the company. 2) Production equipment totaling $48,000 will be purchased on January 2, 2022 and put into immediate use. The equipment has an expected useful life of five years, with no salvage value. It will be depreciated using straight-line depreciation method. 3) The sole product is the Standard Gizmo. Each unit requires four pounds of raw material. The cost of the raw material is $0.50 per pound. It takes six minutes of direct labor to produce one gadget. Direct labor employees are paid $18.00 per hour. 4) Monthly fixed and variable overhead and selling & administrative (S&A) expenses are estimated below. Fixed manufacturing overhead is allocated on the basis of direct labor hours. VARIABLE Manufacturing overhead (including depreciation) 30% of direct labor costs $20,000 Selling & Administrative Expenses $0.40 per unit sold $ 6,000 5) Gizmos will sell for $8.05 each. January sales are expected to be 8,000 units. Demand is expected to increase by 500 units per month until a level of 12,000 units per month is reached. 6) Gizmos, Inc.'s inventory policy establishes the following required monthly ending inventory levels: FIXED . Finished Goods: Maintain an ending inventory equal to one-half of the following month's expected sales. Raw Materials: Maintain an ending inventory quantity equal to three-fourths of the materials needed for the next month's production Work-in-Process: Assume the month-end inventory is always zero. . 7) All sales are made to established credit customers with credit terms of net 90 days. No sales discounts are offered for early payment. Twenty percent (20%) of payments for credit sales are expected to be received in the first month subsequent to the month of sale. Another sixty percent (60%) of payments are expected to be received in the second month after the sale. The remaining twenty percent (20%) of payments are expected to be received in the third month subsequent to the sale. (As an example, if January sales are $10,000, the company expects to receive payments of $2,000, $6,000 and $2,000 in February, March and April, respectively.) 8) Gizmos, Inc. has a credit arrangement with its raw materials vendor and purchases all materials on account with a basic invoice term of net 60 days. The company receives NO cash discounts for early payment of invoices. Gizmos pays 60% of its raw materials purchases in the month of purchase. The remaining 40% is paid in the first month subsequent to the purchase. 9) All other operating expenses will be paid in the month incurred. 10) The income tax rate is 24%. Estimated tax payments are made to the US Treasury on the last day of each quarter. Each quarterly payment is $5,000. Any balance due at year-end is recorded as a liability on the balance sheet. If taxes have been overpaid, the estimated refund is recorded as a receivable on the balance sheet. 11) The company requires a minimum ending monthly cash balance of $2,000 which must be reflected in the cash budget. NOTE: This assignment has been modified such that Gizmos, Inc. will have sufficient cash funding at the end of each month. NO BORROWING will be required. You do not have to build a financing section in your budget. We will do a separate problem (easier!) to practice financing decisions. Page ASSIGNMENT DETAILS: Create the 2022 monthly master budgets for Gizmos, Inc. in Microsoft Excel. The elements of the master budget Include both the operating budget and the financial budget: . . . . . . Operating budget-all schedules by month and in total for the year Sales Revenue budget Production budget Ending inventor budget Direct materials budget Direct labor costs budget Manufacturing overhead budget Cost of goods sold budget Selling and administrative expenses budget Pro-forma income statement for the year ended December 31, 2022 Financial budget Capital expenditures, by month and in total for the year. Cash budget, by month and in total for the year. Pro-forma balance sheet at December 31, 2022 Pro-forma statement of owners' equity for the year ended December 31, 2022 Pro-forma statement of cash flows for the year ended December 31, 2022 1. Prepare individual monthly budgets but monthly financial statements are not required. 2. I created a workbook template as a suggested starting point. You may modify the workbook in any way to accommodate your assignment (or you can create your own unique worksheet). Refer to the handout on working with spreadsheets for additional Information on spreadsheet design and functions you may find useful. Label the budget carefully for units and dollars. Use the dollar or comma format with two decimal places for dollars ($1,500.00 or 1,500.00) and use no decimals for units (9,000), 3. The workbook template has columns for January and February 2022 only to accommodate the calculations required for December 2022. Do not complete budgeting for these months. Use the 2022 columns only as a source of data for sales, inventory and production quantities. 4. The cash budget must reflect the actual amounts of cash overhead costs. G H 1 3 27 W CY NIL INDUSTRI 8000 500 12000 8.05 $ 8 INPUTS 9 First Month Sales Quantity (units) 10 Monthly Sales Increment: 11 Maximum Sales Quantity per Month 12 Sales Price per gadget 13 14 Credit Sales percent of Total Sales: 15 Credit Sales Collection - Month of Sale (percent): 16 Credit Sales Collection Month Following Sale (percent): 17 Credit Sales Collection - Second Month Following Sale (percent); 98 Credit Sales Collection - Third Month Following Sale (percent): 10 20 Finished Goods Ending Inventory (percent of Following Month's Sales Quantity): 21 Materials Ending Inventory (percent of fraction of Following Month's Materials Usage): 22 Finished Goods Beginning inventory (January 2 Materials Beginning Inventory (anuary 100% 0% 20% 60% 20% SON 75% 0% 05 4 S 0.50 100% 50% 40% 0.1 $ Direct Materials Quantity per padget(pounds) Direct Materials Price per Pound: 27 23 Credit Purchases percent of Total Materials Purchases: 2 Credit Purchases Payments Month of Purchase (percent): 30 Credit Purchases Payments - Month following Purchase (percent); 31 22 Direct Labor Hours per gadget: 33 Direct Labor Wage Rate per Hour Variable Overhead Rate (percent of Direct Labor Cost) Foed Overhead per Month 37 Variable Selling and Administrative Rate (per unit): 30 FSelling and Administrative Expense 30 40 Capital Budget Expenditures 41 Usta Coc ed Residual Value of Coal Asie Depreciation pense per month 18.00 30% $ 20,000.00 $ 0.40 $ 6,000.00 $ 48,000.00 5 0 $ 800.00 + o come Quarticoloxament 24% $ 5,000.00 So beginnings Balea 51 Minimum Cash Balance $25,000.00 $ 2,000.00 Summary and Index Inputs 2022 Operating Budget 2022 Financial Rets