I have completed most of the project but I need help with the Cash Budget, COGS Calculation using FIFO, Budgeted Income Statement, and Budgeted Balance Sheet. The cells in yellow require formulas so please include those, the ones in pink do not require formulas. Thank you!

INFORMATION FOR N-TET CORP. N-TET Corp. is a company that sells a highly specialized product, an advanced AI system and astronavigation recursive analyzer module. A small German engineering firm produces the item exclusively for N-TET Corp.; Therefore, N-TET Corp. has no manufacturing related costs. PRODUCT COSTS In Nov of 2021, each module costs N-TET Corp. $44,250. Per an existing contract, the cost of each unit is scheduled to increase by 2% on May 1, 2022. Rare earth minerals are used in production of the item. Analysis has shown that there will be a shortage of these rare earth minerals due to a spike in their demand from production of electric vehicles. The cost is anticipated to increase by an additional 40% on Sept 1, 2022. PRODUCT SALES N-TET Corp. sells each Module for $92,850 per unit. Projected Sales units: Year 2021 Month Nov Dec Jan Amt 32 48 27 45 68 2022 Feb 90 Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan 82 76 93 88 66 75 92 65 52 2023 To offset increasing costs of modules, the company plans to raise the sales price to $132,750 per unit beginning Sept 1, 2022. The sales forecast (i.e., estimated sales in units) takes this price increase into account. CREDIT SALES Monthly sales are 16% cash sales, 84% credit sales. 58% of credit sales are collected in the month of sale, 37% are collected the following month, and 4% are collected the 2nd month after sale. The remaining receivables are deemed uncollectible at the end of the 2nd month after sale. Bad debts are written off in the month the debt is deemed uncollectible PRODUCT INVENTORY The firm's policy regarding inventory is to maintain their stock (i.e. have in ending inventory) at 15% of the forecasted sales in units for the next month. N-TET Corp. uses the first-in, first-out (FIFO) method in accounting for inventories. Page 2 of 4 purchase with the remaining 55% paid the 45% of the inventory purchases are paid in the month following month. EQUIPMENT A Note payment of $2,300,000 for facilities and equipment previously purchased is due in January, then payments of $1,140,000 per month February through October 2022. There are no Note Payables at the end of 2022. OPERATING EXPENSES Monthly Cash expenses are paid when incurred Salary and Wage Exp Sales Commissions Exp Rent Exp Gen & Admin Variable Exp Supplies Exp Gen & Admin Fixed Exp Gen & Admin Depreciation Exp $261,000 4% of sales revenue $62,500 7% of sales revenue $18,500 $170,000 $260,000 (Cash expenses are paid when incurred) CASH MANAGEMENT The company must maintain a minimum cash balance of $3,500,000. Borrowing can make up shortfalls. For simplicity, assume that the bank will only lend (and accept repayments) in $10,000 increments. (Ignore interest on the loans, but minimize the amount borrowed and pay off any loans as soon as possible.) DIVIDENDS Dividends of $14,920,000 are paid in Nov. BALANCE SHEET Balance Sheet balances as of December 31, 2021: Cash $ 2,575,000 Accounts Receivable $ 1,697,149 Supplies* $ 25,000 Merchandise Inventory $ 179,213 Buildings and Equipment* $ 26,850,900 Accumulated Depreciation $ 2,520,000 Accounts Payable $ 1,091,537 Notes Payable $ 12,560,000 Capital Stock* $ 8,000,000 Retained Earnings $7,155,725 *same balance in 2022 CHECK FIGURES Cash Collections Budget Total Cash Receipts for January: Total Cash Receipts, Total 2022: Bad Debt Exp for January: Bad Debt Exp, Total 2022: $ 3,107,504 $ 89,867,720 $ 24,958 $ 663,410 Purchase Budget Cost of Purchases for Dec 2022: $ 3,984,066 Operating Expense Budget Cash disbursements for Op Exp for Dec $ 1,461,163 Cash Budget Total cash available, Summary $ 92,442,720 Total Cash Disbursements, Summary: $ 87,119,955 Ending Cash Balance for Dec: $ 5,322,765 Budgeted Income Statement Gross Profit, Total: Cost of Goods Sold Total Net Income, Total: $ 48,272,183 $ 44,118,967 $ 28,181,746 Budgeted Balance Sheet Total Assets Dec 2022: $ 30,608,708 INFORMATION FOR N-TET CORP. N-TET Corp. is a company that sells a highly specialized product, an advanced AI system and astronavigation recursive analyzer module. A small German engineering firm produces the item exclusively for N-TET Corp.; Therefore, N-TET Corp. has no manufacturing related costs. PRODUCT COSTS In Nov of 2021, each module costs N-TET Corp. $44,250. Per an existing contract, the cost of each unit is scheduled to increase by 2% on May 1, 2022. Rare earth minerals are used in production of the item. Analysis has shown that there will be a shortage of these rare earth minerals due to a spike in their demand from production of electric vehicles. The cost is anticipated to increase by an additional 40% on Sept 1, 2022. PRODUCT SALES N-TET Corp. sells each Module for $92,850 per unit. Projected Sales units: Year 2021 Month Nov Dec Jan Amt 32 48 27 45 68 2022 Feb 90 Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan 82 76 93 88 66 75 92 65 52 2023 To offset increasing costs of modules, the company plans to raise the sales price to $132,750 per unit beginning Sept 1, 2022. The sales forecast (i.e., estimated sales in units) takes this price increase into account. CREDIT SALES Monthly sales are 16% cash sales, 84% credit sales. 58% of credit sales are collected in the month of sale, 37% are collected the following month, and 4% are collected the 2nd month after sale. The remaining receivables are deemed uncollectible at the end of the 2nd month after sale. Bad debts are written off in the month the debt is deemed uncollectible PRODUCT INVENTORY The firm's policy regarding inventory is to maintain their stock (i.e. have in ending inventory) at 15% of the forecasted sales in units for the next month. N-TET Corp. uses the first-in, first-out (FIFO) method in accounting for inventories. Page 2 of 4 purchase with the remaining 55% paid the 45% of the inventory purchases are paid in the month following month. EQUIPMENT A Note payment of $2,300,000 for facilities and equipment previously purchased is due in January, then payments of $1,140,000 per month February through October 2022. There are no Note Payables at the end of 2022. OPERATING EXPENSES Monthly Cash expenses are paid when incurred Salary and Wage Exp Sales Commissions Exp Rent Exp Gen & Admin Variable Exp Supplies Exp Gen & Admin Fixed Exp Gen & Admin Depreciation Exp $261,000 4% of sales revenue $62,500 7% of sales revenue $18,500 $170,000 $260,000 (Cash expenses are paid when incurred) CASH MANAGEMENT The company must maintain a minimum cash balance of $3,500,000. Borrowing can make up shortfalls. For simplicity, assume that the bank will only lend (and accept repayments) in $10,000 increments. (Ignore interest on the loans, but minimize the amount borrowed and pay off any loans as soon as possible.) DIVIDENDS Dividends of $14,920,000 are paid in Nov. BALANCE SHEET Balance Sheet balances as of December 31, 2021: Cash $ 2,575,000 Accounts Receivable $ 1,697,149 Supplies* $ 25,000 Merchandise Inventory $ 179,213 Buildings and Equipment* $ 26,850,900 Accumulated Depreciation $ 2,520,000 Accounts Payable $ 1,091,537 Notes Payable $ 12,560,000 Capital Stock* $ 8,000,000 Retained Earnings $7,155,725 *same balance in 2022 CHECK FIGURES Cash Collections Budget Total Cash Receipts for January: Total Cash Receipts, Total 2022: Bad Debt Exp for January: Bad Debt Exp, Total 2022: $ 3,107,504 $ 89,867,720 $ 24,958 $ 663,410 Purchase Budget Cost of Purchases for Dec 2022: $ 3,984,066 Operating Expense Budget Cash disbursements for Op Exp for Dec $ 1,461,163 Cash Budget Total cash available, Summary $ 92,442,720 Total Cash Disbursements, Summary: $ 87,119,955 Ending Cash Balance for Dec: $ 5,322,765 Budgeted Income Statement Gross Profit, Total: Cost of Goods Sold Total Net Income, Total: $ 48,272,183 $ 44,118,967 $ 28,181,746 Budgeted Balance Sheet Total Assets Dec 2022: $ 30,608,708