Questions:
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.
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.
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.
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
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
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
Read less
