Excel Hardware Budget Problem

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.

Be sure to use formulas for adding up the costs.

Existing Computers

Class	Amount	CPU Speed	RAM size	Hard Drive size
1	15
2	20
3	5

Computers Needed

Class	Amount	CPU Speed	RAM size	Hard Drive size
1	17
2	20
3	5
Manager	1

Purchase/Reuse

Number	Computers		CPU			RAM		Hard Drive
	Buy	Reuse	Buy		Reuse	Buy	Reuse	Buy	Reuse
Class 1 Computers
Class 2 Computers
Class 3 Computers
Manager Computer

Items to Purchase

Item	Vendor 1	Vendor 1 Price	Vendor 2	Vendor 2 Price