Excel Hardware Budget Problem

Suppose 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 don't 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 8 and Office 2013 - your company has a site license for both of these programs and allocates the cost as $75 for Windows 8 and $100 for Office 2013. Your department will be charged these fees for each computer (even if new computers come with the software pre-installed). 

There are 3 classes of users in your department: Users requiring Class 1 computers need a minimum of 2GB of main memory, 1 GHz processor speed, and an 80 GB hard drive. Users requiring Class 2 computers need a minimum of 4GB of main memory, 2 GHz processor speed, and a 150 GB hard drive. Users requiring Class 3 computers need a minimum of 4 GB of main memory, dual processors with a speed of 2 GHz, and two 250 GB hard drives. 

The IS Department assesses your department $1,000 per computer annually for their services. 

There are currently 15 people requiring Class 1 computers, 20 requiring Class 2 computers, and 5 requiring Class C computers. 

2 more people are expected to be hired next year that will need Class 1 computers. 

Each current employee has a computer. There are no extra computers. Class 1 computers currently have 256 MB of RAM, CPUs with a speed of 0.5 GHz, and 30 GB hard drives. Class 2 computers currently have 512 MB of RAM, CPUs with a speed of 1 GHz, and 80 GB hard drives. Class 3 computers currently have 1GB of RAM, CPUs with a speed of 2 GHz, and two 125 GB hard drives. 10 of the Class 2 computers have a maximum main memory of 512 MB and cannot be upgraded. The rest of the computers are fully upgradable. You may find it more cost effective to allocate an 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.

As manager of the department, you will get a new computer. You can determine the requirements for it. 

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). 

HINT: 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 the limit some of the class 2 computers have), also from at least 2 vendors. Then you need to choose your most cost effective way to meet the new requirements - totaling the hardware and software (Windows 8, Office 2013, 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.