We Helped With This Finance Math Homework: Have A Similar One?

Fall 2017

ABM 203: Data Analysis for the Agri-Food System

Homework #1

Due to D2L dropbox by 3:00 PM on Thursday, September 14, 2017

For this homework, you will use the Excel workbook entitled “Homework1” in the “Homework #1” folder in D2L. 

The workbook contains data on an agri-food company that produces 6 products: A, B, C, D, E, and F. 

The worksheet entitled “Prices and Costs” contains information on the sales prices and costs of production for each product. The company has two types of costs for each product:  i) A per-unit variable cost

ii) A fixed cost (workers’ salaries). The worksheet lists the number of workers that the company employs to produce each product. Each worker is paid a salary of $80 per day. Assume that workers are paid their daily salaries regardless of the number of units sold by the company on a given day.

The worksheet entitled “Sales per Day” has data on the company’s sales (in units) by product and day for each day of the first quarter of 2015.

Assignment:

1.      Download the “Homework1” workbook from D2L and save it as

“Homework1_Lastname”, where you replace Lastname with your last name.

2.      In the “Sales per Day” worksheet, do the following:

a.       Use “Freeze Panes” to such that the top 3 rows and left-most column stay fixed when you scroll through the file.

b.      Calculate the company’s profits for each day for each of the 6 products (A, B, C, D, E, and F). Use the yellow-shaded area in the worksheet for these calculations.  profits = revenue – costs = price*qty – (per unit variable cost*qty + fixed costs). Note that here, the fixed costs are total worker salaries per day. Use relative, absolute, and/or mixed cell references as needed.

c.       Calculate the company’s total profits per day. Use the green-shaded area in the worksheet for these calculations and bold the text for these answers.

d.      Calculate the company’s total units sold and total profits by product and overall for the entire first quarter of 2015. Use the blue-shaded area in the worksheet for these calculations and bold the text for these answers.

e.       Format all profits cells as currency (in dollars with two decimal places and a 1000s separator (comma)). “Left Align” the Date cells and “Right Align” all other cells.  Format the units sold cells as whole numbers with a 1000 separator (comma) but no decimal places.

3.      Add a worksheet to your workbook and name it “Monthly Profits”. In this new worksheet, create a table with months as the rows (January 2015, February 2015, and March 2015) and the products as the columns (A, B, C, D, E, F). Fill in the cells in the table by calculating the company’s total profits by product and month. Use references to other worksheet(s) in the workbook to complete the table. Title the table “Total Monthly Profits, January 2015-March 2015”.  Add borders and format the text as needed to make your table look like this:

Total Monthly Profits, January 2015-March 2015

January 2015

February 2015

                       March 2015                                                                                               

The product, A-F, and month text should be bold, and the profits cells should be regular

(not bold). Format all profits cells as currency (in dollars with two decimal places and a 1000s separator (comma)). “Left Align” the month cells, Center the title and the text “Product” (using “Merge and Center”), and “Right Align” all other cells.  

4.      Add another worksheet to your workbook and name it “Average Daily Profits”. In this new worksheet, create a table similar to the Total Monthly Profits table above but this time fill in the cells in the table by calculating the company’s average daily profits by product and month. Use references to other worksheet(s) in the workbook to complete the table. Title the table “Average Daily Profits by Month, January 2015-March 2015”.

Add borders and format the text as needed to make your table look like the one above. Use the same formatting as in question #3. 

5.      Check all worksheets in your workbook to make sure the columns are wide enough to show the full text. “#########” is not acceptable for any cell in the workbook. 

6.      Submit your workbook to D2L by the 3:00 PM deadline on Thursday, Sep. 14, 2017.

Note: The answers you submit for this homework assignment must be your own work. Any submissions that are found to be copied and pasted from another student will result in a grade of zero for all students involved, and will be reported to the Registrar, potentially resulting in further disciplinary action.