Reference no: EM13464349

You are the technical director for a company with 1500 employees. The Chief executive officer has asked you to prepare a plan for purchasing and installing new computers (including desktops & laptops). Approximately 800 employees use desktop computers and the other 800 employees use laptop computers. Your budget for each computer is $1,500. Computers should be configured to last at least three years

• Desktop and laptop computers you compare and contrast should be current. In your document, you should include the company and website where you found the information (you should use the manufacturers' websites to obtain information and pricing). This will be verified by the instructor. Including information about computers that are not currently available will result on a grade of zero on this assignment.

Background Information: Now that you have done the research on the various desktops and laptops you might recommend for your organization, you can create an Excel spreadsheet that will calculate the costs associated with purchasing each of these computers.

All instructions in this assignment should be completed in order. If they are completed out of order, you might have trouble performing some steps.

Content Requirements:

• The worksheet will have a total of two tables: one table for desktop computers, and one table for laptop computers. There should only be 3 computers for each table.

• In the first column of each table, include a brief description for each of the three desktop computers, and each of the laptop computers. The descriptions should not include the technical specifications.

• Next to the description column, include unit prices for each of the three desktop computers and each of the laptop computers.

• Next to the unit prices, included the quantities you plan to purchase for each type of computer. The quantity for each desktop computer should be 800, and the quantity for each laptop computer should be 800.

• Next to quantity, include a column in your table that will calculate the Subtotal.

• Next to the Subtotal (also called the Total Price), add a column in the table that will calculate the tax.

• Next to the Sales Tax column, add a column that will calculate shipping.

• Next to the Shipping column, add a column that will calculate the grand total for each computer.

• Add a row at the bottom of each table that will calculate the average unit price, average total price, and average grand total.

• Add a row at the bottom of each table that will calculate the maximum value in the Unit Price column, Total Price column, and Grand Total column.

• Add a row at the bottom of each table that will calculate the minimum value in the Unit Price column, Total Price column, and Grand Total column.

• Be sure there are at least three blank rows at the top of the worksheet (these rows should not be part of the table). If there are not three blank rows, insert the necessary number of rows above the table until there are three (these empty rows will be used for the spreadsheet title and subtitle).

Technical Requirements:

In addition to meeting the content requirements for this assignment, you also will need to demonstrate your proficiency of Microsoft Excel by applying the following formatting. Please note that you should not perform any calculations manually; if a cell should contain a calculated value, use a function or formula to calculate that value. The functions and formulas should automatically recalculate if the source values are modified.

• Change the worksheet tab so that it displays "Computer Purchases" instead of "Sheet1." Also, change the tab color to one of your choosing.

• Create a title on your worksheet in cell A1, and a subtitle in cell A2. Change the font, font size, font color, and fill color from the default values, and then Merge & Center the cells across the top of the worksheet.

• In the Subtotal (also called Total Price) column, use an Excel formula that calculates the Quantity multiplied by the Unit Price. You must use an Excel formula with cell references in order to receive credit for this step.

• In the Sales Tax column, use an Excel formula that calculates Subtotal x Sales Tax. You must use an Excel formula in order to receive credit for this step.

• In the Shipping column, use the Excel IF function to calculate the shipping. You must use an Excel formula with cell references in order to receive credit for this step.

o If the Total Price for the computer purchase is less than $800,000, shipping should be calculated as 7% of the total price. 

o If the Total Price for the computer purchase is greater than or equal to $800,000, shipping should be calculated as 5% of the total price.

• In the Grand Total column, use an Excel formula to add the Total Price, Sales Tax and Shipping to determine the final costs for each of the 3 desktop and 3 laptop computer options. You must use an Excel formula with cell references in order to receive credit for this step.

• In the Average Row on each table, use an Excel function to calculate the average unit price, total price, and grand total. The average is not to be completed for the quantity, sales tax, or shipping.

• In the Maximum Row on each table, use an Excel function to calculate the maximum unit price, total price, and grand total. The maximum is not to be completed for the quantity, sales tax, or shipping.

• In the Minimum Row on each table, use an Excel function to calculate the minimum unit price, total price, and grand total. The minimum is not to be completed for the quantity, sales tax, or shipping.

• All column widths should be set to properly display all contents in the column (nothing should be cut off, there should not be an unnecessary symbols, and columns shouldn't be so wide that there is a lot of blank space in each.