I need help with this Master Budget
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 Number Description A10 $100,000 Amount of Borrowing B30 0.05 Interest Rate K1 You need to Amount of Annual Interest determine! Expense In cell K1, enter the formula only, "+Al*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 A50.. In Part 1, (c), the interpretation is as follows: To make 1 unit of the product, 2 Ibs of direct materials are needed (1 Ibs 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)
