IDS 355
Fall 2019
Assignment 1
Due Friday 02/22 11:59pm
40 points
Instructions
Deadline
This assignment may be submitted for full credit until Friday, Feb 22th at 11:59pm. Solutions will be posted one week after the deadline, after which late submissions will not be accepted. If you need an extension for an assignment, you must ask your TA in advance. Please consult the syllabus for details about lateness penalties and extension requirements. In any event, we strongly recommend submitting your assignment early, in case problems arise with the submission process.
Problem Guidelines
Because using spreadsheet software is a basic business skill, the assignments are intended to give you practice in structuring problems as well as simply finding the answers. All problems must be solved using Excel formulas to receive credit. If, for example, you solve a problem on paper and then copy the results to Excel, you will receive no credit for that problem.
Submission Guidelines
All assignments are to be submitted using the Blackboard site. Assignments should be uploaded using the upload link associated with the assignment, not the digital Dropbox. It is important that all homework files include the student’s NetID, so they can be easily identified and sorted. The file containing homework assignment 1 submitted by the student with NetID
aker2 should be named
aker2hw1.
For this assignment, please submit one Excel file. Use a separate worksheet for each question set and label the worksheets. To rename a worksheet, right-click the worksheet tab located at the bottom of your cu
ent sheet and use the rename option. Label the tabs as “Q Set 1”, “Q Set 2”, and “Q Set 3”, respectively. Be sure to label all results clearly.
Questions
Question Set 1. A manager has tracked days worked and Pizzas sold as given below.
Days Worked
Seasons
Winte
Spring
Summe
Autumn
Winte
Cashie
Jan
Fe
Ma
Ap
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Joey
21
12
26
15
15
6
26
Ross
23
9
12
23
18
Rachel
12
18
4
23
23
30
12
Monica
23
18
26
12
Phoebe
23
18
18
18
Chandle
26
12
18
26
Pizzas Sold
Seasons
Winte
Spring
Summe
Autumn
Winte
Cashie
Jan
Fe
Ma
Ap
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Joey
378
24
572
3
285
132
494
Ross
761
189
288
345
36
Rachel
276
27
8
437
414
72
276
Monica
414
412
39
264
Phoebe
345
396
396
378
Chandle
572
264
45
52
1. For each cashier, compute his or her overall productivity (in Pizzas sold per day) for Spring and Summer. You should have one number per cashier. (3pts)
2. For each cashier, compute his or her productivity (in Pizzas sold per day) for Autumn. Using the MAX function, find the highest productive cashier for the Autumn among the cashiers. (3pts)
3. Compute the overall productivity for each cashier (in Pizzas sold per day) for the entire year. Use the MIN function to find the lowest productive cashier for the year among the cashiers. (3 pts)
4. Which month has the lowest overall productivity? (2 pts)
Question Set 2. For this question set, you will calculate labor productivity and multifactor productivity, both in tons of rice produced per dollar value of inputs.
A Rice Manufacturer employs 75 field workers (each working 40 hours per week at a wage of $10/hr) and 20 line workers (each working 40 hours per week at a wage of $20/hr).
The business has capital expenses of $15,000 per week and produces 375,000 tons of rice per week.
Recently, the company decided to invest on additional equipment which increased the capital expenses by 6%. Also, the number of field workers was cut short by 25% whereas line workers were 1% more recruited. Both filed and line workers were given a raise of 8% and 5% respectively. Due to the above-mentioned changes, the new system reported to have a 4% more output than the old system.
You will need the following formulas:
Labor productivity =
Multifactor productivity =
After copying the table below into Excel, solve the following questions and fill the table
OLD SYSTEM
% CHANGE
NEW SYSTEM
Number of Field workers
75
-0.25
?
Hours
40
Wages
10
0.08
?
Total cost for field workers
?
Number of Line workers
20
0.1
?
Hours
40
Wages
20
0.05
?
Total cost for line workers
?
?
TOTAL LABOR COST
?
?
Capital expenditure
15000
0.06
?
Output
375000
0.04
?
LABOR PRODUCTIVITY
?
?
MULTIFACTOR PRODUCTIVITY
?
?
% CHANGE FOR LABOR PRODUCTIVITY
?
% CHANGE FOR MULTIFACTOR PRODUCTIVITY
?
1. Calculate the Total Input Cost for Old and New systems (Labor cost + capital expense) (2 pts)
2. Calculate the Capital Expenditure and Output of the new system (2 pts)
3. Calculate labor productivity including all employees (line workers & field workers) for the old and new systems, in tons of output per dollar of input. (4pts)
4. Calculate the multifactor productivity (including line workers, field workers, and capital expenses) for the old and new systems, in tons of output per dollar of input. (4pts)
5. Calculate the percentage changes in labor and multifactor productivity. (3pts)
Question Set 3. A bakery has a production target of 1000 units of donuts,
ead and muffins each in a month. The baker spent $20,000 on setting up the bakery, which could be accounted as the fixed cost. The production cost and selling price for the items is listed in the table below. For this question set, use the following formulas and information:
[Total Cost] = [Production] x [Variable Unit Cost] + [Fixed Costs]
[Total Revenue] = [Production] x [Unit Revenue]
[Total Profit] = [Total Revenue] – [Total Cost]
[Break Even Point] = [Fixed Cost] / ([Unit Revenue]-[Unit Variable Cost])
Item
Unit Cost
Unit Revenue
Donut
1.5
5
Bread
2.5
5
Muffin
2
4
1. Calculate Total profit for the bakery in a month, given they bake 1000 units each of all 3 products (3pts)
2. Create a data table (as demonstrated during lab exercise 2), to show the change in Total profit co
esponding to change in Production from 2000 to 3500 units with increments of 100. You must use a data table structure to receive credit for this problem. (6pts)
3. Create a scatter chart that displays the variable total profit (and no other variables) as a function of Production. At low production quantities, total profit may be negative but should still be displayed. Label your chart axes appropriately. (3pts)
4. What is the
eak-even point? (The Production for which the Total profit is zero) (2 pts)