Answered step by step
Verified Expert Solution
Question
1 Approved Answer
New Era Medical COMPLETE A BUDGET SUMMARY WORKSHEET GETTING STARTED Open the file SC _ EX 1 9 _ CT 1 c _ FirstLastName _
New Era Medical
COMPLETE A BUDGET SUMMARY WORKSHEET
GETTING STARTED
Open the file SCEXCTcFirstLastNamexlsx available for download from the SAM website.
Save the file as SCEXCTcFirstLastNamexlsx by changing the to a
o 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 SCEXCTcFirstLastNamexlsx still open, ensure that your first and last name is displayed in cell B of the Documentation sheet.
o If cell B 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 must do to meet its targeted profit goals in the fourth quarter.
Go to the Budget Summary worksheet. Cut the "New Era Medical" worksheet title and paste it in cell A to display the title in its expected location.
In cell E edit the text so that the complete text appears as Number of salespeople:
Enter Quarter in cell C and Quarter in cell D
In cell G enter a formula without a function that subtracts the target revenue from the yeartodate revenue to determine how much revenue the field office must earn in Quarter to reach its target for the year.
Josh wants to calculate the gross margin for Quarters and the year to date. Provide this information as follows:
a In cell B enter a formula without a function that divides the gross profit for Quarter by the revenue in Quarter
b Fill the Gross margin cell range with the formula in cell B to find the gross margin for Quarters and 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 enter a formula that uses the proper function to total the three quarters of regional sales data for the Northeast region to calculate the yeartodate sales.
b Use the Fill Handle to fill the remaining three regions with the formula in cell E to find the yeartodate sales for the other three regions.
In cell F enter the value to provide the targeted annual sales amount for the Northeast region.
Josh wants to determine how much revenue each region must generate in Quarter to reach its target for the year. Enter this information as follows:
a In cell G enter a formula without a function that subtracts the target sales for the Northeast region from its yeartodate regional sales to find the amount of sales the Northeast region must generate to meet its target.
b Copy the formula in cell G and then paste it in the remaining empty cells in the Variance column pasting the formulas only to find the variances for the other regions and for the total.
Josh is interested in how much revenue each of the salespeople generates on average in each quarter and for the year to date. Find this information as follows:
a Edit cell B to include a formula without a function that divides the total regional sales in Quarter by the number of salespeople. Don't change the reference to cell G that is already there.
b Fill the remaining empty cells in the Revenue per salesperson row in the table with the formula in cell B to determine the average revenue per salesperson in Quarters and and for the year to date.
Josh also needs to calculate the operating margin, which is the ratio of operating profit or loss to revenue and indicates how much the office makes after paying for expenses. Calculate the operating margin as follows:
a In cell B enter a formula without a function that divides the operating profit or loss in Quarter by the revenue from Quarter
b Use the Fill Handle to fill the remaining empty cells in the Operating margin row in the table with the formula in cell B to find the operating margin for Quarters and and for the year to date.
Josh wants to include a visualization of the total yeartodate sales separated by region. Insert a chart to provide this visualization as follows:
a Create a D Pie Chart based on the nonadjacent ranges A:A and E:E
b Enter Total YeartoDate Sales by Region as the chart title.
c Resize and reposition the chart so its upperleft corner is in cell I and its lowerright corner is in cell L
d Apply Style to the chart.
Hide the gridlines for the Budget Summary worksheet to make it easier to read.
Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started