Hi,

I need help for structuring my data information my excel budgeting spreadsheet. I do not know how to structure it in excel.

I attached the given data. I just need help with setting this up in order to do my calculations and understand how to connect all the cells.

In the attachment it lists the data.

Thank you for your help,

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. Data Vintage Clothing Corporation produces and sells sweaters. 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 Sweaters 25,000 55,000 65,000 July 60,000 Actual sales in February were 30,000 units; actual sales in March were 45,000 units. Each sweater's selling price is $65/unit. Desired ending inventory of sweaters is 5,000 units plus 10% of the following month's projected sales. There are 7,500 units of sweaters in inventory as of April 1. 2. Estimated cash collections from sales of sweaters 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 sweaters: Material X, Material Y, and Material Z. Materials requirements per unit of sweater 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 4. 5. 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. 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. There are two types of direct labor costs to incur before a sweater is completed. Direct labor costs are paid in cash as incurred. Department hour Direct labor hours per unit of sweater Cost per direct labor Winding: Knitting: 0.12 hour 0.08 hour $20 $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 sweaters 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 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 sweaters 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. 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. Data Vintage Clothing Corporation produces and sells sweaters. 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 Sweaters 25,000 55,000 65,000 July 60,000 Actual sales in February were 30,000 units; actual sales in March were 45,000 units. Each sweater's selling price is $65/unit. Desired ending inventory of sweaters is 5,000 units plus 10% of the following month's projected sales. There are 7,500 units of sweaters in inventory as of April 1. 2. Estimated cash collections from sales of sweaters 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 sweaters: Material X, Material Y, and Material Z. Materials requirements per unit of sweater 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 4. 5. 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. 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. There are two types of direct labor costs to incur before a sweater is completed. Direct labor costs are paid in cash as incurred. Department Winding: Knitting: Direct labor hours per unit of sweater 0.12 hour 0.08 hour Cost per direct labor hour $20 $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 sweaters 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 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 sweaters 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. PLEASE INPUT ALL THE VALUES ON THIS SHEET Estimated Sales (in units) April 25,000 Feb 30,000 Sweaters May 55,000 June 65,000 Mar 45,000 Actual Sales (in units) Sweaters $ Sales price (per unit) 65.00 Beginning Inventory (In Units) 7,500 Ending Inventory (in Units) Desired Ending inventory Add: Next months projected sales percentage 5,000 10% Collection of Sales Schedule % Collection of sale within the month of sale % Collection of sale in month following the month of sale % Collection of sale in second month following the month of sale 40% 30% 28% Raw Material Requirement of production of one unit sweater: Material X (Per unit of Sweater) Material Y (Per unit of Sweater) Material Z (Per unit of Sweater) 1 5 3 Raw Material Ending Inventory Requirement: Material X (Desired Ending inventory) Material Y (Desired Ending inventory) Material Z (Desired Ending inventory) 20% (Of following Months Production requirement) 5% (Of following Months Production requirement) 5% (Of following Months Production requirement) Raw Material Beginning Inventory: Material X (Desired Ending inventory) Material Y (Desired Ending inventory) Material Z (Desired Ending inventory) Cost of Raw material per unit: Material X Material Y Material Z 5,600.00 28,000.00 6,800.00 $ $ $ Payment for material Schedule % Payment of the purchases within the month of sale % Payment of the purchases in month following the month of sale 1.60 1.80 3.00 60% 40% 350,000.00 Total Purchases during the month of March Direct labor Department Direct Labor Hours per unit of sweater Cost per Direct Labor hour 0.12 $ 0.08 $ Winding Knitting Variable production overhead rate (per hour of direct labor) $ 8.00 Fixed production overhead (Including $80,000 Depreciation) Fixed production overhead - Depreciation $ $ 250,000.00 80,000.00 Variable selling and admin cost (per sweater sold) $ 1.50 Fixed Selling & Admin cost (Including $1,80,000 Depreciation) Fixed Selling & Admin cost - Depreciation $ $ 350,000.00 180,000.00 20.00 25.00 July 60,000 Sales Budget Mar Feb 30,000 Actual Sale (in Units) Estimated Sales Sweaters (in units) - April - 45,000 25,000 65 $ - Sale price per Sweater (per unit) $ 65 $ Net Sales Revenue (in $) - Estimated Net Sales Revenue (in $) - Actual $ - 1,950,000 $ - Collection During the month of sale 40% Collection During Month following the month of sale 30% Collection During Second month following the month of sale 28% $ TOTAL COLLECTION $ 65 $ 2,925,000 May - - June 55,000 65 $ - July 65,000 65 $ - 60,000 65 - $ 1,625,000 $ 3,575,000 $ 4,225,000 $ 3,900,000 780,000 $ $ 1,170,000 $ 585,000 $ $ 650,000 $ 877,500 $ 546,000 $ 1,430,000 $ 487,500 $ 819,000 $ 1,690,000 $ 1,072,500 $ 455,000 $ 1,560,000 1,267,500 1,001,000 780,000 $ 1,755,000 $ 2,073,500 $ 2,736,500 $ 3,217,500 $ 3,828,500 Production Budget April May June July Ending Inventory Balance Add: Sales during the month Less: Beginning Invntory 10,500 25,000 7,500 11,500 55,000 10,500 11,000 65,000 11,500 5,000 60,000 11,000 Production During the month 28,000 56,000 64,500 54,000 Material Budget April May June July Monthly Requirement Product X Product Y Product Z 28,000 140,000 84,000 56,000 280,000 168,000 64,500 322,500 193,500 54,000 270,000 162,000 Add: Ending Inventory: Product X Product Y Product Z 11,200 14000 8,400 12,900 16125 9,675 10,800 13500 8,100 0 0 0 Less: Beginning Inventory Product X Product Y Product Z 5,600 28,000 6,800 11,200 14,000 8,400 12,900 16,125 9,675 10,800 13,500 8,100 33,600 126,000 85,600 57,700 282,125 169,275 62,400 319,875 191,925 43,200 256,500 153,900 Purchase Requirement Product X Product Y Product Z Purchase Budget Mar Feb Purchase Requirement (A) Product X Product Y Product Z April May 33,600 126,000 85,600 57,700 282,125 169,275 Cost of material Per unit (B) Product X Product Y Product Z $ $ $ Cost of Purchasing (A*B) Product X Product Y Product Z $ $ $ 53,760 $ 226,800 $ 256,800 $ 350,000 $ $ $ $ Total Purchase Payment During the month of sale 60% Payment During Month following the month of sale 40% TOTAL PAYMENT DURING THE MONTH $ 2 2 3 June $ $ $ 2 2 3 July 62,400 319,875 191,925 $ $ $ 2 2 3 43,200 256,500 153,900 $ $ $ 2 2 3 92,320 $ 507,825 $ 507,825 $ 99,840 $ 575,775 $ 575,775 $ 69,120 461,700 461,700 537,360 $ 322,416 $ 140,000 $ 1,107,970 $ 664,782 $ 214,944 $ 1,251,390 $ 750,834 $ 443,188 $ 992,520 595,512 500,556 462,416 $ 879,726 $ 1,194,022 $ 1,096,068 Labor cost Budget April Direct Labor Hours per unit of sweater: Winding Knitting 0.12 0.08 May June 0.12 0.08 July 0.12 0.08 0.12 0.08 Cost per Direct Labor hour: Winding Knitting $ $ 20 $ 25 $ 20 $ 25 $ 20 $ 25 $ 20 25 TOTAL DIRECT LABOR COST (PAID IN CASH) $ 123,200 $ 246,400 $ 283,800 $ 237,600 Manufacturing Overhead Budget April May Total Direct Labor Hours: Winding Knitting 3,360 2,240 June 6,720 4,480 July 7,740 5,160 6,480 4,320 Total Direct Labor Hours *Variable Overhead Rate per hour $ 5,600 8 $ 11,200 8 $ 12,900 8 $ 10,800 8 Total Variable overheads (Paid in Cash) Total Fixed Overheads, Excluding Dep (Paid in cash) $ $ 44,800 $ 170,000 $ 89,600 $ 170,000 $ 103,200 $ 170,000 $ 86,400 170,000 TOTAL MANUFACTURING OVERHEAD (PAID IN CASH) $ 214,800 $ 259,600 $ 273,200 $ 256,400 Selling & Admin Cost Budget April May Total Units sold *Variable selling & Adming cost (per sweater sold) June July $ 25,000 1.50 $ 55,000 1.50 $ 65,000 1.50 $ 60,000 1.50 Total Variable selling & Admin cost (Paid in Cash) Total Fixed selling & Admin cost, Excluding Dep (Paid in Cash) $ $ 37,500 $ 170,000 $ 82,500 $ 170,000 $ 97,500 $ 170,000 $ 90,000 170,000 TOTAL SELLING & ADMIN COST (PAID IN CASH) $ 207,500 $ 252,500 $ 267,500 $ 260,000 Cash Budget April Total Collections From Sales Total Payment for:Material Labor Manufacturing overhead Selling & Admin overhead $ NET CASH BALANCE $ $ $ $ $ May 2,073,500 $ 462,416 123,200 214,800 207,500 $ $ $ $ 1,065,584 $ June 2,736,500 $ 879,726 246,400 259,600 252,500 $ $ $ $ 1,098,274 $ July 3,217,500 $ 3,828,500 1,194,022 283,800 273,200 267,500 $ $ $ $ 1,096,068 237,600 256,400 260,000 1,198,978 $ 1,978,432 PLEASE INPUT ALL THE VALUES ON THIS SHEET Estimated Sales (in units) April 25,000 Feb 30,000 Sweaters May 55,000 June 65,000 Mar 45,000 Actual Sales (in units) Sweaters $ Sales price (per unit) 65.00 Beginning Inventory (In Units) 7,500 Ending Inventory (in Units) Desired Ending inventory Add: Next months projected sales percentage 5,000 10% Collection of Sales Schedule % Collection of sale within the month of sale % Collection of sale in month following the month of sale % Collection of sale in second month following the month of sale 40% 30% 28% Raw Material Requirement of production of one unit sweater: Material X (Per unit of Sweater) Material Y (Per unit of Sweater) Material Z (Per unit of Sweater) 1 5 3 Raw Material Ending Inventory Requirement: Material X (Desired Ending inventory) Material Y (Desired Ending inventory) Material Z (Desired Ending inventory) 20% (Of following Months Production requirement) 5% (Of following Months Production requirement) 5% (Of following Months Production requirement) Raw Material Beginning Inventory: Material X (Desired Ending inventory) Material Y (Desired Ending inventory) Material Z (Desired Ending inventory) Cost of Raw material per unit: Material X Material Y Material Z 5,600.00 28,000.00 6,800.00 $ $ $ Payment for material Schedule % Payment of the purchases within the month of sale % Payment of the purchases in month following the month of sale 1.60 1.80 3.00 60% 40% 350,000.00 Total Purchases during the month of March Direct labor Department Direct Labor Hours per unit of sweater Cost per Direct Labor hour 0.12 $ 0.08 $ Winding Knitting Variable production overhead rate (per hour of direct labor) $ 8.00 Fixed production overhead (Including $80,000 Depreciation) Fixed production overhead - Depreciation $ $ 250,000.00 80,000.00 Variable selling and admin cost (per sweater sold) $ 1.50 Fixed Selling & Admin cost (Including $1,80,000 Depreciation) Fixed Selling & Admin cost - Depreciation $ $ 350,000.00 180,000.00 20.00 25.00 July 60,000 Sales Budget Mar Feb 30,000 Actual Sale (in Units) Estimated Sales Sweaters (in units) - April - 45,000 25,000 65 $ - Sale price per Sweater (per unit) $ 65 $ Net Sales Revenue (in $) - Estimated Net Sales Revenue (in $) - Actual $ - 1,950,000 $ - Collection During the month of sale 40% Collection During Month following the month of sale 30% Collection During Second month following the month of sale 28% $ TOTAL COLLECTION $ 65 $ 2,925,000 May - - June 55,000 65 $ - July 65,000 65 $ - 60,000 65 - $ 1,625,000 $ 3,575,000 $ 4,225,000 $ 3,900,000 780,000 $ $ 1,170,000 $ 585,000 $ $ 650,000 $ 877,500 $ 546,000 $ 1,430,000 $ 487,500 $ 819,000 $ 1,690,000 $ 1,072,500 $ 455,000 $ 1,560,000 1,267,500 1,001,000 780,000 $ 1,755,000 $ 2,073,500 $ 2,736,500 $ 3,217,500 $ 3,828,500 Production Budget April May June July Ending Inventory Balance Add: Sales during the month Less: Beginning Invntory 10,500 25,000 7,500 11,500 55,000 10,500 11,000 65,000 11,500 5,000 60,000 11,000 Production During the month 28,000 56,000 64,500 54,000 Material Budget April May June July Monthly Requirement Product X Product Y Product Z 28,000 140,000 84,000 56,000 280,000 168,000 64,500 322,500 193,500 54,000 270,000 162,000 Add: Ending Inventory: Product X Product Y Product Z 11,200 14000 8,400 12,900 16125 9,675 10,800 13500 8,100 0 0 0 Less: Beginning Inventory Product X Product Y Product Z 5,600 28,000 6,800 11,200 14,000 8,400 12,900 16,125 9,675 10,800 13,500 8,100 33,600 126,000 85,600 57,700 282,125 169,275 62,400 319,875 191,925 43,200 256,500 153,900 Purchase Requirement Product X Product Y Product Z Purchase Budget Mar Feb Purchase Requirement (A) Product X Product Y Product Z April May 33,600 126,000 85,600 57,700 282,125 169,275 Cost of material Per unit (B) Product X Product Y Product Z $ $ $ Cost of Purchasing (A*B) Product X Product Y Product Z $ $ $ 53,760 $ 226,800 $ 256,800 $ 350,000 $ $ $ $ Total Purchase Payment During the month of sale 60% Payment During Month following the month of sale 40% TOTAL PAYMENT DURING THE MONTH $ 2 2 3 June $ $ $ 2 2 3 July 62,400 319,875 191,925 $ $ $ 2 2 3 43,200 256,500 153,900 $ $ $ 2 2 3 92,320 $ 507,825 $ 507,825 $ 99,840 $ 575,775 $ 575,775 $ 69,120 461,700 461,700 537,360 $ 322,416 $ 140,000 $ 1,107,970 $ 664,782 $ 214,944 $ 1,251,390 $ 750,834 $ 443,188 $ 992,520 595,512 500,556 462,416 $ 879,726 $ 1,194,022 $ 1,096,068 Labor cost Budget April Direct Labor Hours per unit of sweater: Winding Knitting 0.12 0.08 May June 0.12 0.08 July 0.12 0.08 0.12 0.08 Cost per Direct Labor hour: Winding Knitting $ $ 20 $ 25 $ 20 $ 25 $ 20 $ 25 $ 20 25 TOTAL DIRECT LABOR COST (PAID IN CASH) $ 123,200 $ 246,400 $ 283,800 $ 237,600 Manufacturing Overhead Budget April May Total Direct Labor Hours: Winding Knitting 3,360 2,240 June 6,720 4,480 July 7,740 5,160 6,480 4,320 Total Direct Labor Hours *Variable Overhead Rate per hour $ 5,600 8 $ 11,200 8 $ 12,900 8 $ 10,800 8 Total Variable overheads (Paid in Cash) Total Fixed Overheads, Excluding Dep (Paid in cash) $ $ 44,800 $ 170,000 $ 89,600 $ 170,000 $ 103,200 $ 170,000 $ 86,400 170,000 TOTAL MANUFACTURING OVERHEAD (PAID IN CASH) $ 214,800 $ 259,600 $ 273,200 $ 256,400 Selling & Admin Cost Budget April May Total Units sold *Variable selling & Adming cost (per sweater sold) June July $ 25,000 1.50 $ 55,000 1.50 $ 65,000 1.50 $ 60,000 1.50 Total Variable selling & Admin cost (Paid in Cash) Total Fixed selling & Admin cost, Excluding Dep (Paid in Cash) $ $ 37,500 $ 170,000 $ 82,500 $ 170,000 $ 97,500 $ 170,000 $ 90,000 170,000 TOTAL SELLING & ADMIN COST (PAID IN CASH) $ 207,500 $ 252,500 $ 267,500 $ 260,000 Cash Budget April Total Collections From Sales Total Payment for:Material Labor Manufacturing overhead Selling & Admin overhead $ NET CASH BALANCE $ $ $ $ $ May 2,073,500 $ 462,416 123,200 214,800 207,500 $ $ $ $ 1,065,584 $ June 2,736,500 $ 879,726 246,400 259,600 252,500 $ $ $ $ 1,098,274 $ July 3,217,500 $ 3,828,500 1,194,022 283,800 273,200 267,500 $ $ $ $ 1,096,068 237,600 256,400 260,000 1,198,978 $ 1,978,432