Futura Robotics Manufacture - Journal Accounts - Stats Worksheet - Inventory Worksheet - Accounting and Finance Assessment Answer

Futura Robotics Manufacture Accounting and Finance Assessment Answer

Assessment Task:

Scenario
Futura robotics manufacture custom made robotic components. Each job is unique and costed individually. You are required to create a spreadsheet that records a list of completed jobs and allows the company to cost up new jobs. Start by downloading the assessment workbook from iLearn and copying/moving it to an appropriate folder.
When you open the workbook it is very important that you Enable Macros/Content. You will then be asked to enter your Student Number (you will not be able to edit it afterwards, so type it in carefully) and then enter your Student Name.

Section A The following are to be completed in the Completed Jobs sheet:

A1 This sheet contains a list of completed jobs for the year. Column B containing the Manger information has been hidden, unhide column B.

A2 In E4 use a formula to calculate the number of days between date started and date completed. For example, if the job started on the 4th and finished on the 24th of
January, the days taken would be 20. Copy the formula down to E24.

A3 Change the formatting of Days Taken values to be General (number with no decimals).

A4 In I4:I24, use the Man Hours provided along with the information on overheads in the Information sheet, to calculate the manufacturing overhead for each job

A5 In J4:J24 calculate the total costs for each job.

A6 Widen column J so that all the values are clearly visible.

A7 In L4:L24 calculate the profit margin ($) for each job.

A8 In M4:M24 calculate the Gross Profit Margin for each job. (Note: in some cases there may be a loss which will give a negative answer.)

A9 Format the Gross Profit Margin figures to show as a percentage. 1 ?

A10 Convert the data in A3:M24 to a Table. Name the table Jobs. (NB name the table, not the range).

Section B The following are to be completed in the Stats Worksheet 23
Read through the questions and determine if adding named ranges will help you, they are not required but may make your life easier.

B1 In B3 calculate the total number of days spent on the completed jobs.

B2 In B4, use the values in the Completed Jobs sheet to calculate the total revenue.

B5 In B5, use the values in the Completed Jobs sheet to calculate the Average Material Cost rounded to the nearest 10. (You are required to use a rounding function here.)

B4 In B6, use the values in the Completed Jobs sheet to calculate the Highest Labour Cost.

B5 In B7, use the values in the Completed Jobs sheet to calculate the Lowest Labour Cost.

B6 In B8, use the values in the Completed Jobs sheet to calculate the Number of Completed Jobs.

B7 Challenge: In B9, use the values in the Completed Jobs sheet to calculate which job was the highest revenue job. Note: 1 Mark will be given for just identifying the
highest revenue amount, but to get full marks we need the associated Job No. (Hint: Investigate the INDEX function)

B8 In B13, use the values in the Completed Jobs sheet to calculate the total number of jobs managed by Amir. Copy the formula down for Toni and Martin.

B9 In C13, use the values in the Completed Jobs sheet to calculate the number of jobs managed by Amir that made a loss (negative margin). Copy the formula down for
Toni and Martin.

B10 In D13, use the values in the Completed Jobs sheet to calculate the average revenue of jobs managed by Amir. Copy the formula down for Toni and Martin.

B11 In E13, use the values in the Completed Jobs sheet to calculate the total $ profit for jobs managed by Amir. Copy the formula down for Toni and Martin.

B9 In the Stats sheet, create a 2-D Pie Chart showing the Total $ profit for each manager. Ensure there is only one chart and that it has the title Profit by Manager.
Show data labels as percentages on the pie segments and show the legend at the bottom of the chart. (Please follow instructions exactly.)

Section C The following are to be completed in the Inventory Worksheet 20

C1 Go to the Inventory Sheet. This sheet contains a list of all the items the company keep in stock to build the robotics. Each part is identified by a unique code. Convert
the Range A3:I53 to a Table. Name the table Inventory.

C2 Name the following ranges:
A4:A53 Code
E4:E53 Cost_Price (and any other ranges you feel may prove useful)

C3 In F3:F53 calculate the retail price (paid by customer) using the Cost Price and the % Markup in L3. (Note the markup may be changed and your calculation should still produce the correct answer.)

C4 L4 indicates the maximum stock level. In G3:G53 create a formula to check if the quantity in stock is over the maximum stock in L4 (this level can be adjusted). If it is
over the stock level put Yes in the cell, otherwise leave it empty (do not put No or a space). Formula should copy down, check it is working correctly.

C5 L5 indicates the minimum stock level while column D indicates if an item is on back order (already been reordered). If a stock item is below the minimum and not on
Backorder (is blank) we need to reorder it. In H3:53 create a formula to check if the item needs to be re-ordered. If it does put Yes in the cell, otherwise leave it empty
(do not put No or a space). Formula should copy down, check it is working correctly.

C6 Reorder quantities are based on price as shown in the Reorder Quantities data (K11:L16), e.g. currently for items that cost from $100 up to (but not including) $300
we re-order 35. Create a calculation in I4 that will check if we need to reorder and if so return the correct reorder amount otherwise return 0. Ensure the formula is
copied down for the whole table. Note stock levels and reorder quantities will change, you should have one consistent calculation for the column and the
calculations should work regardless of values or sort order of the inventory data.

C7 In L6 calculate how many item codes require reordering.

C8 In L7 calculate how many item codes are over stocked.
Use Excel’s built in help to investigate the SUMPRODUCT function. In L8 calculate the total cost of all inventory items currently in stock.

Section D The following are to be completed in the JOB_4971 Worksheet 19

D1 This sheet shows the Job Card for the most recent job, but it is missing several calculations. The first cost section is for direct materials. Items used for the job are
listed by item code. In F13:F18 use calculations to lookup the correct description for each item from the Inventory table.

D2 In I13:I18 use calculations to lookup and calculate the total cost for each item. (Cost to the company not the client).

D3 In I21 calculate the total costs for direct materials.

D4 In H25:H31 use a formula to lookup the correct hourly rate for each department from the Information sheet.

D6 In I25:I31 calculate the labour costs for each task. 1 ?

D7 In I33 calculate the average hourly rate. (What it cost on average for one hour of labour on this job.)

D8 In I34 calculate the total labour costs. 1 ?

D9 In H37 use a calculation to determine the total direct labour hours for the department shown in C37. (Note the formula must work for different scenarios. you
can’t assume that C26 and C29 will always be Assembly). Copy the formula down to H39.

D10 In I37:I39 use the data in the Information worksheet to determine the manufacturing overheads for each department.

D11 In I42 calculate the total manufacturing overheads.

D12 In I45 calculate the total costs.

D13 In I46 reference (pull through) the Invoice Amount from the Information Sheet.

D14 In I47 calculate the Gross Profit Margin.

Section E The following are to be completed in the Journals Worksheet 6 E1 Complete the journal entries for Job 4971.

This Accounting and Finance Assessment has been solved by our Accounting and Finance experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.