Ask Corporate Finance Expert

Question 1:

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

Question 2:

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    $           -  

Question 3:

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 5 10 14 4 8 11 17 18
Region East East East East East West West West West West Central Central Central Central Central
OrderDate 1/10/2009 3/6/2009 3/28/2009 4/8/2009 4/12/2009 5/16/2009 5/23/2009 6/2/2009 7/8/2009 7/30/2009 8/29/2009 9/9/2009 10/5/2009 11/16/2009 12/25/2009
OrderAmt  $1,122.00  $  4,453.00  $   1,305.00  $2,708.00  $1,502.00  $4,796.00  $1,526.00  $1,523.00  $2,068.00  $1,597.00  $ 781.00  $2,976.00  $4,145.00  $3,979.00  $2,044.00

Corporate Finance, Finance

  • Category:- Corporate Finance
  • Reference No.:- M9495338

Have any Question?


Related Questions in Corporate Finance

Business finance case study assignment -instructions - you

BUSINESS FINANCE CASE STUDY ASSIGNMENT - Instructions - You must do Questions 1-5a, 8 and 10 on a spreadsheet. Eternal Youth Ltd (EY) is a New Zealand company which produces and sells cosmetics. Its financial year is 1 J ...

Q1 delta hedgingon sept 30th 2011 exxon mobil xom stock was

Q1 (Delta Hedging) On Sept 30th, 2011, Exxon Mobil (XOM) stock was traded at $72.63 while the December XOM put option with $75 exercise price is traded at $5.00 and the December XOM call option with $70 exercise price is ...

Q1 delta hedgingon sept 30th 2011 exxon mobil xom stock was

Q1 (Delta Hedging) On Sept 30th, 2011, Exxon Mobil (XOM) stock was traded at $72.63 while the December XOM put option with $75 exercise price is traded at $5.00 and the December XOM call option with $70 exercise price is ...

Assignment -part a - saturn petcare australia and new

Assignment - Part A - Saturn Petcare Australia and New Zealand is Australia's largest manufacturer of pet care products. Saturn have been part of the Australian and New Zealand pet care landscape since opening their firs ...

Mini case assignment -problems - use internet to identify a

Mini Case Assignment - Problems - Use internet to identify a house or condo that you may be interested in investing as a rental property for 10+ years. (Suggested price range between $250k - $1 million) 1. Estimate the a ...

Descriptionstudents are required to study undertake

Description: Students are required to study, undertake research, analyse and conduct academic work within the areas of corporate finance. The assignment should examine the main issues, including underlying theories, impl ...

Corporate finance assignment - required this assessment

Corporate Finance Assignment - Required: This assessment task is a written report and analysis of the financial performance of a selected company in order to provide financial advice to a wealthy investor. It will be bas ...

Interest swap valueabc bank has agreed to receive 3-month

Interest swap value ABC bank has agreed to receive 3-month LIBOR and pay 8% per annum on a notional principal of $100 million. The swap has a remaining life of 11 months. The LIBOR spot rates for 2-month, 5-month, 8-mont ...

Graph an event study relationshipthe event in consideration

Graph an event study relationship. The event in consideration here is: "Environmental performance, being green, clean-tech, corporate sustainability, and many other "green" issues are on the forefront of the current econ ...

Question - assume that the average firm in your companys

Question - Assume that the average firm in your company's industry is expected to grow at aconstant rate of 6 percent and its dividend yield is 7 percent. Your company is about as risky as the average firm in the industr ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As