50.1K

Verified Solution

Link Copied!

i posted the solution too but can you actually post the formulas from Excel?

Excel Project

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

Oxford Manufacturing Company produces and sells one product, Oxford console table for entryway. 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)

	July	August	September	October
Console tables	10,000	12,000	9,000	8,000

Actual sales in May were 10,000 units; actual sales in June were 14,000 units.

2. Oxford console tables selling price is $500/unit. Estimated cash collections from sales of each month (including June) of console tables to customers are as follows: 65% collected in the month of sale, 30% collected in the month following sale, and 5% can not be collected.

3. Desired ending inventory of Oxford console tables is 20% of the next months projected sales. There are 4,000 units of console tables in inventory on June 30.

4. Two materials are used in the production of Oxford console table: Red Oak and Glass. Materials requirements per unit of console table are as follows:

Direct Material Units of direct materials Cost per unit

per unit of Oxford console table

Red Oak: 45 pounds $6.00/pound

Glass: 9 square feet $1.00/square foot

Desired ending inventory of Red Oak is 30% of the following months production need because Red Oak is sometimes in short supply; desired ending inventory of Glass is 5% of the following months production need because Glass is easy to get. Inventories of materials as of June 30 are 26,000 pounds of Red Oak and 4,900 square feet of Glass.

5. The company pays for materials purchases as follows: 60% in the month of purchase, and 40% in the month following purchase. The accounts payable balance (due to materials purchases) on June 30 was $1,100,000, which is all payable in July.

6. Oxford console tables have two departments to pass before they are completed.

Department Direct labor hours per unit of console table Cost per direct labor hour

Shaping: 0.75 hour $24

Finishing: 1.70 hours $16

7. Direct labor costs are paid in cash as incurred.

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

Total fixed manufacturing overhead is estimated at $500,000/month, of which $5,000 is depreciation on factory buildings and equipment. Overhead costs are paid when incurred.

9. Total variable selling and administrative costs are $2/unit of Oxford console table sold.

