Question

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.

Data

Glamour Inc. produces and sells lady handbags. Below is information on its activities for the next few months.

Sales projections for the coming months are as follows:

Estimated Sales (in units)

	April	May	June	July
Handbags	25,000	55,000	65,000	60,000

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

Each handbag’s selling price is $65/unit. Desired ending inventory of handbags is 5,000 units plus 10% of the following month’s projected sales. There are 7,500 units of handbags in inventory as of April 1.

2.         Estimated cash collections from sales of handbags 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 handbags: Rayon, Linen, and Leather. Materials requirements per unit of handbag are as follows: one unit of Rayon, 5 units of Linen, and 3 units of Leather.

Costs of materials are:

Rayon – $1.6/unit, Linen – $1.80/unit, Leather – $3.00/unit

Desired ending inventory of Rayon is 20% of the following month’s production need because it is sometimes in short supply. Desired ending inventory of Linen and Leather 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 Rayon, 28,000 units of Linen, and 6,800 units of Leather.

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.         There are two types of direct labor costs to incur before a handbag is completed. Direct labor costs are paid in cash as incurred.

            Department            Direct labor hours per unit of handbag          Cost per direct labor hour

            Cutting:                                   0.12 hour                                                     $20

            Sewing:                                     0.08 hour                                                    $25

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. Overhead costs are paid when incurred.

7.         Total variable selling and administrative costs are $1.50/unit of handbags sold. Total fixed selling and administrative costs are estimated at $350,000/month, of which $180,000 is depreciation. Selling and administrative costs are paid as the costs are incurred.

8.         The beginning balance of cash account on April 1 is $88,000. The company desires to maintain a minimum cash balance at the amount of $100,000.

9.         The company has an agreement with a local bank that would supply any amount of loan it needs to maintain the minimum cash balance. For simplicity, assume that interest is not compounded and that interest is paid each month but principal is paid at the year end. Annual interest rate is 12%.

10.       The company plans to purchase $5000 of equipment in April and $4,000 in May.

11.       Cash dividends is $1500 each month.

Preparation of Spreadsheet File

Create one Excel spreadsheet file consisting of the following six 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 handbags 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 amounts 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 on lines as follows: cash collections from sales two month ago, cash collections from sales one month ago and cash collections from sales in the current month. Also, list total cash collections in April, May and June.

Production Budget: Prepare a production budget for handbags, 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 Rayon purchase costs, Linen purchase costs, Leather 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. List cutting cost, sewing cost, and total labor cost separately. Also, list total 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.

Sheet 6:           Include the following one budget on the sixth worksheet, clearly labeled:

Cash Budget: Prepare a cash budget for April and May. List total amount of beginning cash balance, cash receipts, cash disbursements, cash excess or deficiency, borrowing at the beginning of the month, and ending cash balance separately.

Project Submission

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

1.         Print out each of the six worksheets. Put your NAME, ID NUMBER, AND RECITATION SECTION NUMBER at the top of each page. Print each page with gridlines. On sheets 2-6, 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 handbags for April: 45,000 units.

Cost per unit of Linen: $1.0

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

3.         Print out worksheets 2-6 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, 5 and 6 for Estimate #1 (5 pages)

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

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

for a total of 16 pages.

Please follow these instructions. If you submit your Excel project in a different format, you will lose points.

Answer 1

DATA
	Feb	March	April	May	June	July
Actual sales in units	30000	45000
Estimated Sales in units			25000	55000	65000	60000
Selling price per unit	$65	$65	$65	$65	$65	$65
Desired Ending inventory		7500	10500	11500	11000	5000
Cash collection in the month of sales	40%	40%	40%	40%	40%	40%
Cash collection in the month following sales	30%	30%	30%	30%	30%	30%
Cash collection in the second month following sale	28%	28%	28%	28%	28%	28%
Material requirement per unit
Rayon	1	1	1	1	1	1
Linen	5	5	5	5	5	5
Leather	3	3	3	3	3	3
Cost of material per unit
Rayon	$1.6	$1.6	$1.6	$1.6	$1.6	$1.6
Linen	$1.8	$1.8	$1.8	$1.8	$1.8	$1.8
Leather	$3.0	$3.0	$3.0	$3.0	$3.0	$3.0
Desired Ending inventory of material
Rayon			20%	20%	20%	20%
Linen			5%	5%	5%	5%
Leather			5%	5%	5%	5%
Actual ending inventory in March
Rayon		5,600
Linen		28,000
Leather		6,800
Cash payment for material in the month of purchase			60%	60%	60%	60%
Cash payment for material in the month following purchase			40%	40%	40%	40%
Total purchases of material in March		350,000
Direct labor hour per unit of handbag
Cutting			0.12	0.12	0.12	0.12
Sewing			0.08	0.08	0.08	0.08
Cost per direct labor hour
Cutting			$20	$20	$20	$20
Sewing			$25	$25	$25	$25
Variable manufacturing overhead per direct labor hour			$8	$8	$8	$8
Fixed manufacturing overhead			$250,000	$250,000	$250,000	$250,000
Depreciation included in fixed manufacturing overhead			$80,000	$80,000	$80,000	$80,000
Variable selling and administrative cost per handbag sold			$1.50	$1.50	$1.50	$1.50
Fixed selling and administrative cost			$350,000	$350,000	$350,000	$350,000
Depreciation included in fixed selling and administrative cost			$180,000	$180,000	$180,000	$180,000
Beginning Cash Balance			$88,000
Minimum Cash Balance			$100,000	$100,000	$100,000	$100,000
Interest rate per annum			12%	12%	12%	12%
Purchase of Equipment			$5,000	$4,000
Cash dividend			$1,500	$1,500	$1,500	$1,500
Sales Budget
	April	May	June
Estimated Sales in units	25000	55000	65000
Estimated Sales Revenue	$1,625,000	$3,575,000	$4,225,000
Cash collections from Sales two months ago	$546,000	$819,000	$455,000
Cash collections from Sales one month ago	$877,500	$487,500	$1,072,500
Cash collections from Sales in the current month	$650,000	$1,430,000	$1,690,000
Production Budget
	April	May	June
Estimated Sales in units	25000	55000	65000
Add: Desired Ending Inventory	10500	11500	11000
Less: Beginning Inventory	7500	10500	11500
Budgeted Production	28000	56000	64500
Direct Material Budget for April
	April			May
	Rayon	Linen	Leather	Total	Rayon	Linen	Leather	Total
Material required for budgeted production in units	28,000	140,000	84,000	252,000	56,000	280,000	168,000	504,000
Add: Desired Ending Inventory	11,200	14,000	8,400	33,600	12,900	16,125	9,675	38,700
Less: Beginning Inventory	5,600	28,000	6,800	40,400	11,200	14,000	8,400	33,600
Material requirement in units	33,600	126,000	85,600	245,200	57,700	282,125	169,275	509,100
Material cost in dollars	53,760	226,800	256,800	537,360	92,320	507,825	507,825	1,107,970
Payment for current month purchases				322,416				664,782
Payment for prior month purchases				140,000				214,944
Direct labor Budget
	April	May
Direct labor required for budgeted production
Cutting	3360	6720
Sewing	2240	4480
Total Hour required	5600	11200
Direct labour cost for budgeted production
Cutting	$67,200	$134,400
Sewing	$56,000	$112,000
Total direct labour cost	$123,200	$246,400
Total Cash Payment	$123,200	$246,400