Spreed Sheet Assigment

1

WEEK # 2 - EXERCISES CHAPTER # 1

Exercise 1-1: Level 1 – Purchasing a Computer for Durban & Associates You are currently employed at a medium-sized financial management firm, Durban & Associates, as a financial analyst. Your boss recently authorized the purchase of a new laptop computer for your use at home and in the office. She asked you to obtain three competitive bids before she approves the purchase. The minimum requirements of the system you want to buy are as follows:

2

WEEK # 2 - EXERCISES CHAPTER # 1

Complete the following:

1. Create a new workbook named 1-1-Computer-Purchase-YourName.xlsx, and then save the file in your folder. Create a worksheet that compares the cost of each of the listed items for the three computers. Organize the worksheet so that each component is listed separately. If an item is included in the base computer price, enter a zero.

Be sure to include the following elements:

• A title formatted in Cambria, size 14, bold, and italic. Merge and center the title above your worksheet and add a light blue fill.

• Appropriate column and row headings so that your worksheet is easy to understand. If necessary, wrap the text headings into more than one row in the cell.

• Numbers in the first row and in any summation rows formatted with the Accounting Number Format. Format all other dollar values with the Comma Style. Note that zeros will appear as dashes.

3. Calculate the total cost of each system, assuming all items listed for that system are purchased, and then format the totals with Accounting Number Format. Enter a label for the row of total, right align the label, and use bold italic formatting. 4. Verify that the totals are accurate even if other values are later substituted for any of the system component costs. 5. Highlight the cell containing the name of the least expensive computer system in yellow. 6. You learn from your boss that the corporation is planning to purchase at least 40 similar systems. Because of this volume, the following price reductions are now available:

• Dell has agreed to a rebate of $150 per machine. • Sony has agreed to give an 18% across-the-board discount on everything but shipping,

which remains at $46 per system. • Lenovo has declined to give any volume purchase discount.

Skip at least two rows at the bottom of your current data. In a separate area, calculate the total cost of a single machine from each competitor using this new pricing structure. Reference the values you have previously calculated as needed. 7. Just below the calculation for Step 6, calculate the cost of purchasing the 60 machines with this new pricing structure for each option. 8. Highlight the cell containing the lowest final cost for 60 machines in a shade of light blue. 9. Save and submit the 1-1-Computer-Purchase-YourName.xlsx workbook.

3

WEEK # 2 - EXERCISES CHAPTER # 1

Exercise 1-2: Level 2- Job Analysis 2. You work in the Human Resources Department of 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 Job-Initial.xlsx attached with the data of the assignment, and then save the file as 1-2-Job-Analysis2-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 5.5% raise after the first year of employment in the new position.

Adjusted Salary + (Adjusted Salary*5.5%) + 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.

4

WEEK # 2 - EXERCISES CHAPTER # 1

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-2-Job-Analysis2-Your-Name.xlsx workbook.