Modular 7 Business Case Senary Scenario As the finance director of a GM Division that focuses on selling automotive component parts to non-GM customers. You are requested to quote on a set of parts to...

1 answer below »
Business case spreadsheet needs complete. Additional instructions are included in Senary Business Case Docs...I paid for a 1000 words because I need a very good score on this one.Last assignment from this class



Modular 7 Business Case Senary Scenario As the finance director of a GM Division that focuses on selling automotive component parts to non-GM customers. You are requested to quote on a set of parts to Daimler (Daimler is the parent company of Freightliner, Mercedes Benz and Cooper. The Quote is for the electronic cluster (cluster is the electronic display a driver observes when sitting in the drivers seat. ( see picture) to be used in the Mercedes Benz s class. From Mary B CEO of General Motors To Finance Manager of GM Division (YOU). The attached is a request from Daimler to Quote on a very important and first step in earning Daimler Business. The Below are the specifics to the quote (other than product specific prints that have been sent to engineering) these are as stated below: 1. Annual volumes 200,000 2. To begin January 1, 2021. 3. Quality is expected to be Perfect every time 4. Annual 3% price reduction 5. Delivery was to be to the Plant in Greenville South Carolina. 6. The winner of the quote will solely be determined on price. 7. Only one quote is submitted, no requotes will be allowed. 8. No presentation will be allowed bestowing the virtues of each supplier 9. If awarded and volumes do not meet 200,000 annual vehicles, no adjustments in price will be made. 10. Any raw Material price increases are the responsibility of the supplier, no price adjustment will be allowed. 11. Tooling investments paid by supplier 12. Machine or other investment costs paid for by supplier 13. Quote due in 10 days upon receipt of the above. Other notes: 1. All four of the major automotive electronic suppliers will be quoting on the business 2. A rough estimate in terms of the sales value of this quote. Avg price to GM car plants for a cluster package is $500. Thus, in round terms $500 X 200000 vehicles x 5 years =$500Mil. 3. Overall Divisional sales $4 Bil The overall challenge is to win the quote, however, certainly not at loss and must equal or exceed the following financial requirements. · IRR greater than 15% · Payback less than or equal to 2 years · NPV greater than $1,000,000. Marketing indicates to win this bid, we will need to be at $470 per car set price first year. Please let me know when we have a signed agreement with Daimler. Regards, Mary B. ===================================================================================== You get your staff together, along with directors of engineering, manufacturing, marketing, sales and purchasing. Your task is to coordinate the bid. Thus, in this type of bid we know what the price needs to be, the challenge is to determine cost. In most business quotes, it is not cost plus, Remember the market determines price, business must than determine costs, profits are than determined on the difference. If a business wants to increase profits, cost reductions and commitment of the cost reductions must be found. Outline what cost reductions, if , Please note: Price is never determined on a cost plus basis. That is a falsehood of how price is determined. Price is determined by the market. Requirement: Complete the financial analysis with the stated prices and costs, then make suggestions on how to meet the financial goal requirements. 1. Complete Excel Financial IRR sheet with given data 2. Complete an income statement with given data 3. Outline suggested cost reductions necessary to meet requirements 4. Complete Excel Financial IRR sheet with improvements (one set of improvements) 5. Complete an income statement with improvements (one set of improvements) 6. Complete Excel Financial IRR sheet with improvements (second set of improvement ideas) 7. Complete an income statement with improvements (second set of improvements) Cost reductions are the key, commitments to these reductions must be made by the responsible areas, not meeting these commitments will be detrimental to committers, not meeting the requirements to win this bid, is also detrimental to career opportunities. Please be specific on cost reduction requirements, not quoting on this business is not an option.
senary-business-case-20-1-y2cxguym.docx copy-of-npv-irr-payback-excel-work-sheet-blank1-3-1-dwxkkndy.xlsx copy-of-senary-project-data-a1-3-2-yrmffp5l.xlsx
Answered Same DayAug 06, 2021

Answer To: Modular 7 Business Case Senary Scenario As the finance director of a GM Division that focuses on...

Sweety answered on Aug 08 2021
143 Votes
NPV
    You will need to enter information in any field that is highlighted in light blue (if it applies to your business case). The remaining fields should                                         DELPHI PACKARD ELECTRIC CASHFLOW                                                        2019    2020    2021    2022    2023    2024    2025    2026    2027    2028
    calculate automatically based on the formulas provided                                        8-Aug-20                                                    wtd by year    0.1428571429    0.1428571429    0    0    0    0.1428571429    0.1428571429    0.1428571429    0.1428571429    0.1428571429
                                            Program Name    Subject
    WORKING CAPITAL    17.00%    Enter the % used
    SOP CALENDAR YEAR    2019                                        Year Periods
    ENTER PROJECT NO:    AR                                        2018    2019    2020    2021    2022    2023    2024    2025    2026    2027    2028    TOTAL
    PROGRAM LIFE (IN YEARS)    0    YEARS                                INVESTMENT DATA                                                    INVEST.    (0)    (1)    (2)    (3)    (4)    (5)    (6)    (7)    (8)    (9)    (10)
    PROJECT TITLE:    business case                                    ( 1) LAND    -    -    -    -    -    -    -    -    -    -    -    -    BUILD.    0     0     0     0     0     0     0     0     0     0     0
    ENTER THE ANNUAL INTEREST                                         ( 1) BUILDINGS    -    -    -    -    -    -    -    -    -    -    -    -    M &E    0     10,000,000     0     0     0     0     0     0     0     0     0
    RATE IE. 12%,10%, ETC.    10%        2003            e                ( 1) MACH. AND EQUIP.    0    10,000,000    -    -    -    -    -    -    -    -    -    10,000,000    IT 7 YR    0     0     0     0     0     0     0     0     0     0     0
                                            (1) INFORMATION TECHNOLOGY- 7 YEAR PROPERTY    0    -    -    -    -    -    -    -    -    -    -    -    IT 3 YR    0     0     0     0     0     0     0     0     0     0     0
                                            (2) CAPACITY TOOLING    0    -    -    -    -    -    -    -    -    -    -    -    TOOLS    0     5,000,000     0     0     0     0     0     0     0     0     0
        CAPITAL INVESTMENT                                    ( 2) TOOLS     0    5,000,000    -    -    -    -    -    -    -    -    -    5,000,000
        LAND    BUILDINGS    M&E    IT-7 YEAR    CAPACITY TOOLING    TOTAL TOOLING    REBILL            ( 3) TOTAL    0    15,000,000    -    -    -    -    -    -    -    -    -    15,000,000    BUILD.        1.46%    3.17%    3.17%    3.17%    3.17%    3.17%    3.17%    3.17%    3.17%    3.17%
    INVESTMENT YR. 0            0            0        2    Error-Investment in years beyond program life                                                        DEPR. 1        0     0     0     0     0     0     0     0     0     0
    INVESTMENT YR. 1            10,000,000            5,000,000        2    Error-Investment in years beyond program life    PROFIT AND CASH FLOW DATA                                                    DEPR. 2        0     0     0     0     0     0     0     0     0     0
    INVESTMENT YR. 2                                0    OK    ( 4) NET SALES INCREASE (MEMO)        -    100,000,000    -    -    -    -    -    -    -    -    100,000,000    DEPR. 3            0     0     0     0     0     0     0     0     0
    INVESTMENT YR. 3                                0    OK    ( 5) OPERATING INCOME (EXCLUDING DEPR., & AMORT.)        -    100,000,000    -    -    -    -    -    -    -    -    100,000,000    DEPR. 4                0     0     0     0     0     0     0     0
    INVESTMENT YR. 4                                0    OK    ( 6) NET COST SAVINGS BEFORE DEPRECIATION                                                -    DEPR. 5                    0     0     0     0     0     0     0
    INVESTMENT YR. 5                                0    OK    ( 7) LEASE PAYMENTS INCLUDING TAXES, INSUR, ETC.                                                -    DEPR. 6                        0     0     0     0     0     0
    INVESTMENT YR. 6                                0    OK    ( 8) ADD: OTHER FIXED                                                 -    DEPR. 7                            0     0     0     0     0
    INVESTMENT YR. 7                                0    OK    ( 9) DEPRECIATION - EXISTING ITEMS        -                                        -
    INVESTMENT YR. 8                                0    OK    ( 9) DEPRECIATION- BUILDINGS        -    -    -    -    -    -    -    -    -    -    -    DEPR. 8                                0     0     0     0
    INVESTMENT YR. 9                                0    OK    ( 9) DEPRECIATION - NEW M & E AND 7 YR. IT ITEMS        -    -    -    -    -    -    -    -    -    -    -    DEPR. 9                                    0     0     0
    INVESTMENT YR. 10                                0    OK    (10) AMORT.- SPECIAL TOOLS AND 3 YR. IT ITEMS        -    -    -    -    -    -    -    -    -    -    -    DEPR. 10                                        0     0
                                            (11) SUBTOTAL- OTHER FIXED, DEPR, & AMORTIZATION        -    -    -    -    -    -    -    -    -    -    -    TOTAL        0     0     0     0     0     0     0     0     0     0
        0    0    10,000,000    0    0    5,000,000    0            (12) PROJECT CHARGES TO OPERATIONS    0    -    7,625,000    83,446,000    85,114,920    86,817,218    88,553,563    90,324,634    -    -    -    441,881,335
                                            (13) TOTAL FIXED & NON-VARIABLE EXPENSE    0    -    7,625,000    83,446,000    85,114,920    86,817,218    88,553,563    90,324,634    -    -    -    441,881,335    M&E & IT        14.28%    24.49%    17.49%    12.49%    8.93%    8.93%    8.93%    4.46%
                                             (14) NET PROFIT BEFORE TAX    -    -    92,375,000    (83,446,000)    (85,114,920)    (86,817,218)    (88,553,563)    (90,324,634)    -    -    -    (341,881,335)    DEPR. 1        0     0     0     0     0     0     0     0
        PROJECT CHARGES TO OPERATIONS                                    (15) NET PROFIT AFTER TAX     0    -    57,734,375    (52,153,750)    (53,196,825)    (54,260,762)    (55,345,977)    (56,452,896)    -    -    -    (213,675,835)    DEPR. 2        1,428,000     2,449,000     1,749,000     1,249,000     893,000     893,000     893,000     446,000
        PROJECT EXP    IT EXPENSE    PROTOTYPES    ENGINEERING    OTHER    MANU. START-UP    YEARLY TOTAL            (16) ADD: DEPRECIATION        -    -    -    -    -    -    -    -    -    -    -    DEPR. 3            0     0     0     0     0     0     0     0
    INVESTMENT YR. 0            0    0    0    0    0    4    Error-Investment in years beyond program life    (17) CASH FLOW FROM OPERATIONS    0    -    57,734,375    (52,153,750)    (53,196,825)    (54,260,762)    (55,345,977)    (56,452,896)    -    -    -    (213,675,835)    DEPR. 4                0     0     0     0     0     0     0     0
    INVESTMENT YR. 1        0    0        0    0    0    4    Error-Investment in years beyond program life    (18) DEDUCT:INCR. IN NET WORKING CAPITAL                                -    -    -        -    DEPR. 5                    0     0     0     0     0     0     0     0
    INVESTMENT YR. 2        0    7,500,000        0    125,000    7,625,000    4    Error-Investment in years beyond program life    (19) ADD: LIQUIDATION OF WORKING CAPITAL        -    -    -    -    -    -    -    -    -    -    -    DEPR. 6                        0     0     0     0     0     0     0     0
    INVESTMENT YR. 3        0    0        83,446,000    0    83,446,000    4    Error-Investment in years beyond program life    (20) TOOLS BILLED TO CUSTOMER    0    -    -    -    -    -    -    -    -    -    -    -    DEPR. 7                            0     0     0     0     0     0     0     0
    INVESTMENT YR. 4        0    0        85,114,920    0    85,114,920    4    Error-Investment in years beyond program life    (21) SALVAGE VALUE        -    -    -    -    -    -    -    -    -    -    -    DEPR. 8                                0     0     0     0     0     0     0     0
    INVESTMENT YR. 5        0    0        86,817,218    0    86,817,218    4    Error-Investment in years beyond program life                                                        DEPR. 9                                    0     0     0     0     0     0     0     0
    INVESTMENT YR. 6    0    0    0    0    88,553,563    0    88,553,563    6    Error-Investment in years beyond program life    (22) NET CASH FLOW [ROWS (17)-(18)+(19)+(20)+(21)-(3)]    0    (15,000,000)    57,734,375     (52,153,750)    (53,196,825)    (54,260,762)    (55,345,977)    (56,452,896)    0     0     0     (228,675,835)    DEPR. 10                                        0     0     0     0     0     0     0     0
    INVESTMENT YR. 7    0    0    0    0    90,324,634    0    90,324,634    6    Error-Investment in years beyond program life    (23) PRESENT VALUE FACTOR     1    0.9091     0.8265     0.7513     0.6830     0.6209     0.5645     0.5132     0.4665     0.4241    0.38554        TOTAL        1,428,000     2,449,000     1,749,000     1,249,000     893,000     893,000     893,000     446,000     0     0     0     0     0     0     0     0     0     0     0     0
    INVESTMENT YR. 8    0    0    0    0    0    0    0    6    Error-investment in years beyond program life    (24) PRESENT VALUE [ROWS (22)X(23)]    0    (13,636,350)    47,714,574     (39,183,634)    (36,333,963)    (33,691,592)    (31,241,144)    (28,969,368)    0     0     0     (135,341,477)
    INVESTMENT YR. 9        0    0    0        0    0    4    Error-Investment in years beyond program life    (25) DISCOUNTED PAYBACK    0    (13,636,350)    34,078,224     (5,105,410)    (41,439,373)    (75,130,965)    (106,372,109)    (135,341,477)    (135,341,477)
    INVESTMENT YR. 9    0    0    0    0    0    0    0    6    Error-Investment in years beyond program life     NET PRESENT VALUE    ($135,341,477)    WTD OVER THE PROGRAM LIFE                    PROJECT CHARGES TO OPERATIONS                        TOOLS & IT        33.00%    45.00%    15.00%    7.00%
    INVESTMENT YR. 10    0    0    0    0    0    0    0    6    Error-Investment in years beyond program life     RETURN ON INVESTMENT (IRR)    ERROR:#DIV/0!    AVERAGE PRICE        $0.00    AVG. O.I.    $0.00    PROJECT EXP.        0        PROTOTYPE & OTHER    441756335    AMORT 1        0     0     0     0
        0    0    7,500,000    0    434,256,335    125,000                 PAYBACK    N/A    AVERAGE COST        $0.00    O.I. MARGIN    ERROR:#DIV/0!    ENGINEERING        0        IT EXPENSE    0    AMORT 2        1,650,000     2,250,000     750,000     350,000
                                             ANNUAL INTEREST RATE    10.0%                        MFG START-UP        125,000         TOTAL    441,881,335    AMORT 3            0     0     0     0
    YEAR    PRICE    PRICE REDUCTIONS    CASHFLOW COST    VOLUME            PRICE REDUCTIONS--EXAMPLES                                                            TOTALS    AMORT 4                0     0     0     0
    2019            $420.98    200,000    0.945        1. GM 4 year program 2% reduction/yr. August SOP            VOLUME    Input DRI, CPV, FPV    200,000    200,000    0    0    0    200,000    200,000    200,000    200,000    200,000    Avg Volume    AMORT 5                    0     0     0     0
    2020    $500.00    0.00%    $420.98    200,000    ERROR - VOLUME BEYOND PROGRAM LIFE         2000= 0%- partial yr            PRICE WITH GIVEBACKS        $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    Calculation    AMORT 6                        0     0     0     0
    2021    $500.00    0.00%    $0.00                 2001= 0% First full year of production            TOTAL COST (OPERATING INCOME LEVEL)        $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    (1)    AMORT 7                            0     0     0     0
    2022    $0.00    0.00%    $0.00                 2002= 2% reduction from 2001 price            OPERATING INCOME        $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    (1,400,000)
    2023    $0.00    0.00%    $0.00                 2003= 2% reduction from 2002 price            CASHFLOW COST (OPERATING INCOME LEVEL)        $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    $0.00    $0.00
    2024    $500.00    0.00%    $420.98    200,000    ERROR - VOLUME BEYOND PROGRAM LIFE        2. Mack 4 year program 2%/yr effective 1 yr after May SOP            OPERATING INCOME MARGIN        ERROR:#DIV/0!    ERROR:#DIV/0!    ERROR:#DIV/0!    ERROR:#DIV/0!    ERROR:#DIV/0!    ERROR:#DIV/0!    ERROR:#DIV/0!    ERROR:#DIV/0!    ERROR:#DIV/0!    ERROR:#DIV/0!        AMORT 8                                0     0     0     0
    2025    $500.00    0.00%    $420.98    200,000    ERROR - VOLUME BEYOND PROGRAM LIFE         2000 -0% Partial first year            PRICE GIVEBACKS            0.00%    0.00%    0.00%    0.00%    0.00%    0.00%    0.00%    0.00%    0.00%        AMORT 9                                    0     0     0     0
    2026    $500.00    0.00%    $420.98    200,000    ERROR - VOLUME BEYOND PROGRAM LIFE         2001=1.167% reduction from 2000 price-            WORKING CAPITAL PERCENT    17.00%
    2027    $500.00    0.00%    $420.98    200,000    ERROR - VOLUME BEYOND PROGRAM LIFE         (7/12)*2 where 7=months from May (effec. Date)-Dec.                                                                AMORT 10                                        0     0     0     0
    2028    $500.00    0.00%    $420.98    200,000    ERROR - VOLUME BEYOND PROGRAM LIFE         2002=2% from 2001 price                                                                 TOTAL        1,650,000     2,250,000     750,000     350,000     0     0     0     0     0     0     0     0
                                 2003=.833% reduction from 2002 price                Payback calc.    1.0    0.3    0.0    0.0    0.0    0.0    0.0    0.0    0.0    0.0
     PV TABLE AFTER TAX RATE    0.1                         (5/12)*2 where 5=months from Jan thru end of production
    YEAR 1    0.90909                                        Month of SOP:    1
    YEAR 2    0.82645
    YEAR...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here
Have files?