Excel Hardware Budget Problem

Note: This assignment does not require many Excel skills, but rather problem-solving skills. You dont have to know anything about computers to complete it. For this assignment, we pretend that the only items that need to be upgraded are the CPU, the RAM, and the Hard Drive. We also pretend that any item will work in any computer. You can use Google to find prices; click on the Shopping tab in Google to filter the results.

You are the manager of a department within a large company. $60,000 has been budgeted for computer upgrades in your department next year. Cost control is important, so you dont want to overspend. If you need to ask for more money than was budgeted, you will need documentation to justify the request. The information systems needs of your department are listed below:

All computers will need to be upgraded to Windows 10 and Office 2016 your company has a site license for both of these programs and allocates the cost as $75 for Windows 10 and $100 for Office 2016. Your department will be charged these fees for each computer (even if new computers come with the software pre-installed). Also, the IS Department charges your department $1,000 per computer annually for their services. Many companies allocate costs from support departments like Information Systems that do not generate income to departments (like yours) that do generate income. These amounts have been established by the company; you do not have any choice. These amounts must come out of your budget.

There are 3 classes of users in your department: Users requiring Class 1 computers need a minimum of 4 GB of main memory, 2 GHz processor speed, and a 500 GB hard drive. Users requiring Class 2 computers need a minimum of 8 GB of main memory, 2 GHz processor speed, and a 1 TB hard drive. Users requiring Class 3 computers need a minimum of 16 GB of main memory, dual processors with a minimum speed of 2.5 GHz, and a 2 TB hard drive.

There are currently 15 people requiring Class 1 computers, 20 requiring Class 2 computers, and 5 requiring Class 3 computers. 2 more people are expected to be hired next year that will need Class 1 computers. As manager of the department, you will get a new computer. You can determine the requirements for it.

Each current employee has a computer. There are no extra computers. Class 1 computers currently have 1 GB of RAM, CPUs with a speed of 1 GHz, and 250 GB hard drives. Class 2 computers currently have 2 GB of RAM, CPUs with a speed of 2 GHz, and 500 GB hard drives. Class 3 computers currently have 4 GB of RAM, CPUs with a speed of 2 GHz, and a 1 TB hard drives. 10 of the Class 2 computers and cannot be upgraded (you can use their components in other computers). The rest of the computers are fully upgradable. You may find it more cost effective to. allocate existing computer to a new class and buy a new one in its existing class. You can also take parts from existing computers and use them in other computers. Assume that any parts you can find will work with the existing computers.

Create a spreadsheet to compute the cost of new computers and the cost of upgrading computers when applicable and purchasing new ones for the remainder. Construct it so that you can change prices and charges so that it will update the Total Cost per Category as well as the total cost (use formulas based on cell references, so that it will automatically update).

HINTS: First put all of your existing data into a spreadsheet, making sure to label it well so that you (and I) can tell what the figures represent. Then you will need to determine the cost of new computers for each class you need to find prices from at least two vendors (Internet sites). Then you will need to determine the upgrade costs (RAM, CPU, and Hard Drive - remember that 10 of the class 2 computers cant be reused but their parts can), also from at least 2 vendors. List the names/web sites of your vendors. Then you need to choose your most cost-effective way to meet the new requirements totaling the hardware and fixed (Windows 10, Office 2016, and IS Dept. cost allocation) costs and determining if it can be done with the amount that was budgeted to your department. If you go over budget, document why you need additional funds.

You currently have 40 computers, 10 of which cant be upgraded, and need a total of 43 so a maximum of 30 can be upgraded and a minimum of 13 new computers must be purchased.

Everyones spreadsheet will look different. There is no one right answer. Be sure to use formulas for adding up the costs. Document your spreadsheet to show what you are doing in your decision for example, show that you are purchasing X new class Y computers, or changing X Class Y computers to Class Z computers. You can also use existing hardware in other machines (upgrading a systems hard drive makes its old hard drive available to put elsewhere). Dont worry about the format you use for laying it out as long as I can easily tell what your figures stand for (label well).

I know this is a lot of information and it seems confusing just put the information in your spreadsheet and you should be able to make sense of it all. If you have missed something, or have some other problem, I will let you know and you will have the opportunity to fix and resubmit.

Name the spreadsheet LastName Hardware Budget (using your last name) and submit it in the Hardware Budget drop box.

You may want to start by filling out the information in the following tables (you are not required to do this).