B15 for G K L M N o -MONO A B D E F H J 1 2 In tthis worksheet you will use the Data Table function to create a table of values that are based on the calculation in cell B20. The table will then 3 show you many possible payments, depending upon the interest rate and the term. 4 1. Using the EPMT function calculate the monthly payment on a loan of 42,000 for 3 years at 3% 5 2. Create a Data Table that shows the monthly payments using various interest rates and number of years shown below. 6 3. In a separate formula calculate the amount of interest paid on the $42,000 loan at 3% for 6 years. To do this have your formula in F24 reference the appropriate monthly payment within the data table. 7 8 9 Using a Data Table 10 11 $0.00 1.00% 1.50% 2.00% 2.50% 3.00% 3.50% 4.00% 12 Principle $ 42,000.00 2 13 Years 3 3 14 Rate 3.0% 4 15 Payment 5 16 6 17 18 Calculate the amount of interest paid on a $42,000 loan at 3% for 6 years. 19 Formula Answer Here ====> 20 21 22 23 24 25 26 27 28 29 Part 1 Data Table Loan Payments Part 2 Break Even Analysis Part 3 Solver B13 E fx D E F. G H J L M N. 0 P Q Breakeven/Sensitivity Analyses In this worksheet you will first put in the formulas for Total Fixed Costs and Total Variable Costs, along with Contribution Margin and Breakeven Point. You will then generate two Data Tables based on the formula in cell B22. The Data Table will then show you the various Break Even points depending on the Labout Cost Per Hour and the Insurance Expense. Then the second Data Table will show the Break Even points based on Labout Cost per hour and the various Sale Price per Unit. Insurance Cost $ 90,000 $ 95,000 $100,000 $105,000 $ 110,000 12 A B 1 2 3 4 Sale Price per Unit $ 125.00 5 6 Fixed Costs 7 Land $ 155,500 8 Buildings $ 332,500 9 Manufacturing Machinery $ 532,000 10 Office Equipment $ 212,800 11 Utilities $ 30,500 12 Insurance $ 99,700 13 Total 14 15 Variable Costs/Unit 16 Labour $ 15.00 17 Advertising $ 2.00 18 Shipping & Receiving $ 5.00 19 Total 20 21 Contribution Margin 22 Breakeven Point 23 24 25 26 27 28 29 30 31 Part 1 Data Table Loan Payments Labou $ $ $ $ $ $ 13 14 15 16 17 Sale Price 125 $ $ 100 $ 150 $ 175 $ 200 $ Labou 12 13 14 15 16 17 $ $ $ Part 2 Break Even Analysis Part 3 Solver + 1 B 1 Uv = 5 + 2 Merge & Center CE% 0 0 Formatting Table Styles > > Filter Select Data Clipboard Font Alignment Number Styles Cells Editing Analysis E16 X fic A B D E F G H J K L M N o P Q R 1 Moo's Ice Cream Company 2 Moo's Ice Cream Company would like to know what the Maxmimum Sales Revenue would be in any given day with the following constraints: 5. 20 Total Sales Revenue $ 3 4 Island Delight $ Regular Cone Subototal: $ 5 6 1. They have 5,321 Waffle Cones available, they serve Rocky Road and Cookie Dough in Waffle Cones. Once the Waffle Cones are gone there are no more until the next day. 5. 25 7 8 9 10 Rocky Road $ Waffle Cone Subototal: $ 2. They only carry enough Cookie Dough each day to serve 2,183 cones and when it is all gone they can not get anymore until the next day. 5. 75 Cookie Dough $ Waffle Cone Subototal: $ Constraints Waffle Cone Cookie Dough Server Capacity 5, 321 2, 183 5, 500 3. The servers can only serve 5500 customers per day. They serve Island Delight in regular cones and have an unlimited supply of these. Total Sales by Type of Cone Regular Cone Waffle Cone Total Served Using the Solver function put in these contraints to calculate the maximum revenue. 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Use the information in Tech Plugin T4 to help with this worksheet. Part 1 Data Table Loan Payments Part 2 Break Even Analysis Part 3 Solver
