williamson plastics

Williamson Plastics is a national company that specializes in the manufacturing and sale of polyvinyl chloride (PVC) pipe fittings for use in residential and commercial plumbing. The company has two divisions, an Eastern and Western division, and they manufacture 3 specific types of pipe fittings: A U-Joint, a V-Joint, and a T-joint. Both company divisions produce these products. You have been asked to develop a master spreadsheet which analyzes annual company performance over the 2000-2019 period, in terms of (1) overall corporate performance, (2) performance by division, and (3) performance by product type. The attached spreadsheet (Homework 1 Spreadsheet.xlsx) provides a partially completed skeleton of this master worksheet, with some initial base data on unit sales, price per unit, and costs per unit, for each product line in bothdivisions, for the first year of the analysis period.

The following represents critical information we know about Williamson’s performance in subsequent years, with regards to sales volume, unit pricing, and unit costs:

Eastern Division:

U-joint sales increase by 6 percent per year through 2014, 4 percent per year afterwards

V-joint sales increase by 3 percent per year through 2014, 5 percent per year afterwards

T-joint sales increase by 10 percent per year through 2014, then decline by 2 percent per year afterwards

U-joint prices increase by 2 percent per year through 2014, 3 percent per year afterwards

V-joint prices increase by 5 percent per year through 2014, then decline by 1 percent per year afterwards

T-joint prices increase by 1 percent per year through 2014, 3 percent per year afterwards

U-joint unit costs increase by 3 percent per year through 2014, 2 percent per year afterwards

V-joint unit costs increase by 4 percent per year through 2014, then decline by 2 percent per year afterwards

T-joint unit costs increase by 2 percent per year through 2014, 1 percent per year afterwards

Western Division:

U-joint sales increase by 4 percent per year through 2014, 2 percent per year afterwards

V-joint sales increase by 1 percent per year through 2014, 6 percent per year afterwards

T-joint sales increase by 5 percent per year through 2014, then decline by 1 percent per year afterwards

U-joint prices increase by 3 percent per year through 2014, 2 percent per year afterwards

V-joint prices increase by 4 percent per year through 2014, then decline by 2 percent per year afterwards

T-joint prices increase by 5 percent per year through 2014, 1 percent per year afterwards

U-joint unit costs increase by 2 percent per year through 2014, 4 percent per year afterwards

V-joint unit costs increase by 6 percent per year through 2014, then decline by 3 percent per year afterwards

T-joint unit costs increase by 4 percent per year through 2014, 2 percent per year afterwards

The company has no inventory, so everything it produces, it sells. (i.e. “Just in Time” inventory)

To complete this analytical assignment, you must do the following:

1. Finish the spreadsheet

A. Use the data above to complete the revenue, cost, and profit estimates for the Eastern and Western divisions

B. Create a “company totals” section of the spreadsheet using the same variables/metrics used for each division

2. Provide graphics that show key company performance indicators

A. Create a simple column chart, that shows total company profit for the period 2010-2019

B. Create a stacked column chart, that shows total company profit by division for the period 2010-2019

C. Create a clustered column chart, that compares total revenues and total costs (expenditures) for the Eastern division, for the period 2010-2019 D. Create a line chart, that compares total revenues and total costs (expenditures) for the Western division, for the period 2010-2019

E. Create a single pie chart, that illustrates profit by product type and division for year 2018. From this single pie chart, you should be able to see what the most profitable product was for the company, and what division it came from.

IMPORTANT CONSIDERATIONS IN THIS EXERCISE

:

ï‚·

Substance and style matter. Your calculations must be present in your spreadsheet formulas and calculation

functions, but also, formatting matters. Dollar values and units need to be expressed as such. Don’t use decimals

where you don’t need them, use them where you do. Column widths and row heights need to be consistent and

reasonable throughout the spreadsheet. Consider shading, bolding, and/or bordering of areas where totals exist.

Also, use underlining where you think it would make sense to do so.

ï‚·

For your charts: It may be necessary to copy and “paste special” relevant values from your master spreadsheet in

order to create new data ranges and headers that are easy to use as the base data for the charts. Also, all of your

charts should be formatted with a chart title, axis titles, data labels, legend (if appropriate), and reasonable data

ranges on your axes (particularly your Y-axis on line and column charts). Use readable colors for your values, and in the case of line charts, make sure your line thickness is enough so that it is readable.