Set up Figures 9-5 and 9-6 in spreadsheets in an Excel workbook. The spreadsheet for Figure 9-5 should allow the user to recalculate the social security, Medicare, FUTA, and SUTA costs by changing the social security rate and limit, Medicare rate, FUTA rate and limit, SUTA rate and limit, the wages for salaried employees, and the hours [worked] per week and wage rate for hourly employees. Use the concepts presented in Sidebar 9-1 to set up this spreadsheet.

FIGURE 9-5 Employee Costs

FIGURE 9-6 General Overhead Budget

When a change is made to the spreadsheet for Figure 9-5, the employee wages and salaries, the employee taxes, and insurance should automatically change in the spreadsheet for Figure 9-6.

Transcribed Image Text:

Social Security Rate: 6.20% Social Security Limit:97500 Medicare Rate FUTA Rato: FUTA Limit SUTA Rate: SUTA Limit 1.45% 0.80% 7.000 2.00% 22,000 Jan. Fob March April May Jno Oct. Nov. Dec,Total Wages Social Socurity 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 120,000 6,045 145 145 145 145 145 145 145 145 145 145 145 145 1.740 620 620 620 620 620 620 620 620 620 FUTA SUTA 200 200 Projoct Managor/Estimator Wages Social Security 5,000 5,000 5,000 5.000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 60,000 310 310 310 310 310 310 310 310 310 310 310 310 3,720 FUTA SUTA 100 100 100 100 Office Salaries 15.000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 ,000 15,000 15,000 15,000 180,000 Secretary/Receptionist Hours Per Woek Wage Rate Wages Social Socurity 42 42 2,236 2,236 2,236 2236 2236 2,236 2,236 2,236 2,236 2,236 2.236 2236 26,832 139 139130 139 139 139 139 139 139 139 1.664 FUTA SUTA 5 Hours Per Weok Wage Rate Wagos Social Security 45 45 3,705 3,705 3,705 3,705 3,705 3,705 3.705 3,705 3,705 3,705 3,705 3,705 44,460 230 230 230 230 230 230 230 230 230 230 230 230 2757 54 54 FUTA 74 74 74 74 74 69___ Hours Per Week Wage Rate Wages Social Security 42 42 1,677 1,677 1,677 1.677 1,677 1,677 1,677 1,677 1,677 1,677 1677 1677 20.124 104 104 104 104 04 04 041040404 104 1,248 24 24 24 FUTA SUTA 34 7,618 7,618 7.618 7618 7,618 7,618 7,618 7,618 7,618 7,618 7,618 7,618 91,416 22,618 22,618 22,618 22618 22.618 22,618 22,618 22,618 22,618 22,618 22618 22618 271,416 34 Hourly Wages Total Wages Taxes Social Security 1,402 1,402 1,402 1,402 ,402 1,402 1,402 1,402 1,402 1,247 782 782 15,433 328 328 328 3,936 328 328 328 329 328 328 328 FUTA SUTA TOTAL 452 452 292 252 192 14878 2,340 2,256 2.054 1,998 1,925 1,878 1,809 1,809 180 1.646 1.144 1,144 21,811 General Overhead 3,910 3,420 4,600 4,920 5,160 68,400 1 Car and Truck Expenses Computer and Office Furniture Employee Wages and Salaries 22,618 22,618 22,618 22,618 22,618 22,618 22,618 22,618 22,618 22,618 22,618 22,618 271,416 2,340 2,256 2,054 1,998 1,925 1,878 3 3 2 4 Dues and Memberships Legal and Professional Services 1,000_ 1,000 43,972 41,949 42,742 46,946 39,238 39,066 65,321 36,686 35,851 38,459 37,537 55,897 523,663