Advanced Spreadsheets for Business and Economics

1

WEEK # 1 - EXERCISES CHAPTER # 1

Exercise Ch1-3: Level 2 – Labor Comparison Objective: Use of formulas and functions MAX, MIN, COUNT and AVERAGE Recall that there are two additional options being considered for the TZEdge shoe: • Textured leather that includes one logo, and no appliqués, for trim • A high top design similar to the original shoe Now that the material analysis for the three options is in place, Paul needs to determine the labor costs. Following Figure shows the labor comparison worksheet that has been developed so far; it contains the costs associated with the manufacturing tasks for one shoe produced with the original option.

The manufacturing group has determined how the different design options will affect the number of hours and skills required to assemble the shoe. This group has provided the following information: Textured Leather • Because the textured leather is easier to work with, it will take about three quarters of the time to assemble the leather and sole compared with the time used for the original option. • The assembly of the arch support will take approximately the same time as for the original option. • Because there are no appliqués on the textured leather, there is no cost for attaching them. Therefore, the cell calculating the attachment of appliqués can be left blank. • It will take about 45% more time to cut the textured leather compared with the leather used for the original option.

• Only one logo needs to be sewn on, so the cost for the textured option is approximately half the amount as it is for the original option. • The cost for delivering boxes is the same as the original option. High Top • The high top shoes require additional leather. The group estimates it will take about 40% more time to assemble the leather and sole compared with the original option. • Since these shoes require more leather, the costs to deliver boxes will increase by $.04 per shoe. • All other manufacturing costs remain the same as those for the original option. Now, you need to finish the worksheet to determine the manufacturing costs of the textured leather and high top options. Complete the following: 1. Open the workbook named Comparison-1.xlsx located in the Chapter 1 folder, and then save the file as 1-3-Labor Comparision-Your-Name.xlsx. 2. Complete columns C and D for the textured leather and high top options based on the data provided for the original option and the information provided by the manufacturing group.

2

WEEK # 1 - EXERCISES CHAPTER # 1

3. Calculate the total labor cost for each option. 4. Format all values in the worksheet with two decimal places. Use dollar signs in the first row and total row only. 5. In row 11, below the option totals, calculate the number of tasks in each option and the number of possible tasks based on the list in column A. Format these values in bold and italic. In cell E11, insert the label # Tasks and format in bold and italic. 6. In cell E3, calculate the maximum cost of the box delivery task for the three options. Use the label Max. Cost as the column heading. Calculate the maximum cost for each of the other manufacturing tasks and for the total labor. 7. In cell F3, calculate the minimum cost of the box delivery task for the three options. Use the label Min. Cost as the column heading. Calculate the minimum cost for each of the other manufacturing tasks and for the total labor. 8. Several rows below the totals, in column B, calculate the average labor cost of all manufacturing tasks for all three options combined. Blank cells should not be included in the average. Enter the label Average to the left of the calculation. 9. The data for the toe support brace, toe support pad, and back support cushion was omitted in error from the worksheet. The manufacturing group advises to include $1.05 in labor costs to assemble these three items for the textured leather option and to include $.75 in labor costs to assemble these three items for the high top option. Insert a row above the “Assembly of Leather, Sole” task, and include the necessary values and the label Leather Support Assembly. (Recall that the original option does not include these items.) Check all your formulas to ensure they are updated correctly after inserting the new values. 10. Add your name at the end of the exercise and date 11. Save and close the 1-3-Labor Comparision-Your-Name.xlsx workbook.

The results should be similar to:

3

WEEK # 1 - EXERCISES CHAPTER # 1

