RMIT ISYS2059 Assignment 3 Business Info system PDF

Preview

Summary

Here is my assignment...

Description

INTERI ORS Business Report INTERIOR S

Student: Student ID: Business Information System (ISYS 2059) LF04 Class: 12pm Monday Tutor: Mr Sam Lai

2 Interiors BusInteriors Business Report

Table of Contents 1.

Introduction.................................................................................................................................2

2.

Analysis........................................................................................................................................2 a.

Quality Initiative........................................................................................................................2

b.

Contract Review........................................................................................................................5

c.

Volume Utilisation.....................................................................................................................6

3.

Conclusion....................................................................................................................................8

4.

Recommendation.........................................................................................................................9

5.

Appendix....................................................................................................................................10

1

Gwendolyn Ng Shu Yee (S3834172)

2 Interiors BusInteriors Business Report

1. Introduction Interiors is an Australian soft furnishing retail chain company with almost 100 outlets across the country. NatFreight has been engaged by Interiors to despatch goods to the respective stores. There are currently three classifications of stores, small, medium and large. A quality initiative was introduced from week 14 onwards to reduce freight costs by lessening the amount of “pallet baskets”. New South Wales and Western Australia’s large and medium country stores are to consolidate and reduce any loads. This report will analyse and discuss on the impact of the quality initiative, display calculations for both current and future cost savings as well as costs comparison with an alternative transport provider – SunnyDels to make recommendations for future actions that Interiors should adopt to reduce freight costs.

2. Analysis a. Quality Initiative Analysis: Refer to Figure A below which shows the difference in average freight cost per kg for NSW and WA’s large and medium country stores for week 1 to 13 (before the implementation of quality initiative) and week 14 to 26. There was a decrease in the average freight cost for both states, with NSW reducing by 3% and WA reducing by 6% with reference to Table Z. NSW produced an average of -14% change and WA produced -31% change in pallet reduction as supported by Figure B. As shown in Figure C, there is a downward trend for WA’s average freight costs for the next 3 months and NSW’s average freight costs have a very minimal and slow downward trend, almost as close as having none. Discussion: This result could be an indication that the quality initiative introduced to Interior’s large and medium country stores from week 14 onwards was effective and as a result, the average freight cost had been reduced. It might be the result of the one-off $1000 bonus motivating the teams to reduce freight costs. WA team was able to reduce a higher sum of cost and has a higher percentage change for freight cost. Furthermore, by using predictive analysis, it shows that the average freight costs for WA has a downward trend, hence cost will continue to decrease.

2

Gwendolyn Ng Shu Yee (S3834172)

2 Interiors BusInteriors Business Report

Figure A Average of Cost per Kg

Column Labels

State and No. of Weeks

L

NSW (Week 1 - 13)

$

WA (Week 1 - 13)

M

Grand Average

0.8550

$ 0.9129

$

0.8851

$

1.3922

$ 1.4495

$

1.4249

Grand Average

$

0.9625

$ 1.0392

$

1.0032

Average of Cost per Kg

Column Labels

State and No. of Weeks

L

M

Grand Average

NSW (week 14 - 26)

$

0.8403

$ 0.8749

$

0.8583

WA (Week 14 - 26)

$

1.3278

$ 1.3500

$

1.3405

Grand Average

$

0.9378

$ 0.9867

$

0.9638

Table Z

3

Gwendolyn Ng Shu Yee (S3834172)

2 Interiors BusInteriors Business Report

Figure B

Figure C

4

Gwendolyn Ng Shu Yee (S3834172)

2 Interiors BusInteriors Business Report

b. Contract Review Analysis: With reference to Figure D, the total costs for each state when engaging the two different transport providers are as shown. The sum of total freight costs for WA, VIC, TAS, SA and NT when engaging SunnyDel costs lesser than NatFreight. Whereas for QLD and NSW, NatFreight cost lesser. As shown in Table Y, the total sum of SunnyDel’s freight costs is higher than NatFreight’s total cost by $4285. Referring to Table X and Figure E, NSW stores contributes to the largest amount of pallet baskets, costing $655,99. However, the store with the least amount of pallet baskets – NT, has a sum of $24,400 for total freight cost. Discussion: Should Interiors switches to SunnyDel as the transport provider, a higher cost is incurred. As seen in Figure D and Table Y, the total sum of freight costs for 5 out of 7 states while using NatFreight is higher than SunnyDel (except for QLD and NSW). Furthermore, with reference to Appendix 6, SunnyDel costs are cheaper for City Express, VIC, SA, WA and NT. Additionally, they do not charge extra metro surcharge. However, SunnyDel’s NSW and QLD costs are more expensive than NatFreight. As shown in Table X, NSW, VIC and QLD are the top 3 states that requires large amount of pallet baskets, hence contributing to the higher cost of using SunnyDel than NatFreight by $4285.

Figure D

5

