Preparing a Comprehensive 12-Month Budget for N-TET Corp

Notes and Tips

Required:  You need to prepare a comprehensive 12-month budget, including supporting schedules and a report for the period January 1, 2022 to December 31, 2022 for N-TET Corp.  This project includes a complete budget template that makes up the Master Budget.   
 
Notes and Tips 
1. All parts must be completed before I grade the project, but you can ask questions. 
2. The completed Master Budget must be uploaded to Blackboard. I will not accept email submissions. The budget must be turned in as an Excel workbook. It is fine if you open the template and use Google Sheets or some other spreadsheet software to complete it, as long as you convert it back to Excel before you upload it to Blackboard. If you do use some other software, make sure all your cells are still formulas. 
3. If you need to upload a corrected copy or make changes after uploading, Blackboard will allow you to upload it multiple times. I will only look at the last upload unless you tell me otherwise. 
 
4. The template I have prepared must be used as-is.
5. This project demonstrates your proper use of Excel (spreadsheets). You may only input a “hard number” into a pink cell. All yellow cells must be formulas (no numbers should be included within formula.

6.Every variable in the formula should use appropriate cell referencing instead of a number.).   
 7. I recommend constructing the formulas for one month and then copying the formulas over to the remaining months using copy/paste ‘fx only’. Try to keep cell formats as they currently are format; i.e. $, %, borders, are preset. 
 
8. The budget template and a copy of this instruction sheet are located on the course materials page under Project 2. Make sure you download the file as Excel and then open the file through Excel (so it doesn’t open in your web browser).  If you want to use Excel but do not have it, you can use the school computers which all do or you can also it is free for students (Microsoft Office). 
 
9. I am providing check figures for you to check your accuracy at different points. 
10.  The total column at the right of each table should be annual totals if that is appropriate. Some cells don’t make sense to have a total, such as the price per unit row. Another example is Cash disbursement total column: the first line beginning cash wouldn’t be total of all the beginning months, it would be the Jan 1st amount. Use logic to determine what amount should be reported in these total columns. NOTE: some budgets have 2021 months in them, DO NOT include those months in the Totals for 2022. 

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. 

Description of N-TET Corp.

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%.
 
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. 
 
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  
 
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. 45% of the inventory purchases are paid in the month of purchase with the remaining 55% paid the following month. 
 
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. 
 
Monthly Cash expenses are paid when incurred   
Salary and Wage Exp $261,000 
Sales Commissions Exp 4% of sales revenue 
Rent Exp $62,500 
Gen & Admin Variable Exp 7% of sales revenue 
Supplies Exp $18,500 
Gen & Admin Fixed Exp $170,000 
Gen & Admin Depreciation Exp $260,000 
 
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 aspossible.) 
 
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 

 
Cash Collections Budget 
 Total Cash Receipts for January: $   3,107,504 
 Total Cash Receipts, Total 2022: $ 89,867,720  
 Bad Debt Exp for January:  $        24,958 
 Bad Debt Exp, Total 2022:  $      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:   $ 48,272,183 
 Cost of Goods Sold Total  $ 44,118,967 
 Net Income, Total:    $ 28,181,746 
 Budgeted Balance Sheet 
Total Assets Dec 2022:    $ 30,608,708