Master Budget

Budgeting

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 determine! Amount of Annual Interest Expense

In cell K1, enter the formula only, "+A1*B30" or "=A1*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 I, (c), the interpretation is as follows:
To make 1 unit of the product,
 2 lbs 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 $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 (4) 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 177, 188, and 207. 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)" }