Exercise 2-4: Level 2- Job Analysis. You work in the Human Resources Department of Devcon Finn, Inc., a computer consulting firm. An employee is considering a transfer to one of the company’s other locations and is qualified for several different positions. Your task is to help the employee choose the most appropriate position based on a number of criteria. For example, you need to determine the value of each position in terms of the disposable income the employee can expect. The position with the highest salary is located in the company’s New York City office, but a studio apartment there costs about $2,650 per month. However, in the New York office, the employee would not need a car. You have documented each position in an Excel worksheet. On this sheet, you have recorded the positions, the annual salary, the cost of living multiplier (which you obtained from a Web site) and estimates of a monthly car payment including insurance, assuming for some of these jobs the employee will need to purchase a car. You have also recorded information regarding starting bonuses (a one-time payment when hired) offered to the employee. Now, you need to finalize the worksheet. Complete the following: 1. Open the workbook named Jobs-1.xlsx located in the Chapter 1 folder, and then save the file as 1-4-Job-Analysis-Your-Name.xlsx. 2. In cells F2:F4, calculate the associated annual adjusted salary.

This adjusted salary is the annual salary divided by the cost of living multiplier minus the expected annual car payments. (Note that car payments are given in dollars per month ($/month).

3. In cell F6, write a formula to calculate the average adjusted salary of the three positions. 4. In cell F7, write a formula to determine the value of the lowest adjusted salary. This formula should automatically update if any of the data inputs are later changed. 5. In cell F8, write a formula to determine the value of the highest adjusted salary. This formula should automatically update if any of the data inputs are later changed. 6. In cell G9, write a formula to determine the number of positions that include a bonus. 7. In cells H2:H4, calculate the value of the adjusted salary package for each position over a two- year period, including bonuses. The bonus does not need to be adjusted for location (multiplied by location factor) because the employee plans to use the bonus toward a vacation. Assume that the employee will receive a 3.5% raise after the first year of employment in the new position.

Adjusted Salary + (Adjusted Salary*1.035) + Bonus 8. Display dollar values in columns E to H without cents, and include a dollar sign only in the first row of columns with dollars. Format the cost of living multipliers with two decimal places displayed, and align these values on the decimal point. 9. Another position for which the employee is qualified has just become available. This position, a senior consultant position, is located in Washington D.C., has an annual salary of $58,000, plus a $1,500 hiring bonus. Because the position is located in downtown Washington, assume that the employee will not be purchasing a car and that the cost of living multiplier is 2.4. Insert the data for this new position just below the New York position. Complete the calculations for the adjusted salary and the total two- year financial package. Verify that all of the other values you’ve calculated update correctly. Adjust the formatting of the new data, as needed, to match the formatting of the existing data. 10. Highlight in yellow the row of the position with the highest two-year financial package, and bold the text in this row. 11. Add your name at the end of the exercise and date 12. Save and close the 1-4-Job-Analysis-Your-Name.xlsx workbook.

4

WEEK # 1 - EXERCISES CHAPTER # 1

Exercise Ch1-5: Level 3- 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.

5

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.

6

WEEK # 1 - EXERCISES CHAPTER # 1

Exercise Ch1-6: Level 3- CKG Sales.

Level 3 – Analyzing Regional Sales Information for CKG Auto As a regional sales manager for CKG Auto, you have just finished summarizing sales data for the first half of this year (January through June), aggregated by car model. You have started to enter data in an Excel worksheet, which lists by model the following information:

• Sales Volume, indicating the number of cars sold to dealers. • Manufacturing (Mfg.) Cost per Vehicle. • Total Cost of all vehicles sold for the model, which is based on the sales volume and the manufacturing costs per vehicle. • Markup Percentage, which is the percentage charged above manufacturing cost to dealers. • Total Sales to dealers, which is the Total Cost plus Markup. Markup equals the markup percentage times the manufacturer cost of the vehicle. • % of Total Volume, which is based on the volume for the model compared to the volume of all models sold for the time period.

First, you need to complete the January through June computations based on the data contained in the worksheet and the information given. Then, you have been asked to create a similar worksheet to estimate sales for July through December based on volume supplied by the marketing group. These volumes are based on the historical values adjusted for seasonal demand of specific models and from market research on the popularity of models. After you have completed both the first half actual sales and the second half estimated sales, combine the data to determine expected yearly sales. Management is interested in not only the absolute value of those sales, but also each model’s contribution to the total yearly sales in each half of the year and in aggregate. When completing the workbook, be sure that all data is correctly referenced so that your formulas will work as you copy them down the column or across the row, as necessary. To complete the workbook, use the following steps: 1. Open the workbook named CKG-1.xlsx located in the Chapter 1 folder, and then save the file as 1-6-CKG-Sales-Your-Name.xlsx. Rename the Sheet1 worksheet tab as 1st Half. 2. In the highlighted cells, enter formulas to perform the necessary calculations for January through June and to summarize. Be sure to write all formulas so that they can be copied as necessary. Note the following:

• Display all dollar values in whole numbers and include the dollar sign in the first row and total rows only. • When calculating averages, your formula should automatically exclude models that had no sales. Display all average values (other than the percentage) with commas and no decimal places. • When calculating summary data (total, average, etc.), keep in mind that additional car models (rows) may eventually be inserted at the bottom of the list. • The formulas in column G need to determine the percent of total volume sales that the vehicle represents. (That is, if model A sold 100 cars and a total of 1000 cars were sold for all models, then model A would represent 10% of the total volume.)

7

WEEK # 1 - EXERCISES CHAPTER # 1

Format the cells in column G to display values to the nearest tenth of a percent. Be sure to calculate the number of models available for sale. 3. Your next task is to estimate the July through December sales based on the marketing data and the sales volume for the second half of the year. The marketing group has provided a list of all car models in identical order to the original data you received with the expected sales volumes for each car model. You can find this list in the Marketing-1.xlsx workbook, located in the Chapter 1 folder. The marketing group assumes that the manufacturing costs and markups will be the same for the second half of the year as they were for the first half. With the data and assumptions in mind, insert a new worksheet named 2nd Half in the 1-6-CKG- Sales-Your-Name.xlsx workbook, identical to the 1st Half worksheet. Copy and paste the sales volumes from the Market.xlsx workbook into your new worksheet. Verify that all the calculations in the new worksheet reflect the new data. TROUBLESHOOTING: In order to complete this task successfully, copy the entire 1st Half worksheet to a new worksheet. Then, copy only the values for the sales volume, excluding the heading, from the Market workbook by highlighting the column Values and clicking the Copy button. Next, place the insertion point in the cell of the new worksheet corresponding to the first vehicle’s volume, and then paste the data. Check the calculated fields (Total Cost, Total Sales to Dealers, etc.) to make sure vehicles that were not sold during the first half of the year have values calculated and vehicles not sold during the second half do not have values. 4. Add another new worksheet named Summary, and include the column headings shown in Table 1.9 on this new worksheet.

a. Insert the model numbers in the identical format as shown on the 1st Half and 2nd Half worksheets. b. Insert the annual volume for each model—the combined totals of the January through June and the July through December volumes. Make sure that the values will automatically update if any of the input values are changed. c. Insert the Jan–June sales to dealers, again ensuring that these values will automatically update if any of the input data changes. d. Insert the July–Dec sales to dealers, again ensuring that these values will automatically update if any of the input data changes. e. Create a combined total of sales to dealers for the entire year. f. Calculate the total volumes and the total sales to dealers for each time period and annually in a row below the data. g. Calculate the percentage of sales to dealers that each model represents, as a percentage of the total sales to dealers for all models, for January through June, July through December, and the year. Use only one formula for this calculation, and make sure that the formula can be copied down

8

WEEK # 1 - EXERCISES CHAPTER # 1

the column to calculate the percentages for the corresponding models and across the row to calculate the percentages for the corresponding time frames. Display the percentages with an appropriate format and number of decimal places. 5. Format all three worksheets so that they have a professional appearance. 6. Add your name at the end of the exercise and date 7. Save and close the 1-6-CKG-Sales-Your-Name.xlsx workbook.