attached
1
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.
2
WEEK # 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.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
