Project $2$: Job cost analysis for Bexia Fortunia, Inc $$ Excel

Download Excel Data Download Download Excel Data

Overview

Bexia Fortunia, Inc. manufactures a variety of custom cabinets. It has several manufacturing facilities located in six different states or provinces: Quebec and Ontario in Canada, and Oregon, Missouri, Georgia, and New Mexico 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. Bexia Fortunia has five sales representatives who can sell products from any of its manufacturing facilities.

All transactions in Bexia Fortunia$$s job cost system for the most recent month have been exported to an Excel file that can be downloaded on Canvas. 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 Bexia Fortunia'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.

For this case, you will need to know how to calculate the cost of a job using job order costingLinks to an external site. and how to calculate the gross profit on a jobLinks to an external site..

Instructions

General instructions: Create pivot tablesLinks to an external site. in separate worksheets to calculate answers for each of the following requirements. Rename each of the pivot table worksheetsLinks to an external site. 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 $($WindowsLinks to an external site. $|$ MacLinks to an external site.$).$

Pivot table #$1($Answering questions about General Ledger Account Totals$)$

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?

Pivot table #$2($Set up to show the total revenue, total direct material, total direct labor, total manufacturing overhead, and total gross profit for each country and each state$/$province$.)$

Which manufacturing facility $($state$/$province$)$ has the most gross profit? Which has the least?

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

Pivot table #$3($GL Account totals by for each job$)$

What is the revenue generated by each job? $($Just show in table$)$

What are the totals of direct material, direct labor, and manufacturing overhead costs for each job? $($Just show in table$)$

What is the gross profit generated by each job? $($Just show in table$)$

Which job generated the most gross profit? Which generated the least gross profit? Hint: Sort the pivot table by gross profit $($linkLinks to an external site.$).$ Give the answer.

Pivot table #$4($Showing revenue by sales rep$)$

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 $($WindowsLinks to an external site.$|$ MacLinks to an external site.$).$

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 $($WindowsLinks to an external site.$|$ MacLinks to an external site.$).$

Pivot table #$5($Showing GL Account Totals by customer name$)$

Which customer was the most profitable?

Which customer was the least profitable?

How much revenue was generated from The Cat Corporation?