代写COMM 3500 – Winter 2025 Midterm帮做R编程

COMM 3500 – Winter 2025

Midterm (Excel Part) – 25 Marks

The European Bakery in the Halifax area bakes 80 trays of its famous “borek”, a well-known savory pastry in the owner’s homeland, at the beginning of each day. Using the historical data, the owner believes that daily demand will follow the distribution shown in the following table:

Daily Demand	Probability
10	0.05
30	0.1
50	0.1
70	0.25
90	0.2
110	0.2
130	0.1

Each tray costs the European Bakery $25 and can be sold for $50. Any unsold trays can be salvaged/donated for $5 at the end of the day.

The (daily) fixed cost has two components. The first component is known for certainty: the rent, insurance, and employee salaries add to $400 per day (daily conversion of $10,000 per month). The second component is uncertain and estimated using a continuous uniform. distribution with lower bound $a and upper bound $b. This includes maintenance (e.g., due to weather conditions), energy usage, shipping, etc., converted as daily expenses.

You will determine a and busing the last 2 digits of your Banner ID. Set the smaller value times 100 as a and larger one times 100 as b. If they are equal, deduct 1 from adigit before multiplying it with 100. If there is a 0 in the last two digits or you get 0 after deducting 1, take a  =  100 and calculate b accordingly. If they are both 0 or a digit is 0 and the other is 1, use a  =  100 and b  = 200. Example: B01234567 yields a = 600 and b = 700. Example 2: B01234599 yields a  = 800 and b  = 900. Example 3: Both B01234500 and B01234501 yield a  =  100 and b = 200.

A. Open a new Excel workbook. In cell A1, type your first and lastname. In cell A2, type your Banner ID and finally, in cell A3, type your Dalemail. Make them in bold font in size 16. If any of these items are missing or incorrect, you will receive a mark of zero for the Excel file!

B. Change the name of the worksheet as ‘BE_analysis ’. Copy the worksheet or create another one and change its name to ‘Simulation ’. Both worksheets must contain the information in A. in the requested format.

C. Please note that you will be marked based on the formatting in your spreadsheets too. Your values and plots must be neat and readable; and you must use frames and highlight cells with a formula, change decimals and/or format, etc. as in the Excel files we use in lectures/labs.

D. Answer the questions below. Save your Excel file using filename: Last Name_First Name_Banner ID and place the file in the dropbox: Brightspace>Assessments>Assignments>Midterm (Excel Part).

E. Important: It is recommended that the students first save the file to a safe place with the desired name and then, keep saving it frequently throughout the exam to avoid any unforeseen events that may result in crushing of the exam file. It is student's responsibility to upload a working Excel file. Perform. the following tasks:

1. Expected values and B-E analysis (3 Marks):

a. Enter the necessary data into ‘BEanalysis ’ worksheet. Find the expected demand and expected fixed cost. (1.5 Marks)

b. Find break-even point using the mean fixed cost. Is it attainable for the given information, why or why not? Comment using bold red font. (1.5 Marks)

2. Simulation (Part 1) (10 Marks):

Simulate 1 month (25 workdays) of operation to calculate the bakery’s total monthly profit. Your worksheet must have the following four areas:

I. The table of demand probability distribution (including the corresponding cumulative probability).

II. Input data area that neatly displays the information provided in the question including the probability distribution of the fixed cost.

III. Simulation area that neatly shows 25 replications to calculate daily profit for 25 days.

IV. Analysis area that shows the total monthly profit.

3. Simulation (Part 2) (7 Marks):

a. Replicate this calculation 1000 times and compute the average total monthly profit. (3 Marks)

b. Create a summary statistics part and the histogram of the monthly profit. Comment using bold red font. (3 Marks)

c. Calculate the probability of making more than $x profit per month. You determine $x according to the upper bound of the uncertain fixed cost (1 Mark):

$X	UB
$28,000	UB ≤ 200
$25,000	200 < UB ≤ 400
$20,000	400 < UB ≤ 600
$15,000	600 < UB ≤ 800
$10,000	UB > 800

4. Scenario Manager (3 Marks):

The European Bakery would like to investigate the profitability of baking 60, 80, 100, or 120 trays at the start of the day. Create a table using Scenario Manager (don’t forget to replace cell addresses in the table with the labels). Which quantity would you recommend? Why? Comment using bold red font.

Accurate data entry and general formatting (2 marks)

Late Submission Penalties: This assignment should take around 30 mins and you are allocated 24 hours to complete it. My advice for you is to have at least a 10-15 mins buffer before the due date/time for submitting your Excel file as it is worth 25% of the Final. If you think you have a connectivity issue or some other problem that may cause a delay, I recommend submitting the file even earlier. For late submissions, we have apenalty policy:

<30 mins	10%
>30 mins but <1hr	20%
>1hr but <2hr	40%
<2hr but >3hr	80%
>3hr	Submissions not acceptable

Files sent via e-mail will receive no marks and there will be no exceptions. Lastly, there is a 10% penalty in case the Excel file doesn’t include the necessary details (name, Banner ID, etc.).