Making Predictive Analyses: Course Project on Loan Selection

Making Predictions with Forecasts and Data Course Project Instructions: To submit this assignment, please refer to the instructions in the course. Copyright © 2018 eCornell. All rights reserved. All other copyrights, trademarks, trade names, and logos are the so This is your course project. Make sure to save all progress often, as you will be submitting Working through this project, you will have the opportunity to work with fictional scenarios a In Part One of the project, you will analyze analyze data to select the most suitable loan to In Part Two of the project, you will create a scenario report for fictional company Haeger B In Part Three of the project, you will combine all you have learned to assess business data decision whether to increase marketing expenses.

Periods Per Year Annual Interest Rate Duration in Years Periodic Payment Loan 1 6 4.50% (3,000) Loan 2 2 10 (5,499) Loan 3 12 7.40% 6 (1,200) Loan 4 4 5.50% 4 Loan 5 2 8.50% 5 Imagine that your company has decided to upgrade hardware and software in your department. Complete the table above to include the missing values using financial functions and formulas. Critera to select the best loan: Loan 4 should reflect a balloon payment of $10,000 The periodic payment should not be in excess of $5500 The loan should be paid off in 55 months or less Total interest expense should not be greater than $8,000 Select the most suitable loan and create an amortization schedule in the next worksheet, "Par You have been given the loan information in the table above, but notice that each one is missin suitable loan. The loan objective is to minimize interest even if it means a slightly higher periodi

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Present Value Future Value Total Payments Total Interest 74,541 - - 74,541.00 85,016 - (109,980.00) (24,964.00) - (86,400.00) (86,400.00) 56,956 - - 56,956.00 68,998 - - 68,998.00 . rt 1 Data." ng a key piece of data that will allow you to select the most ic payment.

Amortization Table Original Loan Annual Interest Rate Loan Duration in Years Number of Periods per Year Ending Value of Loan Periodic Payment Period Remaining Principal Interest Payment Ending Balance Due

Principal Payment

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Haeger Book Use these instructions to complete the activities and data In the "Books" tab, write formulas in the Projections table to Haeger Books has three different lines of books, each sold Using the initial marketing costs and units sold in the sprea baseline. Create three additional scenarios in which the ma scenario is geared towards the novel line in which the mark self help, and $17,000 for novel. This first scenario should r The next scenario is focused on the self help line in which self help, and $11,500 for novel. This second scenario shou respectively. The third scenario is focused on the text line $9,000 for self help, and $15,500 for novel. This third scena respectively. Name each scenario with the name of the line Create a Scenario Report including all scenarios that show Income before Taxes, and Marginal Net Income. Using the and justify your answer. Hint: Did you name cells?

Shop, Inc. a tables on the following worksheet tab, "Part 2 Data." o calculate Marginal Net Income accurately. independently with different markets and demands. adsheet, create a scenario called "Standard" as a arketing expenses and units sold change. The first new keting costs amount to $15,000 for text, $11,000 for reflect units sold as 4200, 5500, and 5000 repsectively. the marketing costs are $10,000 for text, $12,000 for uld reflect untis sold as 3000, 6000, and 2000 in which the marketing costs are $20,000 for text, ario should reflect units sold as 5400, 4500 and 4000 e that they are focused on. ws the projected figures for Sales, Contribution Margin, e summary, propose which Scenario should be used

Assumptions: Boo Text Revenues Units sold 4,800 Unit sales price $44.99 Variable Manufacturing Costs $18.50 Distribution $3.00 Fixed expenses Marketing $17,500 Manufacturing Total Rent Total Other items Marginal tax rate Variable expenses (on a per unit basis)

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

SP18

ok Categories Projections: Self Help Novel 5,000 4,000 Sales $74.99 $54.99 Variable Costs: Manufacturing $17.50 $15.00 Distribution expense $4.00 $4.00 Total Variable Costs Contribution margin $10,000 $15,500 Fixed expenses: $90,000 Marketing $30,000 Manufacturing 90,000 Rent 30,000 35% Total fixed expenses Income before taxes Estimated income tax expense Marginal net income

- $ - 120,000 (120,000) (42,000) $ (78,000)

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Part 3a Instructions Apply the following instructions to the data on the next tab, "Part 3a Data." Add the missing function in the assumptions section of the forecast to reflect accurate values for Sales Units when Marketing Expense is adjusted. Create a one-variable data table for changes in Marketing Expense to indicate how these changes will impact Sales Revenue and Profit before taxes. Be sure to relate different marketing investments to unit sales so both tables are accurate. You are considering an increase in Marketing expense to increase our sale of jeans. We always sell exactly what we produce so an increase in marketing will definitely increase our sales. Given the data, create a two- variable data table varying Marketing Expense (same values as the last table) and varying Selling Price per unit from $300-$500 in increments of $25 to determine the impact on Profit Before Taxes. Label properly.

DYSON SNOW, INC Forecast for Sa Assumptions: Revenue: SKIS Sales in units Selling price per unit $ 379.00 Variable Costs per Unit: Manufacturing Costs $ 125.00 Distribution $ 49.95 Fixed Costs: Marketing $ 500,000 Rent $ 60,000 Selling and Administrative $ 122,000 If Marketing Exp is: Then Units Sold is: $ 500,000 10,000 $ 600,000 22,500 $ 700,000 32,000 $ 750,000 45,500 $ 800,000 65,000

CORPORATED ale of Skis Projections: Sales Revenue $ - Variable Costs: Manufacturing - Distribution - Total Variable Costs - Contribution margin - Fixed Costs: Marketing $ 500,000 Rent 60,000 Selling and Administrative 122,000 Total Fixed Costs 682,000 Profit before taxes (682,000) Marketing Investment Impact on Revenue and Profit Marketing Investment and Price Adjustment Impact on Profit

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Part 3b Instructions In column H create a total for beverages demanded on a given day. Complete the following instructions in the data table on the following tab (Part 3b Data ) Your Beverage Company is interested in Forcasting late orders. Each row represents an order day and is broken down by country where the orders originated. For each country (columns C-G) randomize an order volume (whole number) from 0- 20. Sometimes beverages take too long and are considered late. This happens between 2% and 5% of the time. In column I simulate the probability of beverages being late (percentage format 2 decimals). Not a whole number. In column J calculate a value for potential number of late beverages for each row (use Potential Beverage Demand Total x Probability of Late Orders). Round to the nearest whole number. Fill in the summary table to the right of the data with the potential total late orders (column J) by product type for all 28 days.Do not display decimal places. Reference the names in column M for efficiency.

Daily Order Demand for Beverages - Simulaton Potential Orders for a sample 28-Day Period Beverage Day Product United States England Germany France China 1 Latte 2 Tea 3 Tea 4 Tea 5 Espresso 6 Espresso 7 Tea 8 Espresso 9 Espresso 10 Espresso 11 Espresso 12 Espresso 13 Latte 14 Espresso 15 Cappuccino 16 Espresso 17 Latte 18 Espresso 19 Espresso 20 Cappuccino 21 Latte 22 Tea 23 Espresso 24 Espresso 25 Cappuccino 26 Tea 27 Tea 28 Tea Demand Total (PBD)

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Probability of Late Potential Orders Total Late Summary Table Product Potential Total Late Latte Tea Espresso Cappuccino

DYS544_course-project

Related Documents