BSAD $321$: Assignment #$2$: Ivy Corporation

Students need to prepare the following excel analysis of Ivy Corporation. You are to upload your completed excel to Moodle by Friday, November $10$th at $5$:$00$ pm$.$ Please name your file Ivy Corp$\_$First name Last name. This is worth $5\%$ of your overall grade in BSAD $321.$

Background:

Ivy Corporation manufactures a variety of indoor and outdoor furniture. It has several manufacturing facilities located in six different states or provinces: Ontario and British Columbia in Canada, and Kansas, North Carolina, Ohio, and Texas in the United States. Each manufacturing facility makes its own unique products. The company uses just$-$in$-$time inventory management, so it has no significant inventory. Ivy has five sales representatives who can sell products from any of its manufacturing facilities.

All transactions in Ivy$$s job cost system for the most recent month have been exported to an Excel file. In this transaction file, revenue amounts are shown as positive numbers while all costs shown as negative numbers. The transactions file contains the following fields:

$$ Job #

$$ Customer #

$$ Customer name

$$ Sales rep

$$ Facility

$$ Country

$$ General ledger account

$$ Amount

Job costs in Ivy$$s system are tracked using four different general ledger numbers: G$/$L #$3100$ Revenue; G$/$L #$4101$ Direct material; G$/$L #$4301$ Direct labor; and G$/$L #$4501$ Manufacturing overhead

Excel Requirements:

General instructions: Create pivot tables $($link$)$ in separate worksheets to calculate answers for each of the following requirements. Rename each of the pivot table worksheets $($link$)$ from the default names to $$Pivot table $1,$Pivot table $2,$ etc. Format dollar amounts in all pivot tables with the Accounting format, zero decimal places $($Windows $|$ Mac$).$

Using your knowledge and excel skills gained from the first data analytics assignment, prepare the following analysis.

$1.$ Pivot table #$1$

$$ What is the total revenue?

$$ What is the total direct material used?

$$ What is the total direct labor used?

$$ What is the total manufacturing overhead applied?

$$ What is the total gross profit?

$2.$ Pivot table #$2$

$$ What is the total revenue, total direct material, total direct labor, total manufacturing overhead, and total gross profit

$$ for each country?

$$ for each state or province?

$$ Which manufacturing facility has the most gross profit? Which has the least?

$$ Which country$$s manufacturing facilities generate the most gross profit?

$3.$ Pivot table #$3$

$$ What is the revenue generated by each job?

$$ What are the totals of direct material, direct labor, and manufacturing overhead costs for each

job?

$$ What is the gross profit generated by each job?

$$ Which job generated the most gross profit? Which generated the least gross profit? Hint: Sort

the pivot table by gross profit $($link$).$

$4.$ Pivot table #$4$

$$ What is the revenue generated by each sales rep?

$$ Which sales rep generates the most revenue? Which generates the least?

$$ How much of each sales rep$$s revenue is generated from facilities in Canada? From facilities

in the United States? Hint: Use a filter in the pivot table $($Windows $|$ Mac$).$

$$ How much of each sales rep$$s revenue is generated by each of the facilities within each

country? Hint: Use a filter in the pivot table $($Windows $|$ Mac$).$

$5.$ Pivot table # $5$

$$ Create a pivot table of your choosing. Tell me why you created that table or what question you can answer from it$.$

Interpretation of Excel Analysis

After completing the excel analysis, provide written responses to the following questions. Be sure to

answer in the appropriate tab$/$worksheet in a format that is easy to read.

$1.$ Based on analysis for pivot table $2$:

a$.$ What is the total gross profit for each country?

b$.$ Which manufacturing facility has the most gross profit? Which has the least?

$2.$ Based on your analysis for pivot table $3$:

a$.$ What is the revenue generated by Job $4095?$

b$.$ What is the total direct materials for all jobs this period?

c$.$ What is the direct labor cost for Job $4518?$

d$.$ Which generated the least gross profit?

$3.$ Based on your analysis for pivot table $4$:

a$.$ What is the total revenue generated by Franklin Smithers?

b$.$ Which sales rep generates the most revenue? Which generates the least?

c$.$ What is the total revenue generated by Hallie Vang in BC$?$

$4.$ Tell me about the table you created $($Pivot Table $5).$ What questions can you answer from your analysis? Why would this analysis be useful for the Ivy Corp to know?

Check figures

Here are some check figures for each of the pivot tables.

$$ Pivot table $1$: G$/$L#$4501-$ $ $(429,680)$

$$ Pivot table $2$: G$/$L #$4501$ Mfg overhead should be $ $(74,461)$ for the Ontario facility

$$ Pivot table $3$: G$/$L#$3100$ revenue for Job $3757$ should be $$11,215$

$$ Pivot table $4$: Sophia Payne$$s Ontario revenues should equal $ $151,580$