ACCG 2000 Excel Assessment S1 2021 Instructions Scenario You work for a company that produces two different types of Workbench: the WB500 which is the simpler,  cheaper option; and the WB660 which has more features but is a bit more expensive

Subject:MS ExcelPrice:20.01 Bought11

Share With

ACCG 2000 Excel Assessment S1 2021 Instructions

Scenario

You work for a company that produces two different types of Workbench: the WB500 which is the simpler,  cheaper option; and the WB660 which has more features but is a bit more expensive. You are required to create a spreadsheet that can conduct a weighted average Cost Volume Profit Analysis for the two products.  You can assume that the sales mix, sales price, fixed costs, and variable costs per unit are constant. You will need to calculate the fixed costs, labour costs and material costs and then work out the weighted average break-even point.  You have also been asked to show a forecast for different unit volumes and different sales mixes and produce a break-even analysis line chart for the selected volumes and sales mix.

 

Start by downloading the assessment workbook from iLearn and copying/moving it to an appropriate folder. You will need to work on a desktop version of Excel, this can be Mac or PC, but you cannot use Excel online, Google Sheets or Numbers.  It is recommended that you use 2016 or later. When you open the file it is very important that you Enable Macros and 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. Please note the first 3 worksheets are locked and you will only be able to change the cells specified. 

 

General Guidelines

• All the light grey cells require you to insert a calculation or a cell reference, i.e. start with an “=”, just typing in an answer will result in 0 marks. 
• Do not change the structure of the workbook in anyway or put any workings anywhere other than directed.  (Do not add columns/rows, pivots, additional calculations.)
• Only use rounding functions where specifically requested (although you can format to show/hide additional decimals).
• The use of named ranges is encouraged, and in certain cases required but unless stipulated calculations do not need to use named ranges. 
• Please do NOT create tables other than where directed.
• Because you are developing a model where values may be subject to change it is very important that all calculations return a correct answer regardless of the values in the spreadsheet and must still work correctly when the tables of data (BOM & Inventory) are sorted in a different order.
• Double check your answers as you go as one wrong result may affect other results. 
• To get full marks for a question both the formula AND the answer must be correct.  Partial marks may be awarded for correct or partially correct formulas, but this will be at the marker’s discretion.
• Use the check boxes in the Done column to keep track of tasks completed.
• Remember to save often.  
• When you are ready to submit, save, close and 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 the correct Excel file, (Numbers files will not be accepted) and if it has a ~ in front and is only 1KB it is not the correct file.  Also please ensure that there are no “non-English” characters in the file name as these files cannot be opened in an English version of Excel.  

 

 

Detailed instructions are provided on the next pages. Please follow each of the instructions precisely.  Good luck ?

 

               

 

Section A	The following are to be completed in the Labour HR Data sheet:	8
A1-A8	This sheet contains a list of employees at the company followed by a series of multiple-choice questions.  Some of the questions are general, some refer to the data.  Each question has only one correct answer, please indicate the correct answer by changing the corresponding value in column F from FALSE to TRUE.  Only change one option for each question or it will be marked wrong.	8	?
Section B	The following are to be completed in the Fixed Costs sheet:	11
B1	This sheet contains details a breakdown of fixed costs by quarter.  In row 9 calculate the total costs for each quarter.	1	?
B2	In B11 add up the quarterly totals to calculate the total fixed costs for the year.	1	?
B3	In column F4 calculate the average quarterly Rent. Copy the formula down to F7.	1	?
B4	Widen column F so the values can be seen properly.	1	?
B5	In column G4 calculate what percentage of total costs was made up by Rent costs. Copy the formula down to G7.	2	?
B6	In B13 calculate the highest quarterly admin cost.	1	?
B7	In B14 calculate the average monthly rent. Apply a normal rounding calculation to round to the nearest hundred. (i.e. 150 will become 200, 149 will become 100).	2	?
B8	In B15 calculate the percentage by which the rent went up from Quarter 2 to Quarter 3.	1	?
B9	Change the number format in B15 to Percentage with one decimal place.	1	?
Section C	The following are to be completed in the BOM Worksheet	8
C1	Name the range A4:A43 Product	1	?
C2	A named range has been created called Total Price, but it does not refer to all the correct cells. Edit the named range Total Price to include all cells from F4:F43. Do not create another named range for this column, change the existing one, but feel free to create other named ranges that prove useful.	1	?
C3	Convert the data in A3:F43 to a table. Change the name of the table to BOM. (This is not a named range).	2	?
C4	The Bill of Materials itemises the quantities of each component required to make the two products. In column D use a formula to get the appropriate Part Description for each Part Code from the Inventory table.	2	?
C5	In column E use a formula to get the appropriate Unit Price for each Part Code from the Inventory table.	2	?
Section D	The following are to be completed in the CVP Analysis Worksheet	19
D1	B6 and C6 contain the number of units sold for the two different product types respectively. In B7 and C7 calculate the % sales mix (percentage of total) for each product type. You can assume for the CVP analysis that this will stay constant.	1	?
D2	In F4 pull through the total fixed costs from the Fixed Costs sheet (if the value in the fixed costs sheet changes, this should update.)	1	?
D3	In B11 and C11, use the information in the BOM to calculate the Direct material costs for one unit of each product.	2	?
D4	In B12 and C12 use the values in the Labour Costs sheet to calculate the cost of labour to produce one unit of each product.	1	?
D5	In B17 and C17 calculate the contribution margin per unit for each product (ensure you include vary with unit and vary with revenue costs).	2	?
D6	In F9 calculate the Weighted Average Contribution Margin using the sales mix percentages calculated previously.	1	?
D7	In F10 calculate the break-even units (the combined number of units from the two products that need to be sold to not make a loss). As we cannot sell part of a product,  apply an appropriate rounding function to get the answer to 0 decimal places.  (This answer is very important so double check it.)	2	?
D8	In B19 and C19 use the previous result and the sales mix to work out the break-even point in units per product.	2	?
D9	In B20 and C20 calculate the break even point in dollars for each product.	1	?
D10	In F13 to F16 calculate the Break-Even Variable Costs for the two products.	4	?
D11	In F19 calculate the Break-Even Contribution Margin (this is the contribution margin for the total number of units required to break even.)	1	?
D12	In F20 calculate the Break-Even Profit Before Tax. (Break even profit would normally be 0, but because we rounded up our break-even units, this should be slightly more than 0.)	1	?
Section E	The following are to be completed in the Profit Volume Forecast Worksheet	19
	In this sheet we want to be able to forecast what our profits will look like with different sales volumes and different sales mixes. Click on the drop down in B3 and change it to 50%, the value in B4 adjusts accordingly. Now change it back to 60%. Click in B5 and you will see you can also select the increment (the amount we go up in) we want to model, for example we may want to see profit for 500, 1000, 1500 units or we may want to see profit for 1000, 2000, 3000 units.
