##### ACCG200 Excel Assessment S2 2018 Instructions, homework help

ACCG200 Excel Assessment S2 2018 Instructions

BikePro Ltd want you to perform a Cost Volume Profit Analysis for their High-End Racing Bike product, the X-800. You

will need to calculate the labour costs, material costs, fixed costs and variable costs and then work out the break-even

volume. You have also been asked to show a forecast for different sales volumes and produce a break-even analysis line

chart.

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.

Detailed instructions are provided on the next pages. Please follow each of the instructions precisely and in sequence.

All the grey cells require you to insert some form of calculation, i.e. start with an “=”, just typing in the answer will result

in 0 marks. Do not change the structure of the workbook in anyway or put any workings anywhere other than directed.

Only use rounding functions where specifically required. The use of named ranges and tables is encouraged, and in

certain cases required.

Because we are developing a model that we may wish to use with other products or different values it is very important

that all calculations will return a correct answer regardless of the values in the spreadsheet and must still work correctly

when the lists of data (Labour Costs, BOM & Inventory) are sorted in a different order.

Double check your answers as you go as one wrong result may affect lots of other results. Use the check boxes in the

Done column to keep track of tasks completed.

Remember to save often. When you are ready to submit, upload the completed Excel File to iLearn (Excel Submission).

You do not need to rename it as your student number will be automatically attached by iLearn, but please ensure that it

is an Excel file you submit (Numbers files will not be accepted) and that there are no “non-English” characters in the file

name as these files cannot be opened in an English version of Excel.

Question Instruction Marks Done

Section A The following are to be completed on the Labour Costs Worksheet 10

A1 All staff members receive an annual bonus of $200 plus an additional percentage of

their Annual Income. Each staff member has been allocated their own percentage

bonus rate (column C). In D6:D16 calculate each staff members Bonus.

2 ☐

A2 F3 contains the % Superannuation given to all staff which is calculated on their

annual salary only (bonus not included). In E6 calculate the annual Super amount

paid to each staff member using the value in F3. Copy the formula down to E16 (to

get full marks, a named range or an absolute cell reference must be used).

2 ☐

A3 In F6:F16 calculate the total package for each staff member (Annual Salary, Bonus

and Super). Adjust the spreadsheet so that the “#####” problem is addressed.

2 ☐

A4 In F18 calculate the Total Labour Costs. 1 ☐

A5 Insert a row below Row 18. In E19 type “Average” and in F19 calculate the average

Total Package.

2 ☐

A6 In F20 use a formula to calculate the highest Total Package paid to an individual staff

member (i.e. the biggest value in F6:F16)

1 ☐

Section B The following are to be completed on the BoM and Inventory Worksheets 13

B1 Go to the Inventory Sheet. This sheet contains a list of all the items the company

keep in stock to build the bikes. Each part is identified by a unique part code.

Convert the Range A3:J44 to a Table. Name the table Inventory.

1 ☐

B2 Go to the Bill of Materials (BoM) Worksheet. This sheet contains a list of which parts

(and how many) are needed to make two different types of bicycles, the ZF300 (no

longer in production) and the X-800, the model currently produced. The table is

incomplete as it doesn’t have the rest of the information about each part. Use

appropriate formulas to lookup the corresponding Category, Product Description,

Supplier and Unit Price for each of the Part Codes from the Inventory table. (Note:

Both the BoM and the Inventory are frequently re-sorted, so the formulas must

work regardless of sort order.)

8 ☐

B3 Calculate the Total Price for each Part Code in column H. 0.5 ☐

B4 Still in the BoM sheet, click into K3. Note the dropdown that allows you to select a

product. In K4 create a formula to calculate the total price of parts of category

Groupset for that product. Copy the formula down to K8 to get a breakdown of

Product Costs by Category.

3 ☐

B5 In K9 calculate the total cost to make one unit of the selected product. 0.5 ☐

Section C The following are to be completed in the CVP Analysis Worksheet 17

C1 In B14 calculate the Total Fixed Costs. 1 ☐

C2 In E5, using the information in the BoM, calculate the Direct Material Cost per unit

