Posted: March 12th, 2023
WEEK # 1 - EXERCISES CHAPTER # 1
Exercise Ch1: Level 3-A- Job Analysis. 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.
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)
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.
2WEEK # 1 - EXERCISES CHAPTER # 1
d. In cells A7:A18, enter the labels for each cost component category from the original option (see Figure 1.24), and then in cells B7:D7, enter the headings Cost per Shoe, Low Priced Total, and High Priced Total. e. In cells B11, B12, and B16, enter the costs per shoe for Materials, Labor, and Selling Expense, respectively. f. In cell B13, enter a formula to calculate the Overhead cost. g. In columns C and D, enter the necessary formulas to determine revenue, cost of goods sold, selling expense, and projected earnings for each pricing alternative. (Keep in mind that projected earnings is defined as revenue minus cost of goods sold minus selling expense.) Be sure to use the appropriate relative, absolute, and mixed cell references, so that the formulas can be copied wherever needed and will automatically update if data inputs are modified. Format the values and calculated results on your worksheet in a similar manner to those in Figure 1.24. h. Refer to Figure 1.24 and apply similar borders to the appropriate cells on your worksheet. i. Rename the Sheet1 worksheet tab as 1stQTR. 3. With the 1stQTR worksheet complete, use an appropriate method to create similar worksheets for the other three quarters. Name these worksheets 2ndQTR, 3rdQTR, and 4thQTR. Modify values and labels as necessary on these three worksheets. 4. Create a final comparison sheet named Summary to display each of the budget components summarized by year for each pricing alternative. For the worksheet title in cells C1:D1, enter TZEdge Annual Budget Textured Leather Option. 5. In cell A20, insert text to indicate which pricing alternative you would recommend and why. Highlight this text with a blue background. 6. Add your name at the end of the exercise and date 7. Save and close the 1-5-Texture-Budget-Your-Name.xlsx workbook.
Make Exercise Ch1: Level 3-A- Job Analysis. This exercise has no initial Data File, so the Creation Date of the solution needs to be on the date of the assignment to be accepted. Complete the exercise following the instructions and submit. NOTE:
• Chapter # 1 focuses on absolute and relative reference, range names, and link information from one tab to another.
Place an order in 3 easy steps. Takes less than 5 mins.