Reference no: EM13855056
Problem 1:
Fresh Blooms, LLC is a floral wholesaler. You want to create an invoice template that references the list of products available using the VLOOKUP function. Copy the invoice template below into a new workbook and populate the highlighted cells with a VLOOKUP function referencing the product table. Hint: you will need to use an IF logical test.
| Code |
Product |
Price |
| A23 |
Tulips |
10.00 |
| B14 |
Roses |
15.00 |
| A27 |
Carnations |
5.00 |
| C45 |
House plants |
12.00 |
|
Invoice Template |
|
|
| |
|
|
|
|
| Invoice # |
74632 |
|
|
|
| Date |
1/14/2010 |
|
|
|
| |
|
|
|
|
| |
|
|
|
|
| Code |
Product |
Unit Cost |
Quantity |
Total |
| A23 |
|
|
3 |
$ - |
| A27 |
|
|
2 |
$ - |
| B14 |
|
|
1 |
$ - |
| C45 |
|
|
4 |
$ - |
| |
|
|
|
$ - |
| |
|
|
|
$ - |
| |
|
|
|
$ - |
| |
|
|
|
|
| |
|
Subtotal |
|
$ - |
| |
|
Tax |
10% |
$ - |
| |
|
Total |
|
$ - |
Problem 2:
Below is select sales data for RX Industries. The orders range from 1 to 20, however any returned or canceled orders have been deleted from the data. Use the template below to create an order summary that automatically shows the data for any selected order ID between 1 and 20. Hint: Use the IFERROR and HLOOKUP functions. The function for any order ID in the range that has been deleted should return the phrase "Not Found."
| OrderID |
1 |
7 |
9 |
13 |
16 |
2 |
3 |
| Region |
East |
East |
East |
East |
East |
West |
West |
| OrderDate |
1/10/2009 |
3/6/2009 |
3/28/2009 |
4/8/2009 |
4/12/2009 |
5/16/2009 |
5/23/2009 |
| OrderAmt |
$1,122.00 |
$ 4,453.00 |
$ 1,305.00 |
$2,708.00 |
$1,502.00 |
$4,796.00 |
$1,526.00 |
| 5 |
10 |
14 |
4 |
8 |
11 |
17 |
18 |
| West |
West |
West |
Central |
Central |
Central |
Central |
Central |
| 6/2/2009 |
7/8/2009 |
7/30/2009 |
8/29/2009 |
9/9/2009 |
10/5/2009 |
11/16/2009 |
12/25/2009 |
| $1,523.00 |
$2,068.00 |
$1,597.00 |
$ 781.00 |
$2,976.00 |
$4,145.00 |
$3,979.00 |
$2,044.00 |
Problem 3:
Below is the December 2009 sales data bysales representative for Papier Nouveau, a distributor of commercial printing supplies. As the Director of HR for Papier Nouveau, you are responsible for calculating monthly incentives. In previous months, bonuses were based on sales volume, however this month you are considering basing them on profit margin. Create pivot tables to help you answer the following questions in analyzing the December sales:
a) What is the highest volume of our highest margin product sold in December?
b) What is the average total profit generated for the month?
c) What is the total number of commercial copiers sold during December?
| Employee ID |
Region |
Product |
Profit Margin per Unit |
Volume Sold |
| 11048 |
SW |
Cartridges |
$ 7.00 |
200 |
| 11048 |
SW |
Paper (ream) |
$ 25.00 |
1,900 |
| 11048 |
SW |
Printers |
$ 103.00 |
7 |
| 12345 |
NE |
Commercial Copiers |
$ 593.00 |
4 |
| 12345 |
NE |
Paper (ream) |
$ 25.00 |
2,000 |
| 12345 |
NE |
Printers |
$ 103.00 |
8 |
| 26427 |
MW |
Cartridges |
$ 7.00 |
600 |
| 26427 |
MW |
Paper (ream) |
$ 25.00 |
7,300 |
| 26427 |
MW |
Printers |
$ 103.00 |
4 |
| 62380 |
NW |
Fax Machine |
$ 121.00 |
7 |
| 62380 |
NW |
Laminating Machine |
$ 375.00 |
3 |
| 62380 |
NW |
Printers |
$ 103.00 |
21 |
| 67890 |
SW |
Cartridges |
$ 7.00 |
820 |
| 67890 |
SW |
Commercial Copiers |
$ 593.00 |
6 |
| 67890 |
SW |
Paper (ream) |
$ 25.00 |
980 |
| 73628 |
NW |
Commercial Copiers |
$ 593.00 |
7 |
| 73628 |
NW |
Fax Machine |
$ 121.00 |
8 |
| 73628 |
NW |
Paper (ream) |
$ 25.00 |
4,900 |
| 82745 |
SE |
Cartridges |
$ 7.00 |
500 |
| 82745 |
SE |
Commercial Copiers |
$ 593.00 |
2 |
| 82745 |
SE |
Paper (ream) |
$ 25.00 |
750 |
| 92857 |
NE |
Laminating Machine |
$ 375.00 |
5 |
| 92857 |
NE |
Paper (ream) |
$ 25.00 |
200 |
| 92857 |
NE |
Printers |
$ 103.00 |
11 |
| 95202 |
SE |
Fax Machine |
$ 121.00 |
2 |
| 95202 |
SE |
Paper (ream) |
$ 25.00 |
5,000 |
| 95202 |
SE |
Printers |
$ 103.00 |
9 |
Attachment:- franchise-data for regression problem.xlsx
|
Describe neuroscience and neuroplasticity
: Answer the following questions. Your Assignment should be at least two full double-spaced pages in length, using size 12-point font in Word format. Be sure your paper is well written in paragraph form, with correct spelling, grammar, and punctuation.
|
|
What sort of business-level strategy should bjc pursue
: What sort of business-level strategy should BJC pursue? Should it focus more on a cost leadership, no-frills model like the Wrangler or BJ models.
|
|
How can bjc capitalize on its huge brand name recognition
: What can it do to keep this information proprietary, given China's shoddy record of protecting intangible property rights? How can BJC capitalize on its huge brand name recognition and technological superiority?
|
|
Employment relationship and discrimination
: Employment Relationship and Discrimination" Please respond to the following: Contrast the primary differences between independent contractors, temporary employees and volunteers. Then, examine two (2) ways in which each role differs from that of ..
|
|
What is the average total profit generated for the month
: What is the highest volume of our highest margin product sold in December and what is the average total profit generated for the month?
|
|
Analyze extrinsic and intrinsic organization issues
: Analyze Extrinsic and Intrinsic Organization Issues - You have just been promoted to the Director of Labor Relations at the 400-bed hospital. You are tasked with describing what you believe to be the salient extrinsic and intrinsic issues for your..
|
|
Course concerns addressing social problems in society
: One of the most crucial components to this course concerns addressing social problems in society from a sociological viewpoint as opposed to a strictly policy-oriented, psychological, or criminal justice viewpoint. Although many different viewpoints ..
|
|
Basic manipulation with pointers
: The aim of this homework is to get familiar with basic manipulation with pointers and simple/double linked lists, such as: finding (searching for) a node in a list, dynamically adding/deleting a node in a list, creating new list, printing a list, ..
|
|
Critique the trends and issues in union membership
: Address the following in your presentation: Critique the trends and issues in union membership that have led to the decrease of a represented workforce. Recommend the beginnings of a strategic plan that reduces the desire of workers to unionize
|