Budget, Budget everywhere Alums from this class have e-mailed me and told me that though they learned a lot from this course, including product costing, performance evaluation and decision analys

Practice Set #1

Budgeting Spreadsheet

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 7 of your text, and examples of budget worksheets appear in the schedules throughout the chapter.

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)

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

iWii’s selling price is $65/unit. Desired ending inventory of iWii is 5,000 units plus 10% of the following month’s 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, 5 units of Material Y, and 3 units of Material Z.

Costs of materials are:

Material X – $1.6/unit, Material Y – $1.80/unit, Material Z – $3.00/unit

Desired ending inventory of materials X is 20% of the following month’s production need because it is sometimes in short supply. Desired ending inventory of materials Y and Z is 5% of the following month’s 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:

Sheet 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). 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.

Sheet 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.

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

Direct Materials Budget: Prepare a direct materials 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.

Sheet 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.

Sheet 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.

Project Submission

When you have completed your file, please submit it in the following format:

1. Print out each of the five worksheets. Put your NAME, ID NUMBER, AND RECITATION SECTION NUMBER at the top of each page. Print each page with gridlines. On sheets 2-5, label each budget clearly and include “Estimate #1" in each label. For example, “Production Budget, Estimate #1" and “Direct Labor Budget, Estimate #1.”

2. Now, on your data worksheet, change the following items:

Estimated sales of iWii for April: 45,000 units.

Cost per unit of Material Y: $1.00

Don’t make any other changes in the worksheets, except for changing the budget labels to “Estimate #2.” Print out worksheets 2-5 again, with gridlines.

3. Print out worksheets 2-5 showing the cell formulas instead of numbers. Print with gridlines.

Staple all pages together in the order presented above.

To summarize, your completed project consists of the following pages:

• Data worksheet (one page)

• Budget worksheets 2, 3, 4 and 5 for Estimate #1 (4 pages)

• Budget worksheets 2, 3, 4 and 5 for Estimate #2 (4 pages)

• Budget worksheets 2, 3, 4 and 5 showing cell formulas (4 pages)

for a total of 13 pages.

PLEASE FOLLOW THESE INSTRUCTIONS. If you submit your practice set in a different format, you will lose points.

Other information

1. You should have been exposed to basic Excel in other courses. You should already know how to link worksheets and enter cell formulas. For example, if you name your data worksheet as “data” and you are currently working on a different worksheet, entering a formula “Data!B5” into a cell will allow you to get the number contained in Cell B5 of the data worksheet to your current worksheet. If for some reason you have not received formal class instruction in Excel, you can use the help menus (as you would with any new software).

Here are some hints for printing this assignment: you can adjust printing formats (page orientation, gridlines) by selecting “Page Layout” from the top menu, and then click on “orientation” to select orientation, and “gridlines” to select gridlines. To print cell formulas, select the “Ctrl” tab and “~.” tab simultaneously (the one at the left corner of your key board). Your formulas should appear and you are ready to print.

1. Academic integrity: This is an individual project, meaning each student is expected to do it independently. You are expected to input your own data, develop your own spreadsheet formulas, and set up your own worksheets within your file. You may not use someone else’s file or jointly share a file. You have great flexibility in how you set up your spreadsheet. It is extremely unlikely that the many choices you make in developing your worksheet will be identical to those of any other student. I will check for evidence of shared files. If academic dishonesty is found, it will be dealt with severely. Be sure to retain the file you created (plus a backup file) until the end of the semester, in case there is a question.

1. The due date for the practice set is August 7 at the beginning of class. This practice set is worth 10 points toward your total grade. If you do not hand it in on time, you will lose 2 points for every day it is late, including weekend days.. You have more than a week to prepare this assignment. If you decide to wait until a few days before it is due to begin work on it, you also accept the risk that unplanned events may prevent you from completing it on time. It is up to you to organize your work schedule so your practice set is completed within the deadline. To minimize potential loss of your file, always back up your work in case something goes wrong

4. Here are some key figures to help you: For Estimate #1, the cash payments for total direct materials purchase for May are $879,726. For Estimate #2, the cash payments for total direct materials purchase for May are $828,786. If your numbers do not come out right, and you can’t find your mistake, do not try to change the numbers so the total comes out correctly. Each individual balance is graded, not the total. You may be changing numbers that are correct.