Gwendolyn Ng Shu Yee (S3834172)

2 Interiors BusInteriors Business Report

Table Y

Table X

Figure E

6

Gwendolyn Ng Shu Yee (S3834172)

2 Interiors BusInteriors Business Report

c. Volume Utilisation Analysis: As shown in Figure F, large stores required the most amount of pallet baskets and small stores required the least amount of pallet baskets across the 7 states. However, the average volume utilisation in small stores (56%) is higher than large stores (48%) by 12% as shown in Figure H and Table W. By referring to Figure H, small stores have a majority of their pallets with 51-75% volume utilisation, medium stores have a majority of their pallets with 26-50% and large stores have more than half of their pallets with 26-50%. Discussion: As shown in Figure G, large stores have the lowest average volume utilisation. Furthermore, Figure H also shows that more than half of large stores total amount of pallets are greatly underutilised at a volume of only 26-50%, leaving at least half of the pallet’s capacity unoccupied. As the majority of the pallets are utilised with a volume of less than 5175%, Interiors are paying extra freight costs for delivering empty spaces in the pallets.

Figure F

7

Gwendolyn Ng Shu Yee (S3834172)

2 Interiors BusInteriors Business Report

Figure G

Table W

Figure H 8

Gwendolyn Ng Shu Yee (S3834172)

2 Interiors BusInteriors Business Report

3. Conclusion With reference to the overall results and analysis, it is concluded that: 1. WA should be awarded with the $1000 incentive-based on cost and predict analysis. It is clearly shown that WA had managed to reduce pallets with a reduced rate of 5.84% and saving total freight cost by $4500. Furthermore, with a downward trend for cost savings for the following 3 months, WA’s team performed better than NSW.

2. If Interiors were to switch to SunnyDel as the transport provider, the sum of freight cost will increase. As most of the pallets are required by NSW, VIC and QLD, it would be cost-saving if SunnyDel’s base price for the 3 states were cheaper than NatFreight.

3. Large stores required the most amount of pallet baskets, but the volume utilization is only 26-75%. Furthermore, almost all medium and large stores’ pallet baskets have not been fully utilized. As such, Interiors have been paying an extra cost for the unutilized spaces in the pallets.

4. Recommendation Based on the above results and analysis, Interiors should consider the following to improve its freight costs:

1) Fully implement Quality saving initiative across all states because it is proven that there is a reduction in the number of pallets and costs. However, with a low reduction rate of costs by 1.64% and 4.52% from NSW and WA respectively, Interiors should make it a requirement for teams to meet a targeted fixed saving in order to receive the bonus. It will motivate the teams to increase the reduction rate of pallet and the total costs.

2) Having NatFreight to continue being the transport provider even though SunnyDels’s service levels are on par and having cheaper base price for most of the states and furthermore not charging any metro surcharge. As SunnyDels are charging higher for QLD and NSW, where both states comprise almost half of the total sum of pallets across 7 states, it would be cheaper to continue with NatFreight.

9

Gwendolyn Ng Shu Yee (S3834172)

2 Interiors BusInteriors Business Report 3) With a total of 2832 pallets with a volume utilization of 25-50% and 2492 pallets with 51-75%, an underlying reason for the high freight cost is due to not fully utilizing the pallet baskets. Interiors can change to another common pallet size such as 1200mm by 800mm instead of 1165mm by 1165mm which reduces the pallet’s volume. 4) Otherwise, introduce a requirement of minimum number of order so as to maximise the capacity of the pallet which reduces the amount of pallets required and reducing total costs.

10

Gwendolyn Ng Shu Yee (S3834172)

2 Interiors BusInteriors Business Report

5. Appendix

Appendix 1: Screenshot of pallet reduction using SUMIFS and AVERAGE function

Appendix 2: Screenshot of average freight cost per kg using Pivot Data (Table Z)

11

Gwendolyn Ng Shu Yee (S3834172)

2 Interiors BusInteriors Business Report

Appendix 3: Screenshot of NSW and WA country stores Average Freight cost using AVERAGEIFS 12

Gwendolyn Ng Shu Yee (S3834172)

2 Interiors BusInteriors Business Report

13

Gwendolyn Ng Shu Yee (S3834172)

2 Interiors BusInteriors Business Report Appendix 4: Screenshot of SUMIF and AVERAGEIF function for sum and average freight cost and using of Pivot Table to get overall averages

Appendix 5: Screenshot of Price Matrix for NatFreight and SunnyDels

14

Gwendolyn Ng Shu Yee (S3834172)

2 Interiors BusInteriors Business Report Appendix 6: Screenshot of pallets, volume utilisation, average number of KG, average sum of freight costs for NSW and WA small, medium and large country stores using SUMIFS and AVERAGEIFS function

Appendix 7: Screenshot of volume utilisation count using COUNTIFS and SUM function

15

Gwendolyn Ng Shu Yee (S3834172)...