i need help putting this master budget project together

Your task is to make a master budgeting template in a spreadsheet. Obtain the pre- formatted spreadsheet (master.xls). 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 A10 B30 Kl Number $100,000 0.05 You need to determine! Description Amount of Borrowing Interest Rate Amount of Annual Interest Expense In cell Kl, enter the formula only._+A1*B30" or "=Al*B30"). Do not enter "10000*0.3", "+A1*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 ASO. 1 In Part 1, (), the interpretation is as follows: To make 1 unit of the product, 12 ibs of direct materials are needed (1 lbs of the material costs $1.50). 0.5 hour of direct labor is needed (1 hour of labor costs 56). 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 (eg, 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 tum 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) Balence Sheet, December 31, Year 0 ASSETS Current Assets Casti Accounts Receivable Finished Goods Inventory Raw Materials Inventory Prepaid Insurance 37 000 $10.000 $10.500 for $10.350 for $2.400 1,500 units 6,900 lbs Property Plant and Equipment Plant and Equipment Leo: Accumulated Depreciation $70,000 $5.00 SES 000 Total Assets S105 250 $8.000 $20.000 LIABILMES & STOCKHOLDERS' EQUITY Liabilities Accounts Payable Long-term Note Stockholders' Equity Common Stock Retaned Earnings $40,000 $37250 Total Liabilities and Equity $105. 250 (h) Sale Forecast (unit) January, Year 1 February, Year 1 March Year 1 April, Year 1 May, Year 1 4,500 3.500 7 nnn 7.600 9,000 Selling Price per Unit $10 (-) Puduction Ryuichen und Munuucluing Culfurmulion $7.00 per unit of product Unit marufacturing cost of product Direct material requirement Direct labor requirement Variable Overhead's POR 2 ts per unr of product 0.5 hrs per unt of product $1.40 per direct labor hour cost per lbs cost per DLH $1.50 56.00 Fxcd Overhead per Month Salaries Utilties Insurance Depreciation Total $680 $350 5200 $700 $1,930 (d) Desired Ending Inventories Finished goods Raw material 50% of Unit sales in next month 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 (1) Estimated Cash Collections, Cash Payments, Financing and Other Information Cash Collections from Sales 70% Same month of sale 30% Next month Cash payments for Material Purchases 60% Same month of purchase 40% Next month Cash Payment for Dividends New Equipment Purchase Cash Payments for other items Minimum Cash Level $500 End of March $3,000 End of March 100% Same month (do not apply to items that need no cash payments) $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.) (lf borrowing is needed for a given month, 2 should occur at the beginning of the month.) Just enough to satisfy the minimum cash level requirement (of repayment is ever made for a given month, it should occur at the beginning of the month) Maximum possible amount after satisfying the minimum cash level requirement 1% per month on outstanding loan balance as of the end of the month To be paid at the end of each month Bank Loan Borrowing time Borrowing amount Repayment time Repayment amount interest payment time 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 BUDGET S/SCHEDULES FOR THE FIRST QUARTER OF Year 1 (1) Sales Budget Jan Feb Mar Qtr Expected sales - units Seling 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 Otr Apr May Expected sales units Desired ending inventory Total needs pm Less: Beginning inventory DOO Unts to be produced (4) Direct Materials Purchases Budget Jan Feb Mar Qtr Apr Units to be produced Material needed per unit (lbs) Production needs (lbs) Desired ending quantity (los) Total needs (lbs) Less: Beginning quantity (lbs) Material to be purchased (lbs) Cost of material purchase (5) (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 unt 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 Qtr Budgeted sales in uns Variable S&A expense per un Budgeted variable expense Budgeted Fixed S&A expenses Salaries Advertising Miscellaneous Total Total budgeted S&A expenses (9) Cash Budget Jan Feb Mar Qtr Cash balance, beginning Add cash receipts: Collections from sales Total 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 III MII III 0000 1000 1000 negative amount in Excess (Deficiency)" Financing: Bank loan Borrowing (repayment) * negative amount in o) Cash before interest payment Interest payment Cash balance, ending (10) Budgeted Income Statement, for 1st Quarter of Year 1 Otr 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 LIABILITES & STOCKHOLDERS' EQUITY Liabilities Accounts Payable Long-term Note Bank Loan Stockholders' Equity Common Stock Retained Earnings Total Liabilities and Equity