specified in B3. (Note Do NOT use the value in BoM!K9 as this can be for another

product, and ensure the calculation works regardless of sort order in the BoM.)

2 ☐

C3 In E6 use the information in the Labour Costs sheet to calculate the Direct Labour

Cost per unit. Note if the values in the changes this figure should update.

1 ☐

C4 In E9 calculate the Variable Manufacturing Cost per unit. 1 ☐

C5 In E14 calculate the Variable Selling & Admin Cost per unit (as a %). 1 ☐

C6 In H4 calculate the Contribution Margin per Unit (this is the Unit Sales Price less the

Variable Manufacturing Cost per unit and the Variable Selling & Admin Cost per

unit).

2 ☐

Question Instruction Marks Done

C7 In H5 calculate your break-even volume (in units) rounded up to 0 decimal places.

(This answer is very important so double check it, multiply it by the variable cost

figures and add the fixed costs back in and unrounded it should give you 0).

2 ☐

C8 In H6 multiply the Break-Even Volume in Units by the Unit Sales Price to calculate

the Break-Even Sales Value.

1 ☐

C9 In H9 to H14 calculate the Break-Even Variable Costs using the calculated figures in

H5 or H6 (where appropriate). Note, each answer is worth ½ mark which will only be

awarded if the answer is correct.

3 ☐

C10 In H15 calculate the Total Break-Even Variable Costs. (Answer must be correct to get

mark.)

1 ☐

C11 In H17 calculate the Break-Even Contribution Margin. 1 ☐

C12 In H18 calculate the Profit Before Tax. (If we hadn’t rounded this should be 0). 1 ☐

Section D The following are to be completed in the Forecast Worksheet 20

D1 In C4 create a calculation to add the Increment (the amount our forecast is

increasing by) in B3 to the volume in B4. (Note we want the ability to change the

increment so do NOT just add 100, use a relative reference or a range name). Drag

the formula across to G4

1 ☐

D2 In row 5 calculate the total sales revenue for each of the different Sales Volumes. 1 ☐

D3 Complete the variable costs table for the different sales volumes (B8:G14). 4 ☐

D4 In row 16 calculate the Contribution Margin for each sales volume. 1 ☐

D5 In row 17 calculate Contribution Margin less Fixed Costs for each sales volume. 1 ☐

D6 In B20:G22 complete the income tax for each sales volume. Formulas must take into

consideration that in some cases there may have been a loss in which case the

income tax should be 0. (Do NOT just type the 0 as the model should work with

different inputs.)

5 ☐

D7 In row 24 calculate the profit after factoring in the income tax. 1 ☐

D8 In row 26 work out the profit for each sales volume as a percentage of sales. (Format

as a percentage with 2 decimal places, do not apply rounding formulas.)

1 ☐

D9 Complete the Break-Even Analysis Line Chart Data in B29 to G32 for each of the Unit

volumes. (Note Total Costs = Variable Costs + Fixed Costs+ Income Tax)

2 ☐

D10 Using the data generated in question D9, create a Line Chart that plots the Total

Fixed Costs, Total Costs & Total Sales. Add the Volume Units as Horizontal Axis

Labels and add the Chart Title “Break-Even Analysis”. (N.B. Only include one chart

ensure it is on the Forecast Sheet and that it is a 2D Line Chart with no line markers

(not a Scatter chart with smooth lines).

3 ☐

Section E The following are to be completed in the Inventory Worksheet 5

E1 In H4 create a formula to convert the weight in grams to a weight in pounds. The

calculation must round the result to 2 decimal places. (Ensure the formula is copied

down for the whole table.)

2 ☐

E2 In J4 create a calculation to work out the reorder quantity. If the stock level is below

the minimum level shown in M3, then reorder the appropriate amount as shown in

the Re-Order Quantities table (L5:M9), otherwise return 0. (Ensure the formula is

copied down for the whole table. Note you should have one consistent calculation

for the column and the calculations should work regardless of values or sort order.)

3 ☐

TOTAL MARKS 65

College Paper Writing Service