Homework answers / question archive / BBE5013 BUSINESS MODELLING WITH SPREADSHEET SEP-DEC 2020                                  Term Assignment Two                                                              CONFIDENTIAL LOCATION: IUMW, CITY CAMPUS LEVEL 5 (Semester 1 / Year 2) TERM ASSESSMENT PERIOD SEPTEMBER – DECEMBER 2020                                                           MODULE CODE: BBE5103                                               BUSINESS MODELLING WITH SPREADSHEET                                                       ASSIGNMENT NUMBER TWO                                             ASSIGNMENT DEADLINE: 18TH DECEMBER 2020 ANSWER ALL QUESTIONS Equipment or materials required – Computer, Spreadsheet and Statistical Tables   BBE5013 BUSINESS MODELLING WITH SPREADSHEET Question 1 (15 marks) Owners of AAB Ltd are thinking of introducing a new product to the market

BBE5013 BUSINESS MODELLING WITH SPREADSHEET SEP-DEC 2020                                  Term Assignment Two                                                              CONFIDENTIAL LOCATION: IUMW, CITY CAMPUS LEVEL 5 (Semester 1 / Year 2) TERM ASSESSMENT PERIOD SEPTEMBER – DECEMBER 2020                                                           MODULE CODE: BBE5103                                               BUSINESS MODELLING WITH SPREADSHEET                                                       ASSIGNMENT NUMBER TWO                                             ASSIGNMENT DEADLINE: 18TH DECEMBER 2020 ANSWER ALL QUESTIONS Equipment or materials required – Computer, Spreadsheet and Statistical Tables   BBE5013 BUSINESS MODELLING WITH SPREADSHEET Question 1 (15 marks) Owners of AAB Ltd are thinking of introducing a new product to the market

Business

Share With

---

BBE5013 BUSINESS MODELLING WITH SPREADSHEET

SEP-DEC 2020                                  Term Assignment Two                                                              CONFIDENTIAL

LOCATION: IUMW, CITY CAMPUS

LEVEL 5 (Semester 1 / Year 2)

TERM ASSESSMENT PERIOD

SEPTEMBER – DECEMBER 2020

                                                          MODULE CODE: BBE5103

                                              BUSINESS MODELLING WITH SPREADSHEET

                                                      ASSIGNMENT NUMBER TWO

                                            ASSIGNMENT DEADLINE: 18^TH DECEMBER 2020

ANSWER ALL QUESTIONS

Equipment or materials required – Computer, Spreadsheet and Statistical Tables

 

BBE5013 BUSINESS MODELLING WITH SPREADSHEET

Question 1 (15 marks)

Owners of AAB Ltd are thinking of introducing a new product to the market. The following information was provided to them by their staff.

They are now seeking your help to design the following spreadsheet model with the required formula functions built in for the purpose of performing risk evaluation.

You have to prove that your spreadsheet is operable by inputting number into the cells B15 and C15, shaded in yellow.

A B C D E

1

Sales =| Winning | pobable Enter a formula in the blank

 

2 Forecast /Probability| . 1. cells. The numbers display

 

3 | 1320] 6%] | 288) must be rounded up using

 

4 | tog] 12%] | formula function =ROUND()

 

5 | 2570] 16%] 823

 

p 3195) 20%) [85

 

7 | 3820] 14%] | 882

 

8 | aaa] 7H] 08

 

9 | 5070] oH

 

10 | 569s] |S

 

11 | 6320) 8H] |S

 

12 p 7570] 1%] C8

 

13 Generate the probability

 

4 Sales Min | Soles Max Probability ok value in this cell using the

 

— =PROB() function

 

15 Target Effort

 

16

 

17 Sales Forecast = FG

 

18 Costs Forecast= FI

 

19 | Net Profit= Tere TAL FO Calculate the Sales Forecast

 

a forecast to show and Costs Forecast values

 

your model works using the =SUMIFS()

 

Here are the formula functions you should be using:

=PROB(x_range,prob_range,lower_limit,[upper_limit])

=SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2,...)

=ROUND(number,number_digit)

Question 2 (20 Marks)

 

Appended on the right hand side of this page is a recorded sales amounts from 1° October 2019 to 25"

November 2019. You are asked to find out if there an identifiable trend for the sales.

 For this question, you are required to prepare the following:

a. Using the data appended on the right to create a Data Table using the spreadsheet function.

b. You are required to generate 7-days moving averages and display them on the Moving Averages column. 1% moving average amount should appear on 07-10-19.

c. You are required to create a line graph to display the trendlines for Sales Amount and Moving Averages.

d. The graph generated has to be properly labelled.

BBE5013 BUSINESS MODELLING WITH SPREADSHEET

SEP-DEC 2020 Term Assignment Two CONFIDENTIAL

Question 3 (15 marks)

Explain in your own words the difference between Risk Analysis and Risk Management, and illustrate your answer with examples how you would perform risk analysis using spreadsheet and evaluate risk premium.

(Note: your written answer should not be longer than 200 words. Your illustration should be done on spreadsheet.)

Question 4 (50 Marks)

A company planned to venture into a new line of busines. The owner has collected the following data from a recent market survey. He wants to know the risk of venturing into the business.

• The information are provided below have to be duplicates on a spreadsheet for the purpose of simulation. Except for the numbers in the cells shaded in yellow are manually input, the rest of the numbers are created via formula.

The owner also found out that the venture could either make a maximum profit of $6,000 or a maximum loss of $2,200. You are requested by the owner to create a simulation model for him to evaluate the risk of the venture. A business modelling consultant has prescribed the following steps for you to design the required model as follow:

Input Values (input)

Nominal Min Max Stochastic

Leads per Month (L) 1750 1400 2100 1780.59523

Cost Per Lead (C) $0.65 $0.38 $0.98 $0.84

Conversion Rate (R) 3.8% 1.8% 7.5% 7.270%

Profit per Sale (P) $55.00 $49.00 $61.00 $57.04

Overhead per Month (H) $800.00

Results (calculations)

Deterministic Stochastic

Monthly Income: $3,657.50 $7,383.30

Monthly Expenses: $1,937.50 $2,303.79

Projected Monthly Profit: $1,720.00 $5,079.51

 

BBE5013 BUSINESS MODELLING WITH SPREADSHEET

SEP-DEC 2020                                      Term Assignment Two                                                          CONFIDENTIAL

• Step 1 – Create a data model

• Step 2 – Append a Stochastic column as shown in table (see above).

• Step 3 – Create a section to compare “Deterministic” and “Stochastic” results (see above).

• Step 4 – Create a simulation model on a separate spreadsheet tab to generate random numbers using the formula function =RAND() for Leads per month (L), Cost per Lead (C), Conversion Rate

(R), Profit per Sales (P) and Overhead per Month (H).

• Step 5 – Provide a column for Profit calculation for all random scenarios.

• Step 6 – Establish a frequency distribution table for histogram and frequency plot.

• Step 7 – Display Graphs

• Step 8 – Display and complete your answer in the blank cells below. (This should be done on a separate tab on your spreadsheet.)

Total marks for the whole paper is 100 marks.

**END OF QUESTION PAPER**

Projection Forecast Quartile

Max Profit

Min Profit

N =

Simulated

Max Profit

Min Profit

Profit (Mean)

Std Dev

Median

3rd Quartile

1st Quartile

Inter-Quartile

Prob (Profit > 0)

Prob (Profit < 0)

Making Profit > 3000

Making Profit <0

P ( 0 < profit < 3000)