I need help with creating this Master Budget

Your task is to make a master budgeting template in a spreadsheet. Obtain the pre- formatted spreadsheet (master.x]s). The information necessary for making the master budget is given in Part I of the spreadsheet. You need to enter formulas in Part II. Once you enter all required formulas, you can use this master budget template for any budgeting situation. Instructions Enter participants' names and team number in designated cells at the beginning of the spreadsheet. In the master budget spreadsheet, you need to complete Part II by entering formulas in relevant colored cells - in all yellow cells like the following Make sure you enter right formulas, not numbers. After completion, change some numbers in Part I, and check whether your budgets are changed accordingly. Do not enter any numbers. Formulas should not contain any numbers. For example, let's assume the following. This is a hypothetical example. Cell Number Description A10 $100,000 Amount of Borrowing B30 0.05 Interest Rate You need to Amount of Annual Interest determine! Expense In cell KI, enter the formula only, "+Al*B30" or "=Al*B30"). Do not enter *10000*0.3", "+Al*0.3", or #100000*B30". . If you just need to copy a number from a particular cell, put the formula "+cell number" or "=cell number"_For example, assume that in cell A50, you have to copy the number in cell B26. Then you have to enter "+B26" or "=B26" in cell A50.In Part I, (c), the interpretation is as follows: To make 1 unit of the product, 2 1bs of direct materials are needed (1 1bs of the material costs $1.50). 0.5 hour of direct labor is needed (1 hour of labor costs $6). Variable overhead would be applied at $1.40 per direct labor hour. (So, 1 unit of the product includes $1.40* 0.5 =$0.70 for variable overhead) Including fixed overhead, unit manufacturing cost is $7.00 (To manufacture 1 unit of the product, it costs $7.00). Formulas have to be entered for each month (Jan., Feb., and Mar.) and for the quarter. -In Sections (3) and (4) of the spreadsheet file, you need to put formulas for April and May too, for some items (Blue areas). In Sections (10) and (11) of the spreadsheet file, the formulas for the whole quarter are needed (not for individual months). In Section (7) of the spreadsheet, Budgeted variable overhead is: Budgeted direct labor hours multiplied by Variable overhead rate (POR). You need to use some complex logical functions (e.g., If, Greater than, etc. and their combinations) in somewhere in Section (9) of the spreadsheet. All numbers are clean (no decimal point) except for Lines 181, 193, and 212. The Cash Budget part of the spreadsheet is formatted to round numbers to the nearest dollar. Off-balance in the budgeted balance sheet implies that something must be wrong. For all negative numbers, format them in such a way that the numbers are in parentheses {e.g., instead of "-360" it should be "(360)"} Grading Policy: Correctness: 80% Formatting/Appearance: 20% Formatting: After you enter all of the formulas, you are supposed to print the file. If your hardcopy looks unprofessional, format the file appropriately. The hardcopy should look professional. For example, we want to have a given schedule statement on the same page. You want to avoid that a part of a given schedule statement is on one page and the remaining part printed several pages later. Also, you do not want to see your numbers shown on the screen or printed like "#######" (overflow problem when the column width is not wide enough). You can avoid it by setting the column width a little bit wider than what you think is right * Professionals are customer/ user/ reader-oriented. Assume that you would turn in your file and its hardcopy of your work for your job interview (Of course, you turn in only the electronic file.) However, if someone looks at or prints your file, how will that look like? It may look OK on your PC/Printer, but may not on other PC/Printer).PART I. COMPANY'S INFORMATION (a) Balance Sheet, December 31, Year 0 ASSETS Current Assets Cast $7,000 Accounts Receivable $10,000 Finished Goods Inventory $10,500 for 1,500 units Raw Materials Inventory $10,350 for 6,900 lbs Prepaid Insurance $2,400 Property, Plant and Equipment Plant and Equipment $70,000 Leaa: Accumulated Depreciation 95,000 965,000 Total Assets $105,250 LIABILIES & STOCKHOLDERS' EQUITY Liabilities Accounts Payable $8,000 Long-term Note $20,000 Stockholders' Equity Common Stock $40,000 Retaned Earnings $37.250 Total Liabilities and Equity $105,250 (h) Sales Forecast (unite) January, Year 1 4,500 February, Year 1 5,500 March, Year 1 7,0n0 April, Year 1 7,600 May, Year 1 9,000 Selling Frice per Unit $10 (0) Production Requirements and Munufucturing Cuel Information Unit manufacturing cost of product $7.00 per unit Direct material requirement 2 lbs per uni of product cost per lbs $1.50 Direct labor requirement 0.5 hrs per unt of product cost per DLH $6.00 Variable Overhead's POR $1.40 per direct labor hour Fixed Overhead per Month Salaries $680 $350 Insurance $200 Depreciation $700 Total $1,930(d) Desired Ending Inventories Finished goods 50% of Unit sales in next month Raw material 60% of Quantity (pounds) to be used in production next month (e) Estimated S & A (selling and administrative) Expenses Sales Commissions (Variable) $0.80 per Unit sold Salaries (Fixed) $2,000 per Month Advertising (Fixed) $600 per Month Miscellaneous (Fixed) $2,345 Jan only Miscellaneous (Fixed) $1,540 Feb only Miscellaneous (Fixed) $481 Mar only (f) Estimated Cash Collections, Cash Payments, Financing and Other Information Cash Collections from Sales 70% Same month of sale 30% Next month Cash payments 60% Same month of purchase for Material Purchases 40% Next month Cash Payment for Dividends $500 End of March New Equipment Purchase $3,000 End of March Cash Payments for Other Items 100% Same month (do not apply to items that need no cash payments) Minimum Cash Level $6,000 (Has to be maintained at the end of each month) Minimum cash level should be maintained before making interest payment, but after borrowing or repayment.) Bank Loan Borrowing time (If borrowing is needed for a given month, it should occur at the beginning of the month.) Borrowing amount Just enough to satisfy the minimum cash level requirement Repayment time (If repayment is ever made for a given month, it should occur at the beginning of the month) Repayment amount Maximum possible amount after satisfying the minimum cash level requirement Interest payment time 1% per month on outstanding loan balance as of the end of the month To be paid at the end of each month Insurance cost will be charged to (deducted from) Prepaid Insurance. No cash will be paid. Long-term Note is a liability that does not bear interest.PART II. ALL BUDGETS/SCHEDULES FOR THE FIRST QUARTER OF Year 1 (1) Sales Budget Jan Feb Mar Qtr Expected sales - units Selling price per unit Total sales (2) Schedule of Expected Cash Collections (Cash Receipts Budget) Jan Feb Mar Qtr From sales before January From January sales From February sales From March sales Total cash collections (3) Production Budget Jan Feb Mar Qtr Apr May Expected sales units Desired ending inventory Total needs Less: Beginning inventory Units to be produced (4) Direct Materials Purchases Budget Jan Feb Mar Qtr Apr Units to be produced Material needed per unit (Ibs) Production needs (Ibs) Desired ending quantity (lbs) Total needs (lbs) Less: Beginning quantity (Ibs) Material to be purchased (lbs) II Cost of material purchase ($)(5) Schedule of Expected Cash Disbursements (Cash Disbursements Budget) for Direct Materials Jan Feb Mar Qtr For Purchases before January For January purchases For February purchases For March purchases Total cash disbursements (6) Direct Labor Budget Jan Feb Mar Qtr Units to be produced DL time required per unit Total hours of DL time needed Direct labor cost per hour Total direct labor cost (7) Manufacturing Overhead Budget Jan Feb Mar Qtr Budgeted direct labor hours Variable overhead rate (POR) Budgeted variable overhead Budgeted fixed overhead Total budgeted overhead Less: Depreciation Insurance Total noncash costs Cash Disbursements for MOH(8) Selling and Administrative Expense Budget Jan Feb Mar Otr Budgeted sales in units Variable S&A expense per unit Budgeted variable expense Budgeted Fixed S&A expenses Salaries Advertising Miscellaneous Total Total budgeted 58 (9) Cash Budget Jan Feb Mar Qtr Cash balance, beginning Add cash receipts: Collections from sales E tal cash available Disbursements: Payments for Material purchases Payments for Direct labor costs Payments for Manufacturing overhead Payments for Selling and administrative Payments for Equipment purchase Payments for Dividends Total Disbursements Excess (Deficiency)* * negative amount in ( ) Financing: Bank loan I III I MI Borrowing (repayment)* negative amount in ( ) Cash before interest payment Interest pa Cash balance, ending(10) Budgeted Income Statement, for 1st Quarter of Year 1 Qtr Sales Less: Cost of goods sold Gross margin Less: Selling and admin. expenses Net operating income Less: Interest expenses Net income (11) Balance Sheet, as of March 31, Year 1 ASSETS Current Assets Cash Accounts Receivable Raw Materials Inventory Finished Goods Inventory Prepaid Insurance Property, Plant and Equipment Plant and Equipment Less: Accumulated Depreciation Total Assets LIABILITIES & STOCKHOLDERS' EQUITY Liabilities Accounts Payable Long-term Note Bank Loan Stockholders' Equity Common Stock Retained Earnings Total Liabilities and Equity