PLEASE POST THE FORMULA SHEET TOO FROM EXCEL!

This assignment asks you to set up an Excel budget spreadsheet file that automatically prepares the master budget for a company, given sales projections and information on beginning balances, production requirements, desired ending inventories, etc. Information on developing the budgets appears in Chapter 8 of your text, and examples of budget worksheets appear in the schedules throughout the chapter.

PLEASE FOLLOW INSTRUCTIONS. If you submit your project in a different format, you will lose points!

DATA

BigV Corporation produces and sells one product, iWii. Below is information on its activities for the next few months.

1. Sales projections for the coming months are as follows:

Estimated Sales (in units)
	April	May	June	July
iWii	25,000	55,000	65,000	60,000

Actual sales in February were 30,000 units; actual sales in March were 45,000 units.

iWiis selling price is $65/unit. Desired ending inventory of iWii is 5,000 units plus 10% of the following months projected sales. There are 7,500 units of iWii in inventory as of April 1.

2. Estimated cash collections from sales of iWii to customers are as follows: 40% collected in the month of sale, 30% collected in the month following sale, 28% collected in the second month following sale, and 2% never collected.

3. Three materials are used in the production of iWii: Material X, Material Y, and Material Z. Materials requirements per unit of iWii are as follows: one unit of Material X, five units of Material Y, and three units of Material Z.

Costs of Materials (per unit)
Material X	$1.60
Material Y	$1.80
Material Z	$3.00

Desired ending inventory of materials X is 20% of the following months production need because it is sometimes in short supply. Desired ending inventory of materials Y and Z is 5% of the following months production need because they are easy to get. Inventories of materials as of April 1 are: 5,600 units of Material X, 28,000 units of Material Y, and 6,800 units of Material Z.

4. The company pays for materials purchases as follows: 60% in the month of purchase, 40% in the month following purchase. Total purchases of materials for the month of March were $350,000.

5. Direct labor costs $25/hour. Nine minutes of direct labor is required to produce each unit of iWii.

6. Total variable manufacturing overhead is estimated at $8/direct labor hour.

Total fixed manufacturing overhead is estimated at $250,000/month, of which $80,000 is depreciation.

7. Total variable selling and administrative costs are $1.50/unit of iWii sold.

Total fixed selling and administrative costs are estimated at $350,000/month, of which $180,000 is depreciation.

8. Direct labor costs are paid in cash as incurred. Cash expenses for overhead and selling and administrative expenses are paid as incurred.

PREPARATION OF SPREADSHEET FILE

Create ONE Excel spreadsheet file consisting of the following FIVE separate worksheets:

19. 1: Data:

This worksheet contains the data necessary to do all the other worksheets. List on this worksheet all of the data shown above, clearly labeled. None of the other worksheets should contain any numbers; they should contain ONLY FORMULAS - all cells on the other worksheets should be linked to cells in the data worksheet, cells within the same worksheet, or cells in the other worksheets.

For example, the cell for direct labor cost for April should contain a formula that multiplies the production in units for April (from the production schedule) times the labor hours per unit (from the data worksheet) times the hourly wage rate (from the data worksheet).

=production in units*labor hours per unit*hourly wage rate

So, if the sales estimate for iWii changes, you should be able to make the change only on the data worksheet; all the other worksheets should automatically adjust to the changes. The production units will change; the manufacturing costs will change, etc. The data worksheet can be in any format; just be sure to label each data item clearly.

19. 2: Include the following two budgets on the second worksheet, clearly labeled:

Sales Budget: Prepare a schedule of sales revenue and cash receipts from sales for each of the months of April, May, and June. List cash collections separately as follows: cash collections from sales two months ago, cash collections from sales one month ago and cash collections from sales in the current month. Also, list total cash collections for each of the months of April, May, and June.

Production Budget: Prepare a production budget for iWii, in units, for each of the months of April, May and June.

19. 3: Include the following one budget on the third worksheet, clearly labeled:

Direct Materials Budget: Prepare a direct material purchases budget, in units and in total dollars, for April and May. List Material X purchase costs, Material Y purchase costs, Material Z purchase costs and total material purchase costs separately. List payments for current month purchase, payments for prior month purchase, and total payments on separate lines.

19. 4: Include the following one budget on the fourth worksheet, clearly labeled:

Direct Labor Budget: Prepare the budget for costs of direct labor used for April and May, in hours and in total dollars. Also, list cash payments for each month.

19. 5: Include the following two budgets on the fifth worksheet, clearly labeled:

Manufacturing Overhead Budget: Prepare a manufacturing overhead budget for April and May. Show variable manufacturing overhead, fixed manufacturing overhead and total manufacturing overhead costs separately for each month. Also, list total cash payments for each month.

Selling & Administrative Expense Budget: Prepare a selling and administrative expense budget for April and May. Show variable selling and administrative costs, fixed selling and administrative costs and total selling and administrative costs separately for each month. List total cash payments for each month.