New Era Medical

COMPLETE A BUDGET SUMMARY WORKSHEET

of GETTING STARTED

Open the file SC$\_$EX$19\_1$a$\_$FirstLastName$\_1.$xIsx, available for download from the

SAM website.

Save the file as SC$\_$EX$19\_1$a$\_$FirstLastName$\_2.$xIsx by changing the $"1"$ to a $"2".$

If you do not see the $.$xlsx file extension in the Save As dialog box, do not type it$.$

The program will add the file extension for you automatically.

With the file SC$\_$EX$19\_1$a$\_$FirstLastName$\_2.$xIsx still open, ensure that your first

and last name is displayed in cell B$6$ of the Documentation sheet.

If cell B$6$ does not display your name, delete the file and download a new copy

from the SAM website.

$$ PROJECT STEPS

Josh Bartel is the senior director of the Pennsylvania field office for New Era Medical, a

global company that manufactures medical equipment. He has created a worksheet

summarizing the revenue and expenses for the first three quarters of the year. He asks

for your help in determining what the Pennsylvania office needs to do to meet its

targeted profit goals in the fourth quarter.

Go to the Budget Summary worksheet. Cut the worksheet title from cell I$1$ and paste it

in cell A$1$ to display the title in its expected location. B

In cell E$2,$ add the text Number of so that the complete text appears as "Number of

salespeople:" and more clearly identifies the value in cell G$2.$

Enter Quarter $2$ in cell $C4$ and Quarter $3$ in cell D$4.$

In cell G$5,$ enter a formula without a function that subtracts the target revenue $($cell F$5)$

from the year to date revenue $($cell E$5)$ to determine how much revenue the field office

needs to earn in Quarter $4$ to reach its target for the year.

Josh wants to calculate the gross margin for Quarters $1-3$ and the year to date. Provide

this information as follows:

a$.$ In cell B$7,$ enter a formula without a function that divides the gross profit for

Quarter $1($cell B$6)$ by the revenue in Quarter $1($cell B$5).$

b$.$ Fill the range C$7$:E$7$ with the formula in cell B$7$ to find the gross margin for

Quarters $2$ and $3$ and for the year to date.

Josh needs to sum the sales for each Pennsylvania region for the year to date. Provide

this information as follows:

a$.$ In cell E$10,$ enter a formula that uses the SUM function to total the regional sales

data for the Northeast region $($the range B$10$:D$10)$ to calculate the year$-$to$-$date

sales.

b$.$ Use the Fill Handle to fill the range E$11$:E$13$ with the formula in cell E$10$ to find the

year$-$to$-$date sales for the other three regions.