Total fixed selling and administrative costs are estimated at $300,000/month, of which $65,000 is depreciation on administrative buildings and equipment. Selling and administrative costs are paid as the costs are 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 July should contain a formula that multiplies the production in units for July (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 console tables 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 July, August and September. List cash collections separately on lines as follows: cash collections from sales one month ago and cash collections from sales in the current month. Also, list total cash collections in July, August and September.

Production Budget: Prepare a production budget for Oxford console tables, in units, for each of the months of July, August and September.

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 July and August. List Red Oak purchase costs, Glass purchase costs, and total material purchase costs separately. List payments for current month purchase and for prior month purchase on separate lines. Also, list total payments in July and August.

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 July and August, in units and in total dollars. List shaping labor cost, finishing labor cost, and total labor cost separately. Also, list total 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 July and August. 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 July and August. 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.

Oxford Manufacturing Company October Ma Au Se June ul Sales (units 10,000 14,000 10,000 12,000 9,000 8,000 Closing stock (20% of next month sales) 4,000 2,400 1,800 1,600 Production (Closing stock+Sales-Op ng stock 8,40011,400 18,000 8,800 Closing Raw Material Stock red oak pounds(30% of next month production 26,000 153,900 118,800 Closing Raw Material stock Glass Sqftpounds(5% of next month production) 4,900 25,650 19,800 Raw Material Consumed Red Oak 810,000 378,000 513,000 396,000 Raw Material Consumed Glass 162,000 75,600 102,600 79,200 Raw Material Purchase Red Oak (stock consumed+closing stock-opening stock 505,900 477,900 277,200 Raw Material Purchase Glass (stock consumed+closing stock-opening stock) 96,350 96,750 59,400 Variable Total Fixed Amount Cost per Sales Budget per unit Qt Ju Au Se June Sales (units 14000 10000 9000 Sales Revenue 500 7,000,000 5,000,000 6,000,000 4,500,000 Sales Collection On account sales 7,000,000 5,000,000 6,000,000 4,500,000 Current Sales@65% 3,250,000 3,900,000 2,925,000 Current on account sales of 2,100,000 1,500,000 1,800,000 arter @ 30% Total Cash Collection 5,350,000 5,400,000 4,725,000 Jul Production Budget: Unit produced August Sept June 8,400 11,400 8,800 Direct Materials Budget: Raw Material Purchase Red Oak (stock consumed+closing stock-opening stock) 505900 477900 277200 Raw Material Purchase Glass (stock consumed+closing stock-opening stock 96350 59400 96750 Direct Material@$6-Red Oak 3,035,400 2,867,400 1,663,200 Direct Material@$1-Glass 96,35096,750 59,400 Total Purchase 3,131,750 2,964,150 1,722,600 Expected Cash disbursement for merchandise Purchase Purchase 1,100,000 3,131,750 2,964,150 1,722,600 Payment to Suppliers current month Purchase @60% 1,879,050 1,778,490 1,033,560 Previous Month purchase@40% 1,100,000 1,252,700 1,185,660 Total cash ents 2,979,050 3,031,190 2,219,220 Direct Labor Budget June August Sept Unit produced Direct Labor (0.75 24)-Shaping 11400 8400 8800 18.00 151,200 205,200 158,400 Direct Labor (1.70*16)-Finishin 28.00 235,200319,200 246,400 Total cash ments 386,400524,400 404,800 Manufacturing Overhead Budget: Jul August Sept June Unit produced Variable Manufacturing OH 8400 11400 8800 51,45069,825 6.125 53,900 Fixed manufacturing overhead Depreciation 5,000 5,000 5,000 5,000 Others 495,000 495,000 495,000 495,000 Total Manufacturing OH 551,450569,825 553,900 Total cash ents (excluding Depreciation 546,450 564,825 548,900 Selling & Administrative Expense Budget: June Ju August Sept Sales (units 10000 12000 9000 Total variable selling and administrative costs 24,000 18,000 20,000 fixed selling and administrative costs Depreciation 65,000 65,000 65,000 Others 235,000 235,000235,000 235,000 Total Seeling and Admin OH 320,000324,000 318,000 Total cash payments (excluding Depreciation) 255,000 259,000 253,000 Oxford Manufacturing Company October Ma Au Se June ul Sales (units 10,000 14,000 10,000 12,000 9,000 8,000 Closing stock (20% of next month sales) 4,000 2,400 1,800 1,600 Production (Closing stock+Sales-Op ng stock 8,40011,400 18,000 8,800 Closing Raw Material Stock red oak pounds(30% of next month production 26,000 153,900 118,800 Closing Raw Material stock Glass Sqftpounds(5% of next month production) 4,900 25,650 19,800 Raw Material Consumed Red Oak 810,000 378,000 513,000 396,000 Raw Material Consumed Glass 162,000 75,600 102,600 79,200 Raw Material Purchase Red Oak (stock consumed+closing stock-opening stock 505,900 477,900 277,200 Raw Material Purchase Glass (stock consumed+closing stock-opening stock) 96,350 96,750 59,400 Variable Total Fixed Amount Cost per Sales Budget per unit Qt Ju Au Se June Sales (units 14000 10000 9000 Sales Revenue 500 7,000,000 5,000,000 6,000,000 4,500,000 Sales Collection On account sales 7,000,000 5,000,000 6,000,000 4,500,000 Current Sales@65% 3,250,000 3,900,000 2,925,000 Current on account sales of 2,100,000 1,500,000 1,800,000 arter @ 30% Total Cash Collection 5,350,000 5,400,000 4,725,000 Jul Production Budget: Unit produced August Sept June 8,400 11,400 8,800 Direct Materials Budget: Raw Material Purchase Red Oak (stock consumed+closing stock-opening stock) 505900 477900 277200 Raw Material Purchase Glass (stock consumed+closing stock-opening stock 96350 59400 96750 Direct Material@$6-Red Oak 3,035,400 2,867,400 1,663,200 Direct Material@$1-Glass 96,35096,750 59,400 Total Purchase 3,131,750 2,964,150 1,722,600 Expected Cash disbursement for merchandise Purchase Purchase 1,100,000 3,131,750 2,964,150 1,722,600 Payment to Suppliers current month Purchase @60% 1,879,050 1,778,490 1,033,560 Previous Month purchase@40% 1,100,000 1,252,700 1,185,660 Total cash ents 2,979,050 3,031,190 2,219,220 Direct Labor Budget June August Sept Unit produced Direct Labor (0.75 24)-Shaping 11400 8400 8800 18.00 151,200 205,200 158,400 Direct Labor (1.70*16)-Finishin 28.00 235,200319,200 246,400 Total cash ments 386,400524,400 404,800 Manufacturing Overhead Budget: Jul August Sept June Unit produced Variable Manufacturing OH 8400 11400 8800 51,45069,825 6.125 53,900 Fixed manufacturing overhead Depreciation 5,000 5,000 5,000 5,000 Others 495,000 495,000 495,000 495,000 Total Manufacturing OH 551,450569,825 553,900 Total cash ents (excluding Depreciation 546,450 564,825 548,900 Selling & Administrative Expense Budget: June Ju August Sept Sales (units 10000 12000 9000 Total variable selling and administrative costs 24,000 18,000 20,000 fixed selling and administrative costs Depreciation 65,000 65,000 65,000 Others 235,000 235,000235,000 235,000 Total Seeling and Admin OH 320,000324,000 318,000 Total cash payments (excluding Depreciation) 255,000 259,000 253,000

Answer & Explanation Solved by verified expert

See Answer