$2-19.$ Excel Application: IT Benchmarks

Jay$$s Bikes is a family$-$owned and $-$operated business that stocks a wide range of bikes designed to fit the needs of professional riders, your child$$s first bike, and everything in between. The business has $12$ full$-$time employees. Jay has asked you to create a spreadsheet from the data in Figure $2-21$ to calculate average IT spending so that it can be compared to the retail industry average. What is the average IT spending in the retail industry? What is the average IT spending per employee in the retail industry? How do Jay$$s IT expenditures compare to the industry averages? How much would Jay need to increase spending in order to match the retail industry average?

The average IT spending in retail is $1\mathrm{.}8$ percent of revenue. The average IT spending per employee in retail is $$4,500.$

Students should use formulas to calculate Jay$$s average IT spending as a percent of revenue and his average IT spending per employee, $($screen shots with these numbered steps are shown below$)$

calculate Jay$$s total Revenue;

calculate Jay$$s total IT Expenditures;

calculate Jay$$s IT spending as percent of Revenue, which is done by dividing Jay$$s total IT Expenditures by Jay$$s total Revenue;

calculate Jay$$s IT spending per Employee, which is done by dividing Jay$$s total IT Expenditures by the number of Jay$$s Employees;

We know the average IT spending benchmark is supposed to be $1\mathrm{.}8\%$ and that average IT spending per employee should be $$4,500,$ but we cannot just enter those numbers into the data cells or Goal Seek will NOT work! So we must start with formulas in those cells.

So for the percent of revenue benchmark that$$s $=$D$25/$C$9$ this will show $0\mathrm{.}0\%$ until after the Goal Seek function is performed.

For the spending per employee calculate benchmark that$$s $=$D$26/$C$20$ this will show as null until after the Goal Seek function is performed. $(*$See the first screen shot below for these steps.$)$