Professor Excel

Comments 6

  1. XML | Pearltrees

    […] In short, XML greatly eases the definition, transmission, validation, and interpretation of data between databases, applications, and organizations. XML data and schema files Excel works primarily with two types of XML files: XML data files (.xml), which contain the custom tags and structured data. The XML standard also defines Extensible Stylesheet Language Transformation (XSLT) (.xslt) files, which are used to apply styles and transform XML data into different presentation formats. Key XML and Excel scenarios By using XML and Excel, you can manage workbooks and data in ways that were previously impossible or very difficult. Top of Page 1. 2. 3. 4. 5. XML & ZIP: Explore Your Excel Workbooks File Structure | Professor Excel | Professor Excel. […]

  2. Worker

    When I try to just change the name of the file to put “.zip” at the end, it just saves as I guess this is beyond just saving in a different format to zip? Maybe I’m just too old for excel these days?

    • Henrik Schiffner

      No, just simply rename it in the Windows Explorer, not through “Save as” in Excel.

  3. Michal

    Is there a way to access encrypted excel file (I do know the password) without opening the file in excel first to remove the password?

  4. daniel

    Assignment 2 – Analysis with Excel
    Instructions for the provided excel file
     Download the archive that contains the initial excel file. The archive is named
    Extract the workbook from the archive.
     Upload and submit your final Excel file. You may have to create a .zip archive containing the file in
    order to upload it.
    Information for the excel workbook file and explanation
    The excel file contains three worksheets. The first worksheet (Orders) contains formulas and the other two
    only values. The three worksheets can be used to track inventory for a set of parts, by entering the proper
    formulas in the cells instead of just numerical values.
     The values in Sheet(“Orders”) are the number of parts per month of certain types that were delivered
    to a warehouse for a particular month. The formula computes the number of parts sold the previous
    month minus how many were on hand at the start of the month and selects the larger of either this
    difference or 0. That is the amount to order during the current month.
     The values in Sheet(“Sales”) are the number of parts of certain types that were sold to customers from
    the warehouse during a month.
     The values in Sheet(“Available”) represent the number of each part available on the first day of the
    Before you begin, make sure you understand the data in the sheets. To help you understand the data, answer
    the following questions.
     How many parts with number A01 were on hand at the end of January 2016?
     How many parts with number A01 were ordered and received by the last day of January 2016?
     How many parts with number A01 were sold during the month of January 2016?
    Follow the instructions
    1. Add the first two values and subtract the third one to determine how many A01s were in the warehouse
    on the first day of February 2016. Compare your computed value to the corresponding value shown in
    the Sheet(“Available”).
    2. Sort all the worksheets so that the part numbers (column headings) are in ascending order
    alphabetically. Make sure that the corresponding numbers in adjacent rows are also rearranged as you
    3. Replace the values in Sheet(“Available”) with formulas that compute the number on hand. The
    calculation is a simple sum of two numbers minus another number.
    4. Format the three inventory worksheets so that titles and subtitles stand out and so that negative
    inventory amounts are in red.
    5. Copy the three sheets, accepting the default names and making sure the three copies are in order at the
    end of the workbook.
    6. The company is thinking to use a set order quantity inventory policy which means about always
    ordering the same amount of a particular part each month, rather than varying the amounts ordered from
    month to month. The amount ordered each month would vary from part to part, but for a particular part
    would be the same each month. Your job is to test a set order quantity inventory policy against last
    year’s data by trying different set order amounts for each part.
    To create a set order amount policy, replace the formulas in Sheet(“Orders (2)”) so that there is a single
    fixed value in each column. The value used in one column can be different from the value used in the
    next column, but the values in a single column should all be the same. That is, the same amount of a
    particular part will be ordered every month, regardless of how many are sold the prior month. An
    example Sheet(“Orders (20)”) with a set order amount is shown in Figure 1.
    Figure 1 Orders Worksheet with Set Order Amount Inventory Policy 7. Try to find a set order amount value for each part so that the quantity on hand on March 1, 2017 is as
    close as possible to 0 but still non-negative and the number of shortages (red values) is minimized. Stop
    trying different set order amounts when you think you have found the ‘best’ set order amount for each
    part (according to the stated criterion). To aid in your analysis, create a chart that compares the March
    2017 Available values for the new ordering policy compared to the old policy. ***Note that there is not one “CORRECT” answer to this task.
    8. How do you think the set order amount inventory policy compares to the policy previously used? Is
    one better than the other? Why? Write four or five sentences that express your ideas and place these in
    a worksheet named My Thoughts

Leave a comment

%d bloggers like this: