Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please help me finish the rest of this. Cells in yellow require formulas so please include those. INFORMATION FOR N-TET CORP. N-TET Corp. is a
Please help me finish the rest of this. Cells in yellow require formulas so please include those.
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 Arial 10 AA General Insert v = = = Conditional Formatting 1 Format as Table Cell Styles 27-O Y 5x Delete Paste B B I U WA $ % ) 98 99 Sort & Filter Format Find & Select Analyze Data Sensitivity 0151 x fx B c D E F F H J L M N 0 P P K Sales Budget For Year Ending Dec 31, 2022 Nov 21 Dec 12 32 48 92,890.00 $ 92,250.00 2.971.2005 4,156,00 Jan 22 27 92.950.00$ 2.506,950 $ Feb 22 Ma-122 Apr 22 45 90 92,800.00 $ 92,050.00 $92,350.00 $ 4,178 2503 6.313.800 8 358.500 May 22 82 92,850.00 $ 7.613 700'S Jun 22 76 92.950.CO$ 7.058.400 Jul 22 93 92,800.00 $ 8,635.050's Aug 22 Sep 22 Oct 22 Nov 22 Dec 22T 88 66 75 92 65 92.950.00 122,750.00 $ 132,750.00 $132.750.00 $ 132,780.00 8.170.2008 781,500 9.95 250'S 12 213.000 $ 8.628.750 16% 84% 6 7 8 Budged units 9 Selina price per unit 10 Total Sales 11 12 Cash Sale % 13 Credit Sales ' 14 15 Cash Sales 16 Credit Sales 17 Total SAIA $ 18 19 20 Ourrent month NR Collections 21 1 manthriar A/R Calactions 2 months prior NR Collections 23 Uncolectible 24 25 475,2028 2.495.000 2.971 2005 713,033'S 3,743,712 4,456 800S 401,1125 2.105.838 2 508,000S 668.520'$ 3.509.30 4,178,250 1,010,208$ 5.303.5% 6,313,800 $ 1,337.040$ 7,019,480 8.356,500 1,218, 192's 8.895.503" 7,613.700S 1.120,06ES 5.927,544 7 056,600 1,381,60B's 7,253.442 8,635 050's 1,307,328 s 8.883,472 8,170,000 $ 1,401.840 7359.880 8.761.500 5 1,000,000's 3.363.250" 9,956 250 g 1.954,080 10.258.920 12 213,000 1,380.000 7,248.150 8,628,750 5856 37% 156 N-TET Corp. Cash Collections Budget For Year Ending Dec 31, ZOZZ ZY 28 F Jan 22 401,112 1.221 386 1,385,173 99.832 3,107,504 Fab'22 B68, 520 2,095,643" 779,150 149,743 3,633,072'S Mar 22 1,010,208 3.076,083 1.299,000 14,234 5.419, 1269 Apr 22 1.337.000$ 4,071 PR7 2 1.962229 140 380 7,511.0145$ May 22 1.218,192 3,709,395 2.597,200 212,144 7,736,931 Jun 22 Jul 22 1,129.058 1,38 1,800's 4,206,006 2,335,334 2,190, 1917 280.778 7,214,148$ 3,087,610S 9.437.97 Aug 22 1,307,328 $ 3.980,814 2.693,774 237.102 F 8.209,017 $ Sep 22 1,401.8403 4,268.603 2,639.495 290.138 8,500.0655 Oct 22 Nov 22 1.593,000 1,954.0805 4,850, FS 5.950,174 2.722,074 2,094,403 294 388 9,441,298 $ 11,293,043 $ Dec 22 Annual Total 1,380,600 14.782,584 4,208 827 545,012,568 2,796,800 27.410,627 334.50 S 2.653.641 9,714,857 $ 89,867,720 F 255.820 274 510 24,950 $ 27,437 $ 21,068 $ 36,097 $ 53,036 $ 70,195$ 62,955 59,275$ TZ 534 $ 62,638 73,697 $ 83,5335 662,410 15%. 30 Current month wash Sales 31 Current month AIR Olections 3Z 1 month prior R Colcctions r 33 2 monthe prior AVR Colections 34 Total cos colections 35 36 Bad Debt Expense $ 37 38 Desired anding inventary % 39 40 40 41 42 43 44 45 Budged units 46 Add desired andrg inventory 47 Total unit noods 48 Loss Beginning inventary 49 Required Unit Purchases Budget + Nov 21 Feb 22 May 22 Oct 22 Dec 22 T A5 92 Dec 21 40 4 52 75 Jan 22 27 7. 34 4 45 10 F 55 F Mar 22 68 14 82 F 10 NETET Corp Purchase Budget For Year Ending Dec 31, 2022 Jun 22 Jul 22 Aug 22 76 93 88 14 13 10 90 as 11 13 79 92 85 Apr 22 90 12 102 02 32 11 r 937 127 Nov 22 92 10 102 404 141 14 99 Sep 22 66 14 F 77 10 67 104 14 F R9 11 79 73 10 83 09 31 Ready 1 ! B + 90% Arial 10 ~ AI General Insert [S 3 ab FC -O Conditional Formatting Format as Tablev Cell Styles v Ix Delete v Paste BI U V OA V $ % ) Sart & Filter Format v Find R Select Sensitivity Analyze Data 0151 Xfx B D E G H 1 J K L M N 0 P 51 Onat per unit oz Cost of Purchases Ps $ 44.250.00 1.984,613 44,290.00 1,314 225$ 44,250.00 $ 2.143,913 44,250.00 $ 2,155,025$ 44,250.00 $ 3,929,400 45.135.00 $ 3.660,449 $ 45,135.00 $ 3,045,354 $ 45,135.00 4,163,704 45 135.00 $ 2,822,935$ 63,189.00 4,206,779 63. 189.COS 4.900,307 63,189.00 $ 5,067 43$ 63,189.00 3.984,006 5 SS * Paid in Month of Purchase * Paid in Month After Purchase 45% 55% 57 NETET Corp. Schedule of Budgeted Cash Disbursements for Merchandise Purchases For Year Ending Dec 31, 2022 59 60 61 62 Ourrent purchase AIP Payments 63 1 month prior AJP Payments 64 Cash disbursements for merchandise purchases 65 "S F S Jan 22 501,101 S 1,091,587 1.882,938's Feb 22 964.781 722 24 1.687.5843 Mar '22 1.419,761 1.179,152 2.590.9136 Apr 22 May 22 1,788.230$ 1,647,202S 1,735,264 2,161,170 3.503.494 3.0003726 Jun 22 Jul 22 Aug 22 1,585,109 1.873. 887 1.720,321 2.013,247 2,290,037 3,808,656$ 3,623 612 44,010,368 Sep 22 Oct 22 1,915.107 2.206,138's 2.102.614 2.340.679 4,017.7453 4,545,817 S Nov 22 2.500.863 2.595,169 5.198,0815 Dec 22 Annual Total 1,792.830 $ 19.994,683 3,056.610 23,238,246 4,849,440$ 43,332,29 1,949 345 67 Fixed Operating expenses Salary and Wage Exp Fant Fro Supplies Exp Gen & Aurrin Fired ExD Gan & Admin Deprec.ion Exa Variable Operating Expenses Sales Commission Exp % of Revenue Gen & Admin Variable Exp % of Revenue 4% 7% $ 5 $ 3 $ 261,000 62,500 18,500 170,000 260,000 70 71 72 73 74 75 NTET Corp Operating Expense Budget For Year Ending Dec 31, 2022 F Feb 22 281.000 167.130 62.500 202.478 18.00 170.000 250.000 Oct 22 - 261.000 200,250 62.500 Jan 22 281,000 100,278 82,500 175,487 19,000 170.000 200,000 24.058 1.072,723 280.CCO EGE 939 77 78 79 Salary and Wage Exp 80 Sales Commission Exp 81 Rent Exp 82 Gen & Adwin Variable Exp 83 Supplies Exp 84 Gen & Adhin Fixed Exp 85 Gor & Admin Depreciation Exp 86 Bed Debt Exp 87 Total Opanating Exp 88 Less: Depreciation Exp noncash) 89 Lees: Bad Debt Exp (noncash) 90 Dash Disbursements for Operating Expenses 91 sz Note payment Equipment - Jan $ 93 Note payment Equipment Fob-Oul 5 94 Dividends - Now $ 96 Minimum Monthly Cash Budget s Mar '22 261,000 252,552 82,500 441,966 12,000 170,000 260,000 21.058 1,487,576$ 57 260,000 21.058 1,200,6185 Jun '22 281.000 282,264 82.500 403.62 19,000 170.000 260.000 Jul 22 261.0003 345.402 62 500 604.454 18.500 170.000 250.000 Apr 22 May 22 281.000$ 261.000's 334,260 304,548 62.500 62.500 584,955 582959 15.500 18,500 170.000 170,000 250,000 260,000 35.097 1.728,3125 ce 1,662 543's 250,000 200,000 35.097 59,038 1,431,215$ 1,349,507 S Aug 22 281.000 226,832 82,500 571,956 18,000 170,000 260,000 59,275 1.790,043 $ 260,000 59.275 1,410,788 Sep 22 281.000 350,460 62.500 613.305 19,500 170.000 280,000 F 72.534 1.800,299 $ 250,000 72.534 1,475,765 $ 18,500 170.000 260,000 68,835 1,995 R22S Nov 22 281,000 488,620 82,500 R54,910 19,000 170.000 260,000 73.627 2,189,027 $ 260.CCO F 73.597 1.855,430$ Dec 22 Annual Total 281.000 3.132.000 345. 150$ 3,695,646 62.500 750,000 604.013 6,467,381 16,500 222,000 170.000$ 2.040,000 250,000 $3,120,000 83.8335 883,410 1,804, 795 $ $ 20,090,437 250.000 3,120,000 83.833 3 683,410 1,461.163 $ 16,307,027 20.196 63.955 's r 1.61R, 421 786 1,260 045's 200.0 37.437" 971.60R'S 1,785,811 's F 250.000 63 55 1,461.856'$ 260,009 260.000 20.198 24.95 68.835 s 787,765 $ 1.288,2269 1,607,183's 2,300,000 1.140,000 14,520,000 3,500,000 Budget + Ready 15 90% X Arial 10 Insert ~ AP General Conditional Formatting aby ' : -- SX Delete Paste BIU Format as Table v Cell Styles $ % ) Y Sart & Filter Format v Find & Select Sensitivity Analyze Data 0151 7 B c D E F H . M N a N-TET Corp. Cash Budget For Year Endng Dec 31, 2022 FAh 22 Mar 22 Aar22 May 22 Jun 22 Ju '99 Aug 22 Sep 22 Oct 22 Jan 22 2,078,000 Nov 22 DAC: 22 Annus Summary $ 97 98 99 100 101 102 Cash balance, begrring 103 Add collection from customers 104 Total cash avalabla 105 Less disbursements: 106 Cash disbursements for merchandise purchases 107 Cash Disbursements for Operating Expenses 108 Note Payment Equipment 109 Dividends 110 Total cash disbursements 111 Excess of receipts aver disbursements 112 Finanorg 113 Borrowing-note 114 Repayments-note 115 Cash balance, andrg 116 117 112 119 120 421 Leg Inventory 422 123 Jan 124 Feb 125 Mar 126 Apr 127 May 129 Jun 129 Jul 190 Aug 131 Sep 132 Oct Nov 134 Dec 135 138 N-TET Corp Inventory Data COGS Calculation using FIFO Cost Assumption Purchases Purchases OOGS COGS Uhila 3 Units Beg Inventory Unile Ending Inventory Endrg Inventory Units 137 N-TET Corp Budgeted Income Statement For Year Ending Dec 31, 2022 Jan 22 Feb 22 Mar 22 Apr 22 May 22 Jun 22 Jul 22 Aug 22 Sep 22 Oct 22 Nov 22 Dec 22 Annual Total 139 140 141 Sales 142 Cast of goods sold Budget + Ready 11 -- 90% Insert v Arial Y X Lin y General y On y 10 ~ AF A A A OA, Conditional Formatting V Format as Table Cell Styles DX Delete v Paste ov $ % ni - Find & Format Sort & Filter v Sensitivity Select Analyze Data 0151 X fx B D E F H 1 J K L M N 0 Sec Oct Nov Dec 132 133 194 135 198 137 130 139 140 NTET Corp. Budgeted Income Statement For Year Ending Dec 31, 2022 Jan 22 Feb 22 Mar 22 Apr 22 May 22 Jun 22 Jul 22 Aug 22 Sep 22 Oct 22 Nov 22 Den 22 Annual Total 141 142 143 144 145 146 Sales Cost of goods sold Gross Profit Total Operating Exp Net Operating Income 148 N-TET Corp. Budsted Balance Sheet As of Dec 31, 2022 2022 2021 $ 25,000 $ 25,850,000 150 1511 152 Assets 159 Current Asses Cash 155 Accounts receivable 156 Supplies 157 Merchandise Inventory 158 plant and Equipment Bulidings and Equipment 180 Accumulated Depreciation 161 Total assets 162 162 Labilities and Equity 184 Accounts Payable 165 Notes Payable 86 09 167 Capital stock 168 Retained earnings 189 Total liabilities and equity 170 174 172 179 174 175 -7 Budget + S 8.000.000 Ready 11 El + 90%Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started