ACG $3341$ Module $1$ Data Analytics

Based on Material in Chapter $2$:

The purpose of this assignment is to apply Excel skills in producing a set of schedules from a raw set of data accounts. You$$ll practice:

$$ Cell referencing $($linking$)$

$$ Cell naming

$$ Use of multiple sheets

$$Vlookup$$ function

$$Sum$$ function

$$ Cell formatting

You may find videos on these topics on the WileyPlus course page as well as in YouTube.

This is a task will emulate an audit setting where you are given a download from a client and you have to put together a set of schedules for the audit workpapers.

The Excel File includes six $(6)$ sheets:

$$ Data: raw data table to be used to generate the schedules $($you shouldn$$t edit anything in this$).$

$$ Task $1$: Generate preliminary calculations

$$ Task $2$: Generate a Cost of Goods Manufactured Schedule

$$ Task $3$: Generate a Cost of Goods Sold Schedule

$$ Task $4$: Generate an Income Statement

$$ Task $5$: Recreate T$-$Accounts.

Requirements:

$$ Task $1$: Generate Summary $/$Support schedules $(5$ Points$)$

o Use direct references $($linking$)$ to cells in the Data worksheet to create summary schedules for:

$$ Direct Materials Used in Production

$$ Manufacturing Overhead Costs

$$ Selling General and Administrative expenses

o Use cell name to label the total of each schedule $($you will use these in the next requirements$).$

o Make sure you name each schedule as if they were exhibits and format numbers using commas and dollar signs where necessary.

$$ Task $2$: Complete the Cost of Goods Manufactured Schedule $(5$ points$)$

o Use the "vlookup" formula to obtain the values for the direct manufacturing labor, beginning and ending WIP balances from the Data Worksheet

o Use the names$/$labels you created in Task $1$ to link the Direct Materials Used in Production and Manufactured Overhead Costs Incurred.

o Use formulas to generate subtotals

o Use cell formatting to format the schedule in a way that the final schedule resembles the Schedule of COGM in Illustration $2\mathrm{.}19$ in the textbook.

o Use cell name to label the Total Cost of Goods Manufacturing amount as $"$CGM$",$ so you can link$/$refer to it in the next task$($s$).$

$$ Task $3$: Complete the Cost of Goods Sold Schedule $(5$ points$)$

o Use the $$vlookup$$ formula to obtain the values for the beginning and ending Finished Goods Inventory from the Data Worksheet

o Use the names$/$labels you created in task $2$ to link the Cost of Goods Manufactured to the current schedule.

o Use formulas to generate the subtotals

o Use cell formatting to format the schedule in a way that the final schedule resembles the Schedule of COGS in Illustration $2\mathrm{.}20$ in the textbook.

o Use cell name to label the Total Cost of Goods Sold amount as $"$CGS$",$ so you can link$/$refer to it in the next task$($s$).$

o Center the schedule titles across columns.

$$ Task $4$: Generate an Income Statement $(5$ points$)$

o Generate an Income Statement for TWD for the year ending $12/31/2025$ that resembles to the one in Illustration $2\mathrm{.}21$ in the textbook. Make sure you use the appropriate cell formatting.

o Use the $$vlookup$$ function to link the Sales$/$Revenue information from the Data worksheet.

o Use the cell names$/$labels you created in the previous tasks to link the information you need to complete the Income Statement.

o Center the Header$/$Title to the Schedule across columns.

$$ Task $5$: Recreate T$-$Accounts. $(5$ points$)$

o Use the information you have generated in Tasks $1-4$ to construct the T$-$Accounts for: Direct Materials Control, WIP Control, and FGI Control. Refer the cell names$/$labels you created where possible.

o Use of cell links from other Task or the Data Worksheet to complete each T$-$Account; label appropriately.

o Use formulas to generate the account balances.

o Center the account title across the T$-$Account

o Use cell formatting to make the accounts resemble those in Illustration $2\mathrm{.}18$ in the textbook.