Public Relation - Social Media
Joann211684
Assignment3.docxCAIS 201 – Introduction to CAIS
Fall 2019
Assignment #3
Due Date and Time: October 3rd , Before 9 AM.
All assignments must be typed in MS Excel (No MS Word No PDF) and must be submitted via Canvas.
Please see the instructions posted in Canvas for online assignment submission
Suppose that you are the manager of a small e-commerce business buying computer parts in batches and selling them online. There are 5 products in the inventory and monthly inventory counts are given in the following table.
|
INVENTORY |
|||||||||||||
|
PRODUCT |
Dec-17 |
Jan-18 |
Feb-18 |
Mar-18 |
Apr-18 |
May-18 |
Jun-18 |
Jul-18 |
Aug-18 |
Sep-18 |
Oct-18 |
Nov-18 |
Dec-18 |
|
Product 1 |
250 |
394 |
586 |
606 |
994 |
390 |
426 |
531 |
230 |
331 |
883 |
84 |
347 |
|
Product 2 |
2325 |
2,347 |
466 |
1,323 |
2,059 |
2,822 |
836 |
1,545 |
904 |
1,796 |
1,064 |
1,695 |
2,006 |
|
Product 3 |
1800 |
2,014 |
1,869 |
2,035 |
2,344 |
1,818 |
1,601 |
1,663 |
1,750 |
944 |
1,397 |
2,055 |
2,687 |
|
Product 4 |
1826 |
2,741 |
1,490 |
1,607 |
1,077 |
357 |
1,254 |
620 |
1,308 |
1,946 |
194 |
318 |
319 |
|
Product 5 |
600 |
821 |
1,040 |
447 |
1,002 |
1,153 |
1,457 |
157 |
379 |
384 |
703 |
250 |
675 |
-The company has a commitment of purchasing 1000 product/month for each one to its suppliers and the order cost for each product is given in the following table.
|
PRODUCT |
Order Cost / Product |
|
Product 1 |
$ 10.00 |
|
Product 2 |
$ 12.00 |
|
Product 3 |
$ 15.00 |
|
Product 4 |
$ 20.00 |
|
Product 5 |
$ 5.00 |
-The monthly holding cost of each unsold product in the warehouse is given in the following table
|
PRODUCT |
Holding cost / Product |
|
Product 1 |
$ 0.20 |
|
Product 2 |
$ 0.10 |
|
Product 3 |
$ 0.25 |
|
Product 4 |
$ 0.20 |
|
Product 5 |
$ 0.15 |
-The shipping cost for each product sale is $3.00
-The sales price for each product is given in the following table
|
PRODUCT |
Order Cost / Product |
|
Product 1 |
$ 16.00 |
|
Product 2 |
$ 20.00 |
|
Product 3 |
$ 25.00 |
|
Product 4 |
$ 27.00 |
|
Product 5 |
$ 8.00 |
Questions
Calculate the followings from (Jan 18 to Dec 18)
1. The monthly sales quantity for each product (30 pts)
2. The total order cost per month (10 pts)
3. The total shipping cost per month (10 pts)
4. Monthly Revenue (10 pts)
5. Monthly Net Income (10 pts)
6. Cumulative Income (15 pts)
7. Draw a line graph for Cumulative Income from Jan 18 to Dec 18 (15 pts)
TIPS
· You need to find out the monthly sales quantity for each product which is (= 1000+the inventory amount from the previous month – the inventory in that month)
· Holding cost is directly related to the inventory (the unsold products every month)
· Use =SUMPRODUCT(Column1, Column2) function to calculate the total order cost, shipping cost and so on
