Fill This Form To Receive Instant Help
Homework answers / question archive / 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
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 breakeven point. You have also been asked to show a forecast for different unit volumes and different sales mixes and produce a breakeven 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
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 

A1A8 
This sheet contains a list of employees at the company followed by a series of multiplechoice 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 breakeven 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 breakeven 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 BreakEven Variable Costs for the two products. 
4 
? 
D11 
In F19 calculate the BreakEven Contribution Margin (this is the contribution margin for the total number of units required to break even.) 
1 
? 
D12 
In F20 calculate the BreakEven Profit Before Tax. (Break even profit would normally be 0, but because we rounded up our breakeven 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 breakeven). 
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 breakeven). 
2 
? 
E7 
In row 26 work out the profit for each sales volume as a percentage of sales. 
1 
? 
E8 
Complete the BreakEven 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 reordered 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 reorder 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 reorder to have an in stock value of 25% over the min stock level, return 0 if reorder not required. The suppliers also impose a minimum order quantity (see column O). Modify your calculation so that if we need to reorder and the reorder 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:
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.
Already member? Sign In