You have learned a tremendous amount about creating the first year's budget for the original option...

 

Transcribed Image Text:

You have learned a tremendous amount about creating the first year's budget for the original option of the new TZEdge shoe. Now, you need to create quarterly budgets and a summary for the textured leather option using $74.07 per shoe as the cost of materials and $5.32 per shoe as the cost of labor. Unlike with the original option, only two pricing alternatives are being considered for the textured leather option. Table 1.7 shows the two pricing alternatives, as well as the expected sales volume in each quarter for these alternatives. Overhead is calculated at 29% of the direct labor costs, and the selling expense is assumed to be $5.10 per shoe. Table 1.7: Pricing alternatives for textured leather option 1stQTR 2ndQTR 3rdQTR 4th QTR Alternative: $/Pair #Pairs #Pairs #Pairs #Pairs Low Priced 215 560 710 950 1150 High Priced 255 325 410 535 640 Complete the following: 1. Create a new workbook named 1-5-Texture-Budget-Your-Name.xlsx, and then save the file. 2. Using the finished first quarter budget worksheet for the original option as a model (see Figure 1.24) Figure 1.24: Finished first quarter budget A B C D E F 1 Number of Shoes Per Pair TZEdge First 21 Quarter Budget 2 Overhead % of Labor 25% 3 4 Low Priced Medium Priced High Priced 5 Sales Volume #pairs: 6 Solling Price Per Pair: $ 1,000 200 S 750 225 S 350 250 7 Item 8 Revenue Total $ 200,000 S Costs per Low Priced Medium Priced High Priced Shoo Total 168,750 S Total 87,500 9 10 Cost of Goods Sold: 11 Materials $ 50 15 100,300 75,225 35,105 12 Direct Labor 7.33 14.660 10,995 5,131 13 Overhead 1.83 3.665 2,749 1,283 14 COGS Subtotal 118,625 88,969 41,519 15 16 Selling Expense 5.00 10,000 7.500 3,500 17 18 Projected Earnings S 71,375 5 72.281 5 42.481 19 20 Create the first quarter budget worksheet for the textured leather option, as follows: a. In cells A1:B2, enter the necessary inputs for the number of shoes per pair and the % of labor for overhead. b. In cell C1, enter the title TZEdge 1st Quarter Budget Textured Leather Option in bold and italic. Merge and center this title across cells C1:D1, and wrap the text so this title appears over 2 lines. Apply a gray shaded background to the title cells. c. In rows 4 through 6, enter the first quarter sales volume and price data, with the column headings Low Priced and High Priced in cells C4 and D4, respectively. Use similar formatting as shown in Figure 1.24. You have learned a tremendous amount about creating the first year's budget for the original option of the new TZEdge shoe. Now, you need to create quarterly budgets and a summary for the textured leather option using $74.07 per shoe as the cost of materials and $5.32 per shoe as the cost of labor. Unlike with the original option, only two pricing alternatives are being considered for the textured leather option. Table 1.7 shows the two pricing alternatives, as well as the expected sales volume in each quarter for these alternatives. Overhead is calculated at 29% of the direct labor costs, and the selling expense is assumed to be $5.10 per shoe. Table 1.7: Pricing alternatives for textured leather option 1stQTR 2ndQTR 3rdQTR 4th QTR Alternative: $/Pair #Pairs #Pairs #Pairs #Pairs Low Priced 215 560 710 950 1150 High Priced 255 325 410 535 640 Complete the following: 1. Create a new workbook named 1-5-Texture-Budget-Your-Name.xlsx, and then save the file. 2. Using the finished first quarter budget worksheet for the original option as a model (see Figure 1.24) Figure 1.24: Finished first quarter budget A B C D E F 1 Number of Shoes Per Pair TZEdge First 21 Quarter Budget 2 Overhead % of Labor 25% 3 4 Low Priced Medium Priced High Priced 5 Sales Volume #pairs: 6 Solling Price Per Pair: $ 1,000 200 S 750 225 S 350 250 7 Item 8 Revenue Total $ 200,000 S Costs per Low Priced Medium Priced High Priced Shoo Total 168,750 S Total 87,500 9 10 Cost of Goods Sold: 11 Materials $ 50 15 100,300 75,225 35,105 12 Direct Labor 7.33 14.660 10,995 5,131 13 Overhead 1.83 3.665 2,749 1,283 14 COGS Subtotal 118,625 88,969 41,519 15 16 Selling Expense 5.00 10,000 7.500 3,500 17 18 Projected Earnings S 71,375 5 72.281 5 42.481 19 20 Create the first quarter budget worksheet for the textured leather option, as follows: a. In cells A1:B2, enter the necessary inputs for the number of shoes per pair and the % of labor for overhead. b. In cell C1, enter the title TZEdge 1st Quarter Budget Textured Leather Option in bold and italic. Merge and center this title across cells C1:D1, and wrap the text so this title appears over 2 lines. Apply a gray shaded background to the title cells. c. In rows 4 through 6, enter the first quarter sales volume and price data, with the column headings Low Priced and High Priced in cells C4 and D4, respectively. Use similar formatting as shown in Figure 1.24.