Public Relation – Social Media

Please see attached document for the assignment details

CAIS201 – 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