Excel Assignment

Tutorial 8, Case Problem 4, pgs 482 – 485 – You
 will submit the following documents from the Alia’s
 Senior Living workbook:  Documentation sheet, Invoice and Product Pricing and Shipping sheet.

In step 4o, the best solution for the shipping cost is an IF Function with a nested vlookup

 (the IF checks if the subtotal is >=200 and thevlookup references the shipping cost table).

Below is a link to the book…

https://www.dropbox.com/s/2eyf12hgbv3jmx5/New%20Perspectives%20on%20Microsoft%20Excel%202010%20Comprehensive

Here is a dropbox link to the data files…

https://www.dropbox.com/sh/24hij3u0lff4o0h/HA9YTlylIs

   

2. Complete Tutorial 10, Case Problem 4 (through Step 4 only) on pages 611 – 612. There are no data files for this assignment – you will complete this worksheet from scratch. The following instructions will help you complete this assignment.

3. Save the workbook as: xx NewGen Robotics (replace xx with your first and last initials).

4. When creating the income statement for Step 2 in the textbook, use the categories (row labels) listed in Figure 10-5 on page 558, but not the data. Use the data (numbers) shown in step 2 on page 611. Create formulas for: Total Revenue, Total Material Cost, Total Manufacturing Cost, Total Variable Expenses, Total Fixed Expenses, Total Revenue, Total Expenses and Net Income.

5. Create a formula in the Income Statement for Units Produced that assumes that the company produces enough cleaning robots to have a 1 percent surplus over Units Sold.

6. Format the income statement professionally and name the sheet tab Income Statement. Create a copy of the Income Statement sheet and name the copied sheet tab Goal Seek.

7. In the Goal Seek sheet you created above, use the Goal Seek feature to complete Step 3 in the text.

8. To complete Step 4 in the text, create the one-variable data table on the Income Statement sheet. Name the data table Cost-Volume-Profit Analysis. Do not complete the CVP chart.

9. Make sure each sheet in the workbook will print on one page. Save and print the finished workbook. You will submit the xx NewGen Robotics workbook to the Week 10 –

 

This assignment is not in your textbook. Complete the steps listed below:

Problem: Ellen Felton is events coordinator at Kirk Harbor Inn, which is located on Cape Cod. She schedules weddings, conferences, engagements, and so forth at this water-front Victorian inn. She constantly is sending quotes to potential clients and asks you to assist her in linking the workbook she developed to the letter she sends to potential clients.

Complete the following:

1. Open the NewQuote_2010 workbook located in the Appendix C misc folder and then save it as xx Harbor Quote (replace xx with your first and last initials).

2. In the Documentation sheet, enter the date and your name, and then switch to the Quote worksheet.

3. Open the Event document located in the Appendix C misc folder and then save the document as xx Event Planner.

4. Return to the Harbor Quote workbook, and then copy the range C2:G19 in the Quote worksheet.

5. Return to the Event Planner document, and then paste the selected range as a link below the sentence “Here are the details.”

6. Ellen’s client requests two changes: move the wedding to the Salon room and change the number of guests to 160. Make these changes in the Harbor Quote workbook, and then verify that the Event Planner document is updated.

7. Create a pie chart of expenses in the Harbor Quote document: select the ranges F13:G14 and F16:G18 (do not select the Subtotal or Grand Total) and insert a 3-D Pie chart.

8. Add a Chart Title above the chart that reads: Wedding Expenses.

9. Copy the chart and embed it in the Event Planner Word document. Place it directly after the Excel worksheet (before the sentence that reads: “We look forward to working with you in helping to make this special event a memorable one for you.”)