E1	Click in B7 and you will see you can select the minimum sales volume you want to model. In C7 create a formula to add the Increment in B5 to the volume in B7.  Use appropriate cell referencing so that the formula can be dragged across to G7 and the total will increase by the value shown in B5 (and change if the value in B5 changes).  Drag the formula across to G7.	1	?
E2	In row 8 calculate the total sales revenue for each of the different Sales Volumes using the sales mix options selected in B3 and B4 (do not use the sales mix from the CVP sheet.)	1	?
E3	Complete the variable costs table for the different sales volumes (B11:G14) using the sales mix options selected in B3 and B4.	4	?
E4	In row 17 calculate Profit Before Tax (Contribution Margin - Fixed Costs) for each sales volume (this will return a negative value where the unit volume is less than the break-even).	1	?
E5	In B20 calculate the state tax incurred for each sales volume using the rate at the top of the sheet. If the profit was zero or below, the calculation should return 0.  (Do NOT just type 0 as the volumes can change.) Apply appropriate cell referencing so that the formula can be dragged down and then across to complete the tax table.	3	?
E6	In row 24 calculate the profit after factoring in the income tax (this may return a negative value where the unit volume is less than or close to the break-even).	2	?
E7	In row 26 work out the profit for each sales volume as a percentage of sales.	1	?
E8	Complete the Break-Even Analysis Line Chart Data in B29 to G31 for each of the Unit volumes. (Note Total Costs includes Variable Costs, Fixed Costs and Income Tax)	2	?
E9	Use the data in A29:G31 to create a 2D Line Chart that plots the Total Fixed Costs, Total Costs & Total $ Sales.  Add the Sales Volume in Units figures as Horizontal Axis Labels and add  the Chart Title: Profit Volume Forecast (mark given only if this exact title is used). Use chart tools (not drag and drop) to position the legend at the top of the chart. The completed chart should look something like this (although values will vary). The point where the lines cross is the break even point.   N.B. Do NOT use Pivot Tables or Pivot Charts. Only include one chart, ensure it is on the Profit Volume Forecast Sheet and that it is a 2D Line Chart with no line markers (not a Scatter chart with smooth lines).	4	?

 

Section F	The following are to be completed in the Inventory Worksheet	15
F1	The inventory shows a list of parts kept in stock, who supplies them and how many are currently in stock. In P4 calculate how many parts are supplied by the supplier shown in N4. Copy the formula down to P6.	2	?
F2	In Q4 calculate the average cost of parts supplied by the supplier shown in N4. Copy the formula down to Q6.	2	?
F3	Some parts have been re-ordered but not yet delivered, these are said to be on backorder and are indicated with a Y in the backorder column. In R4 calculate how many parts on backorder are supplied by the supplier shown in N4. Copy the formula down to R6.	2	?
F4	We have decided to classify parts into two categories. Parts that are less than $5 will be category A and all other products will be Category B. Create a formula in the Category column to calculate the correct category for each part.	2	?
F5	If a stock item falls below the minimum stock level shown in column G  and is NOT on backorder, then we need to reorder it.  In K4 create a calculation that will return “Y” if the item needs reordering and leave the cell blank if it does not.	3	?
F6	Challenge Question (limited help will be given) When we re-order we like to ensure we will have 25% more than the minimum stock level. Create a calculation that checks if we need to reorder, if we do, calculate the number we need to re-order to have an in stock value of 25% over the min stock level, return 0 if re-order not required. The suppliers also impose a minimum order quantity (see column O). Modify your calculation so that if we need to re-order and the re-order quantity is less than the Min Order Qty for the supplier, it returns the appropriate Min Order Qty.   (2 Marks will be given for doing just the first part correctly).	4	?

TOTAL MARKS     80

 

 

Further Clarification:

1. The BOM is like a recipe, it itemises which components and how many we need to make one of each type of product.
2. In the CVP, the Breakeven costs are the costs incurred to produce the number of units required to break even, so if it usually costs $10 in materials to make one unit, and I need to sell 5 units to break even, my Breakeven material costs are $50.

If any cells are incorrectly formatted (e.g. showing as currency where they should be general or showing as general where they should be % please feel free to adjust them.